SQL for Time Series: Date Functions, Rolling Averages, and Gaps

Most time series bugs in SQL come from one false assumption: that one row equals one time step. Daily sales tables skip holidays, sensors drop packets, and billing events only exist when something happens. A rolling average built on those rows quietly averages the wrong window. This guide shows how to truncate and bucket timestamps, build correct rolling averages, and detect and repair gaps. The examples use PostgreSQL, with dialect equivalents for MySQL, BigQuery, SQL Server, and Snowflake.

Quick Takeaways

  • Bucket first: use date_trunc() (or date_bin()) to put raw timestamps on a regular grid before any window math.
  • Rows are not days: ROWS BETWEEN 6 PRECEDING counts rows. Use RANGE with an interval, or a calendar join, when dates are missing.
  • Build a calendar spine: generate_series() plus a LEFT JOIN exposes gaps as explicit NULL rows you can fill or flag.
  • Detect gaps and islands: LAG() finds missing stretches, and the date - ROW_NUMBER() trick groups consecutive runs.

Sample Data Setup

All examples use a daily revenue table with deliberately missing days.

CREATE TABLE daily_sales (
    sale_date  date PRIMARY KEY,   -- PK gives a B-tree index for ordered scans
    revenue    numeric(12,2) NOT NULL
);

INSERT INTO daily_sales (sale_date, revenue) VALUES
  ('2026-01-01', 1200), ('2026-01-02', 1350), ('2026-01-03', 980),
  ('2026-01-06', 1500),                       -- Jan 4-5 missing
  ('2026-01-07', 1425), ('2026-01-08', 1610),
  ('2026-01-12', 1700), ('2026-01-13', 1550); -- Jan 9-11 missing

Core Date Functions for Time Series

Date functions do two jobs in time series work: they normalize timestamps into buckets, and they shift them for comparisons.

Truncating and Extracting

date_trunc(unit, ts) snaps a timestamp to the start of a period. It is the foundation of every aggregation by day, week, or month.

SELECT
    date_trunc('week',  sale_date)::date  AS week_start,   -- ISO week: Monday
    date_trunc('month', sale_date)::date  AS month_start,
    EXTRACT(dow  FROM sale_date)          AS day_of_week,  -- 0 = Sunday
    EXTRACT(isodow FROM sale_date)        AS iso_dow,      -- 1 = Monday
    SUM(revenue)                          AS revenue
FROM daily_sales
GROUP BY 1, 2, 3, 4;

For custom buckets such as 15-minute intervals, use date_bin() (PostgreSQL 14+). It aligns timestamps to an origin you control.

SELECT
    date_bin('15 minutes', ts, TIMESTAMP '2026-01-01 00:00:00') AS bucket,
    AVG(temperature_c) AS avg_temp
FROM sensor_readings
GROUP BY bucket
ORDER BY bucket;

Interval Arithmetic

Add or subtract INTERVAL values instead of hard-coding day counts. Date subtraction returns an integer number of days.

SELECT
    sale_date,
    sale_date - INTERVAL '7 days'      AS same_day_last_week,
    sale_date + 30                     AS plus_30_days,
    CURRENT_DATE - sale_date           AS days_ago   -- integer
FROM daily_sales;

Time zone warning: date_trunc('day', timestamptz) uses the session time zone. Convert explicitly with ts AT TIME ZONE 'America/New_York' before truncating. Otherwise daily totals shift across midnight boundaries between environments.

Dialect Cheat Sheet

Operation PostgreSQL MySQL 8 BigQuery SQL Server Snowflake
Truncate to month date_trunc('month', d) DATE_FORMAT(d,'%Y-%m-01') DATE_TRUNC(d, MONTH) DATETRUNC(month, d) (2022+) DATE_TRUNC('month', d)
Add 7 days d + INTERVAL '7 days' DATE_ADD(d, INTERVAL 7 DAY) DATE_ADD(d, INTERVAL 7 DAY) DATEADD(day, 7, d) DATEADD(day, 7, d)
Days between d2 - d1 DATEDIFF(d2, d1) DATE_DIFF(d2, d1, DAY) DATEDIFF(day, d1, d2) DATEDIFF(day, d1, d2)
Calendar generator generate_series() Recursive CTE GENERATE_DATE_ARRAY() Recursive CTE / numbers table GENERATOR() + ROW_NUMBER()

