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()(ordate_bin()) to put raw timestamps on a regular grid before any window math. - Rows are not days:
ROWS BETWEEN 6 PRECEDINGcounts rows. UseRANGEwith an interval, or a calendar join, when dates are missing. - Build a calendar spine:
generate_series()plus aLEFT JOINexposes gaps as explicitNULLrows you can fill or flag. - Detect gaps and islands:
LAG()finds missing stretches, and thedate - 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 withts 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 ontsis 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
tsenables partition pruning for date-bounded queries. - Materialize the calendar. For recurring reports, store a permanent
dim_datetable 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.