Rolling Averages with Window Functions

A rolling (moving) average smooths short-term noise so trends become visible. The syntax is simple. The frame definition is where results go wrong.

The Naive Version

SELECT
    sale_date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW   -- last 7 ROWS, not 7 days
    ) AS rolling_7_row_avg
FROM daily_sales;

This works only when every date appears exactly once. In the sample data, the window on January 12 reaches back to January 3, which is 9 calendar days, because the missing dates have no rows to count.

ROWS vs. RANGE vs. Calendar Join

Approach Window Definition Handles Missing Dates Cost Best For
ROWS frame N physical rows ❌ No Lowest Gapless, pre-aggregated series
RANGE with interval Rows within N days of current row ✅ Yes (averages only existing rows) Low Event data where absent means “no event”
Calendar join + ROWS N rows over a complete date spine ✅ Yes (zeros or fills are explicit) Higher Metrics where absent means zero

The Correct Calendar-Based Version

RANGE with an interval (PostgreSQL 11+) defines the window by date distance. It averages only the rows that exist inside the last 7 days.

SELECT
    sale_date,
    revenue,
    AVG(revenue) OVER w AS rolling_7d_avg,
    COUNT(*)     OVER w AS days_in_window     -- diagnose sparse windows
FROM daily_sales
WINDOW w AS (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
);

If missing days mean zero revenue, the right denominator is 7, not the count of rows present. Use a calendar spine instead:

WITH spine AS (
    SELECT d::date AS day
    FROM generate_series(
        (SELECT MIN(sale_date) FROM daily_sales),
        (SELECT MAX(sale_date) FROM daily_sales),
        INTERVAL '1 day'
    ) AS d
),
filled AS (
    SELECT s.day,
           COALESCE(f.revenue, 0) AS revenue      -- explicit zero for missing days
    FROM spine s
    LEFT JOIN daily_sales f ON f.sale_date = s.day
)
SELECT
    day,
    revenue,
    CASE WHEN COUNT(*) OVER w = 7                 -- enforce a full window
         THEN ROUND(AVG(revenue) OVER w, 2)
    END AS rolling_7d_avg
FROM filled
WINDOW w AS (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
ORDER BY day;

The CASE WHEN COUNT(*) = 7 guard works like the min_periods argument in Pandas. It returns NULL for the first six days instead of a misleading partial average.

Cumulative Sums, Lags, and Period-over-Period Change

SELECT
    day,
    revenue,
    SUM(revenue) OVER (ORDER BY day)               AS cumulative_revenue,
    LAG(revenue, 7) OVER (ORDER BY day)            AS revenue_7d_ago,   -- valid on a gapless spine
    ROUND(100.0 * (revenue - LAG(revenue, 7) OVER (ORDER BY day))
          / NULLIF(LAG(revenue, 7) OVER (ORDER BY day), 0), 1) AS wow_pct_change
FROM filled;

NULLIF(..., 0) prevents division-by-zero errors when the comparison period had no revenue. LAG(x, 7) returns the value seven rows back, which equals seven days back only on a gapless spine.

Detecting Gaps and Islands

Before choosing a repair strategy, measure the problem.

Find Missing Date Ranges with LAG

SELECT
    prev_date + 1                  AS gap_start,
    sale_date  - 1                 AS gap_end,
    sale_date - prev_date - 1      AS missing_days
FROM (
    SELECT sale_date,
           LAG(sale_date) OVER (ORDER BY sale_date) AS prev_date
    FROM daily_sales
) t
WHERE sale_date - prev_date > 1;   -- any jump larger than one day is a gap

Result for the sample data: January 4–5 (2 days) and January 9–11 (3 days).

Group Consecutive Runs (Islands)

The inverse problem is finding unbroken streaks. Subtracting a row number from a date yields a constant for every consecutive run.

SELECT
    MIN(sale_date)  AS island_start,
    MAX(sale_date)  AS island_end,
    COUNT(*)        AS consecutive_days
FROM (
    SELECT sale_date,
           sale_date - (ROW_NUMBER() OVER (ORDER BY sale_date))::int AS grp
    FROM daily_sales
) t
GROUP BY grp
ORDER BY island_start;

This pattern drives login streak analysis, uptime reporting, and session detection.

Filling Gaps: Choosing a Strategy

The correct fill depends on what a missing value means in the business context.

Strategy Meaning of “Missing” SQL Technique Typical Use
Zero fill No activity occurred COALESCE(x, 0) after calendar join Sales, signups, page views
Forward fill (LOCF) State persisted unchanged Window COUNT + FIRST_VALUE Prices, inventory levels, device status
Linear interpolation Value changed smoothly Prev/next values with weighted math Temperature, sensor telemetry
Leave NULL Unknown, must not be guessed Calendar join, no fill Audit-sensitive metrics

Forward Fill (Last Observation Carried Forward)

PostgreSQL lacks IGNORE NULLS, so use a running count of non-null values as a group key.

WITH spine AS (
    SELECT d::date AS day
    FROM generate_series(DATE '2026-01-01', DATE '2026-01-13', INTERVAL '1 day') d
),
joined AS (
    SELECT s.day,
           p.price,
           COUNT(p.price) OVER (ORDER BY s.day) AS grp   -- increments only on non-NULL
    FROM spine s
    LEFT JOIN daily_prices p ON p.price_date = s.day
)
SELECT
    day,
    FIRST_VALUE(price) OVER (PARTITION BY grp ORDER BY day) AS price_filled
FROM joined
ORDER BY day;

Every row after a real observation shares its grp value, so FIRST_VALUE returns that observation. Leading NULL rows form group 0 and stay NULL. Snowflake, BigQuery, and SQL Server 2022+ support LAST_VALUE(x) IGNORE NULLS, which shortens this query.

Linear Interpolation

WITH marked AS (
    SELECT day, reading,
           COUNT(reading) OVER (ORDER BY day) AS grp
    FROM spine_with_readings
),
anchors AS (
    SELECT day, reading, grp,
           FIRST_VALUE(day)     OVER (PARTITION BY grp ORDER BY day) AS prev_day,
           FIRST_VALUE(reading) OVER (PARTITION BY grp ORDER BY day) AS prev_val
    FROM marked
),
nexts AS (
    SELECT *,
           MIN(day) FILTER (WHERE reading IS NOT NULL)
               OVER (ORDER BY day ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS next_day
    FROM anchors
)
SELECT day,
       COALESCE(reading,
           prev_val + (
               (SELECT reading FROM spine_with_readings WHERE day = next_day) - prev_val
           ) * (day - prev_day)::numeric / NULLIF(next_day - prev_day, 0)
       ) AS reading_interp
FROM nexts;

For long series, compute interpolation in Python with pandas.Series.interpolate(). Use SQL when the data cannot leave the warehouse.

Real-World Use Cases

Use Case 1: Monitoring IoT Sensor Uptime

A fleet of temperature sensors reports every minute. Operations needs to know which devices went silent for more than 10 minutes.

SELECT
    device_id,
    prev_ts                         AS silent_from,
    ts                              AS silent_until,
    ts - prev_ts                    AS outage_duration
FROM (
    SELECT device_id, ts,
           LAG(ts) OVER (PARTITION BY device_id ORDER BY ts) AS prev_ts
    FROM sensor_readings
) t
WHERE ts - prev_ts > INTERVAL '10 minutes'
ORDER BY outage_duration DESC;

PARTITION BY device_id keeps each device’s timeline independent, so one sensor’s data never fills another’s gap.

Use Case 2: E-Commerce Revenue Trend with Weekly Smoothing

Marketing wants a dashboard-ready series that does not collapse on low-traffic days. Join the calendar spine, zero-fill, and apply the guarded 7-day average from the earlier section. Pair it with LAG(revenue, 7) for week-over-week growth. Because the spine is gapless, the 7-row offset equals exactly one week.

Use Case 3: Subscription Churn Windows

Flag customers with no activity for 30 days using the same LAG() logic, partitioned by customer_id, with a threshold of INTERVAL '30 days'. Group the flagged rows by date_trunc('month', ts) to chart churn risk over time.

Performance and Production Tips

  • Index the time column. A B-tree on (entity_id, ts) supports partitioned window sorts. For append-only tables with billions of rows, a BRIN index on ts is far smaller.
  • Aggregate before windowing. Collapse raw events to daily rows in a CTE, then apply window functions to the smaller set.
  • Reuse named windows. The WINDOW w AS (...) clause avoids repeating frame definitions and lets the planner share one sort.
  • Partition large tables by time. Declarative range partitioning on ts enables partition pruning for date-bounded queries.
  • Materialize the calendar. For recurring reports, store a permanent dim_date table instead of regenerating the spine on every query.

Frequently Asked Questions

How do you calculate a 7-day moving average in SQL?

Apply AVG() as a window function with a frame covering the current row and the six before it: AVG(metric) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Run it over a gapless calendar spine so that six rows equals six days. If dates can be missing, use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW instead.

How do you fill missing dates in a SQL time series?

Generate a complete date list with generate_series() (PostgreSQL), GENERATE_DATE_ARRAY() (BigQuery), or a recursive CTE (MySQL, SQL Server). LEFT JOIN your data onto that list, then replace NULL values with COALESCE(x, 0) for zero fill, or use a window-based forward fill for state-like data.

What is the difference between ROWS and RANGE in a window function?

ROWS counts physical rows relative to the current row. RANGE selects rows by the value of the ORDER BY column, such as all rows within 6 days. With missing dates, ROWS produces windows that span more calendar time than intended, while RANGE keeps the window length consistent in time.

How do you find gaps in a date sequence in SQL?

Use LAG() to fetch each row’s previous date, then filter for rows where the difference exceeds your expected step. For daily data, WHERE sale_date - prev_date > 1 returns every gap, and prev_date + 1 and sale_date - 1 give its start and end dates.

Hot this week

Build a RAG App From Scratch: Documents, Embeddings, and Answers

Build a RAG app from scratch in Python: chunk documents, create embeddings, retrieve context, and generate cited answers. Copy the full code now.

SHAP vs LIME: Explain Any Model’s Predictions

Compare SHAP vs LIME for explaining ML predictions. See code, speed tradeoffs, and a decision guide. Pick the right explainer for your model today.

Predict House Prices With Linear Regression: A Complete Project

Build a house price predictor with linear regression in Python. Clean data, train, and evaluate with RMSE and R². Start the full project now.

AI and Copyright: The Lawsuits, the Rulings, and What They Mean for Creators

Track every major AI copyright lawsuit in 2026: Anthropic's $1.5B deal, NYT v. OpenAI, Ross, Midjourney. See what it means for your work. Read now.

Build Your First Spam Email Classifier

Build a spam email classifier in Python with TF-IDF and Naive Bayes. Copy the code, hit 98%+ accuracy, and ship your first ML model today.

Topics

Build a RAG App From Scratch: Documents, Embeddings, and Answers

Build a RAG app from scratch in Python: chunk documents, create embeddings, retrieve context, and generate cited answers. Copy the full code now.

SHAP vs LIME: Explain Any Model’s Predictions

Compare SHAP vs LIME for explaining ML predictions. See code, speed tradeoffs, and a decision guide. Pick the right explainer for your model today.

Predict House Prices With Linear Regression: A Complete Project

Build a house price predictor with linear regression in Python. Clean data, train, and evaluate with RMSE and R². Start the full project now.

AI and Copyright: The Lawsuits, the Rulings, and What They Mean for Creators

Track every major AI copyright lawsuit in 2026: Anthropic's $1.5B deal, NYT v. OpenAI, Ross, Midjourney. See what it means for your work. Read now.

Build Your First Spam Email Classifier

Build a spam email classifier in Python with TF-IDF and Naive Bayes. Copy the code, hit 98%+ accuracy, and ship your first ML model today.

Best AI Music and Voice Generators in 2026

Compare Suno v6, ElevenLabs Music, Udio, Eleven v4, Cartesia and Fish Audio on price, rights and latency. Pick your tool and start creating today.

Train/Test Splits Done Right: Best Practices and Common Mistakes

Avoid data leakage with proper train/test splits. Compare random, stratified, group and time-series methods with Python code. Fix your pipeline today.

AI Engineer vs ML Engineer vs Data Scientist: What’s the Difference?

AI engineer vs ML engineer vs data scientist: compare skills, tools, and roles side by side. Find your best-fit career path and start learning today.

Related Articles

Popular Categories