Home Alternatives Time-series basics Data Center & Infrastructure Telemetry Postgres basics Postgres guides Postgres best practices

Moving Averages in SQL: A Time-Series Developer's Guide

By Tiger Data Team

Updated at May 18, 2026

Moving averages are one of the most common operations in time-series analysis: smooth sensor noise, track 7-day rolling revenue, detect trend shifts in infrastructure metrics. Most tutorials show the SQL pattern, call it done, and move on.

The problem is that those tutorials assume data arrives at perfectly regular intervals: one row per minute, one row per day, no gaps. That assumption breaks the moment you're working with IoT sensors, telemetry pipelines, SCADA systems, or financial tick data. Irregular timestamps are not the exception. They are the default.

This guide covers the full path: the standard AVG() OVER window function, RANGE BETWEEN for interval-based windows, where both fail on irregular data, how Tiger Data's time_weight() hyperfunction computes a correct time-weighted moving average, and how continuous aggregates eliminate full-table scans at production scale. For the math behind time-weighted averages, see time-weighted averages in depth.

The standard SQL moving average: AVG() OVER (ROWS BETWEEN)

Start with the pattern you already know. Given a sensor readings table:

CREATE TABLE sensor_readings (
    ts          TIMESTAMPTZ NOT NULL,
    device_id   TEXT        NOT NULL,
    value       DOUBLE PRECISION
);

A 7-reading simple moving average looks like this:

SELECT
    ts,
    device_id,
    value,
    AVG(value) OVER (
        PARTITION BY device_id
        ORDER BY ts
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7
FROM sensor_readings
ORDER BY device_id, ts;

Sample output (illustrative):

ts	device_id	value	moving_avg_7
09:00:00	sensor-1	22.1	22.1
09:00:10	sensor-1	22.3	22.2
09:00:20	sensor-1	21.9	22.1
09:00:30	sensor-1	22.5	22.2
09:00:40	sensor-1	22.2	22.2
09:00:50	sensor-1	21.8	22.2
09:01:00	sensor-1	22.4	22.2

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means: count exactly 6 rows physically preceding the current row, regardless of the timestamps. The window always contains exactly 7 rows when enough data exists. This is the load-bearing detail for what comes next.

This pattern works correctly when data arrives at fixed, regular intervals. That assumption is what every tutorial makes and never revisits.

Calculating a rolling average over a time interval (RANGE vs. ROWS)

There is a second frame specification worth knowing: RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW. Unlike ROWS BETWEEN, which counts physical rows, RANGE BETWEEN with a time interval includes all rows where the timestamp falls within the specified trailing window, however many rows that turns out to be.

SELECT
    ts,
    device_id,
    value,
    AVG(value) OVER (
        PARTITION BY device_id
        ORDER BY ts
        RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg
FROM sensor_readings
ORDER BY device_id, ts;

PostgreSQL requires an explicit ORDER BY on a timestamp column for interval-based RANGE frames. The window includes every row within the trailing time window relative to the current row's timestamp.

Use RANGE BETWEEN INTERVAL when you want a true trailing-time-window average (all readings in the past 7 days regardless of row count) rather than a fixed count of N prior rows. This is semantically more correct for most time-series analysis than ROWS BETWEEN.

The catch: RANGE BETWEEN still treats every row within the window as equally significant. A reading in effect for 2 hours and one in effect for 10 seconds each contribute equally to the average. On evenly sampled data that is fine. On irregular data it is not.

The problem: what happens with irregular timestamps

Here is what neither LearnSQL nor Mode tells you: with ROWS BETWEEN, the window always includes exactly N preceding rows regardless of the time gaps between them. When your data has gaps (a sensor goes offline, a device bursts after reconnecting, a pipeline stutters), the 7-row moving average silently produces numbers that look reasonable but are mathematically wrong.

Consider a temperature sensor that normally sends a reading every 10 seconds, goes offline for 45 minutes, and then resumes. Here is what the data looks like:

ts	value	notes
09:00:00	22.1	normal
09:00:10	22.3	normal
09:00:20	21.9	normal
09:00:30	22.5	normal
09:00:40	22.2	normal
09:00:50	21.8	normal
09:46:00	38.7	resumed after 45-min gap
09:46:10	39.1	resumed
09:46:20	38.9	resumed

With ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, the moving average at the row for 09:46:20 includes readings from both 09:00:10 and 09:46:20 in the same 7-row window, and gives them identical weight. The 45-minute gap disappears from the calculation entirely.

A correct time-weighted average accounts for those 45 minutes. The reading at 09:00:50 was the last known value before the gap; it was "in effect" for 45 minutes. Any moving average that ignores this duration over-weights the post-gap burst and under-weights the long stable period before it.

The numbers here are illustrative, but the failure mode is real. It shows up in IoT sensor networks, industrial telemetry, SCADA systems, event-driven pipelines, and financial tick data. Wherever timestamps are irregular, row-count-based windows give you a plausible-looking wrong answer.

RANGE BETWEEN INTERVAL is a partial fix: it counts only rows within a real trailing time window. But it still assigns equal weight to every row. A reading in effect for 45 minutes and one in effect for 10 seconds each contribute 1/N to the average.

The question often left unanswered: how do you weight each reading by the duration it was observed?

Time-weighted moving averages with time_weight()

Tiger Data's time_weight() hyperfunction weights each observation by the duration it was in effect, computing a duration-correct average rather than a row-count average.

time_weight(
    method TEXT,
    ts     TIMESTAMPTZ,
    value  DOUBLE PRECISION
) RETURNS TimeWeightSummary

Two interpolation methods:

'Linear' (alias: 'trapezoidal'): assumes values change linearly between observations. Best for smooth physical signals: temperature, pressure, flow rate, voltage.
'LOCF' (Last Observation Carried Forward): assumes the value stays constant until the next observation. Best for step-change signals: on/off states, digital sensor readings, discrete state machines.

time_weight() returns a TimeWeightSummary object. Wrap it in the average() accessor to get a numeric result:

SELECT
    device_id,
    average(time_weight('Linear', ts, value)) AS time_weighted_avg
FROM sensor_readings
GROUP BY device_id;

On the irregular-timestamp example, time_weight() correctly weights the 45-minute pre-gap reading far more heavily than the 10-second post-gap readings. The result is a fundamentally different number from ROWS BETWEEN or RANGE BETWEEN, and it is the correct one.

time_weight() is available on Tiger Cloud and ships as part of the TimescaleDB toolkit extension. Full API reference: /docs/reference/toolkit/time_weight/time_weight. For the mathematical treatment, see time-weighted averages in depth.

Combining time_weight() with time_bucket() for production queries

The more common production pattern combines time_weight() with time_bucket() to compute a bucketed time-weighted average. The example below produces one time-weighted average per hour across all readings in that bucket:

SELECT
    time_bucket('1 hour', ts) AS bucket,
    device_id,
    average(time_weight('Linear', ts, value)) AS twa_per_hour
FROM sensor_readings
GROUP BY bucket, device_id
ORDER BY device_id, bucket;

When you need to combine time_weight() aggregates across multiple buckets (for example, rolling up 1-hour buckets into 24-hour moving averages), use rollup(). The rollup() function combines multiple TimeWeightSummary objects while preserving the interpolation state across bucket boundaries. A simple SUM or AVG over pre-aggregated values would not do this correctly.

SELECT
    device_id,
    average(rollup(hourly_twa)) AS daily_twa
FROM hourly_sensor_stats
GROUP BY device_id;

For more on rollup(), see writing better queries with Tiger Data hyperfunctions.

Moving averages at scale: the continuous aggregates pattern

Window function queries scan the full underlying table on every execution. For a hypertable with hundreds of millions of sensor readings, a 7-day moving average query gets expensive regardless of your indexes. Chunk exclusion helps, but the scan still touches every relevant row on every request.

Continuous aggregates address this at the architecture level. They are PostgreSQL materialized views that refresh incrementally as new data arrives rather than recomputing from scratch. Moving average queries hit the materialized view instead of the raw hypertable, which typically reduces both I/O and query latency substantially.

Here is a continuous aggregate that pre-computes time_weight() at 1-hour granularity:

CREATE MATERIALIZED VIEW sensor_hourly_twa
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', ts) AS bucket,
    device_id,
    time_weight('Linear', ts, value) AS twa_summary
FROM sensor_readings
GROUP BY bucket, device_id
WITH DATA;

The continuous aggregate stores the TimeWeightSummary object rather than the final average. The summary preserves the interpolation state needed to roll up into coarser windows at query time:

SELECT
    time_bucket('1 day', bucket) AS day,
    device_id,
    average(rollup(twa_summary)) AS daily_twa
FROM sensor_hourly_twa
GROUP BY day, device_id
ORDER BY device_id, day;

This is the production pattern for high-volume time-series workloads. In practice, moving from full-table scans to continuous aggregate queries produces significant latency improvements. Tiger Data's Hypercore columnar storage further accelerates queries that hit raw data for recent, unmaterialized rows.

Two tradeoffs to account for:

Schema complexity. Continuous aggregates add a materialized view layer that must be defined, maintained, and understood by anyone operating the system.
Refresh lag. New data is not immediately visible in the view. The lag is configurable via refresh policies, but it exists. If you need moving averages over the absolute latest data, query the raw hypertable directly, or use Tiger Data's real-time aggregates, which combine pre-materialized rollups with a live scan of the unmaterialized range.

For a broader introduction to the continuous aggregates pattern, see real-time analytics with continuous aggregates.

Decision framework: which moving average approach is right for your data

Approach	Best for	Limitation
`AVG() OVER (ROWS BETWEEN N PRECEDING)`	Evenly sampled data; simple analytics	Silently wrong on irregular timestamps
`AVG() OVER (RANGE BETWEEN INTERVAL ... PRECEDING)`	Time-window-based rolling average	Still equally weights all readings; no interpolation
`average(time_weight('Linear', ts, value))`	Irregular timestamps; IoT/telemetry/industrial	Requires TimescaleDB extension; `rollup()` needed for multi-bucket aggregation
`average(time_weight('LOCF', ts, value))`	Step-change signals; state machines	Assumes constant value between observations
Exponential moving average (recursive CTE)	Trend smoothing with recency bias	O(n) scan; not suitable for large datasets without pre-aggregation
Continuous aggregates + `time_weight()`	Production scale; billions of rows; low-latency queries	Schema setup overhead; refresh lag (configurable)

Choose ROWS BETWEEN if:

Your data arrives at fixed, regular intervals with no gaps
You want to count exactly N prior observations regardless of their timestamps
You are working without the TimescaleDB extension

Choose RANGE BETWEEN INTERVAL if:

You want a true trailing-time-window count (all readings in the past 7 days)
Your data is mostly regular with occasional small gaps
You want a pure-PostgreSQL solution without extensions

Choose time_weight() if:

Your timestamps are irregular by design or by the nature of the data source (IoT, telemetry, event-driven)
You need a duration-weighted average, not a row-count average
You are running on Tiger Cloud or TimescaleDB (the open-source extension)

Choose continuous aggregates + time_weight() if:

You are running moving average queries at production scale (millions of rows or more)
Query latency on raw-table scans is unacceptable
You need real-time freshness combined with pre-computed aggregation

You can find additional patterns for both time-bucketing and window analysis in the guide to data analysis on PostgreSQL.

Exponential moving average in SQL

Exponential moving averages (EMA) apply exponentially decreasing weights to older observations, giving more influence to recent data. EMA is common in financial time series (stock prices, volatility signals) and signal processing where you want a smoother trend line that responds to recent changes faster than a simple moving average would.

In SQL, EMA is typically implemented with a recursive CTE:

WITH RECURSIVE ema_calc AS (
    -- Base case: first row
    SELECT
        ts,
        value,
        value AS ema
    FROM sensor_readings
    WHERE device_id = 'sensor-1'
    ORDER BY ts
    LIMIT 1

    UNION ALL

    -- Recursive case: apply EMA smoothing
    SELECT
        r.ts,
        r.value,
        0.1 * r.value + 0.9 * e.ema AS ema
    FROM sensor_readings r
    JOIN ema_calc e ON r.ts > e.ts
    WHERE r.device_id = 'sensor-1'
    ORDER BY r.ts
    LIMIT 1
)
SELECT ts, value, ema
FROM ema_calc
ORDER BY ts;

The smoothing factor (0.1 in this example) controls how quickly the EMA responds to new values. A higher factor gives more weight to recent readings.

Recursive CTEs for EMA are O(n): they scan every row in sequence, and pre-aggregation does not help. Tiger Data does not currently offer a native exponential_moving_average() hyperfunction. For most time-series use cases where recency bias is useful, time_weight('Linear', ts, value) is more practical and avoids the recursive scan. For true EMA on large datasets, external computation (Python with pandas) is the better path.

FAQ: moving averages in SQL

What is the difference between a moving average and a rolling average in SQL?

The terms are interchangeable. Both compute an average over a trailing window of rows or time. "Rolling average" appears more in business analytics contexts; "moving average" is more common in signal processing and time-series contexts. The SQL pattern is identical: AVG() OVER (ORDER BY ts ROWS/RANGE BETWEEN ...).

Why does my SQL moving average produce wrong results with irregular timestamps?

Because ROWS BETWEEN N PRECEDING AND CURRENT ROW counts physical rows, not time intervals. If data arrives in bursts (for example, after a sensor reconnects after a gap), the window includes rows from a very short recent period and weights them equally with rows from a much longer period. The result looks plausible but is mathematically incorrect. Use RANGE BETWEEN INTERVAL ... PRECEDING for time-window averaging, or Tiger Data's time_weight() hyperfunction for true time-weighted averaging on irregular data.

What happens to moving averages with irregular time series data?

Standard SQL window functions (ROWS BETWEEN) ignore the time gaps between rows: ten readings from a sensor that just came back online get weighted identically to ten readings from a normal hour. RANGE BETWEEN INTERVAL constrains the window to a real time range but still treats every row inside it as equal. The fix is time_weight('Linear', ts, value), which weights each observation by its duration.

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

Use AVG(value) OVER (ORDER BY ts ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) if your data has exactly one row per day with no gaps. Use AVG(value) OVER (ORDER BY ts RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) if you want a true 7-day trailing window regardless of row count. For irregular data, use Tiger Data's time_weight() hyperfunction with time_bucket('7 days', ts) to compute a duration-weighted average per 7-day bucket: _{average(time_weight('Linear', ts, value))} grouped by time_bucket('7 days', ts).

What is a time-weighted average and when should I use it?

A time-weighted average weights each observation by the duration it was in effect rather than treating every row equally. Use it when timestamps are irregular: IoT sensors, telemetry, SCADA, financial tick data. A row-count average over-weights frequent readings and under-weights infrequent ones. See time-weighted averages in depth for the mathematical treatment.

Can I compute a moving average in PostgreSQL without any extensions?

Yes. AVG() OVER (ROWS BETWEEN ...) and AVG() OVER (RANGE BETWEEN INTERVAL ... PRECEDING) require no extensions and run on any PostgreSQL installation. True time-weighted interpolation for irregular data requires Tiger Data's time_weight() hyperfunction, which ships with the TimescaleDB extension.

How do I avoid scanning billions of rows for every moving average query?

Use continuous aggregates (available in TimescaleDB). A continuous aggregate pre-materializes aggregations at a chosen time granularity and refreshes them incrementally as new data arrives. Moving average queries then hit the materialized view rather than the raw hypertable, reducing I/O and latency substantially. Combined with time_weight() and rollup(), you can pre-aggregate at 1-hour granularity and roll up to daily or weekly windows at query time without rescanning raw data. This is the recommended production pattern for high-volume time-series workloads.

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

ROWS BETWEEN N PRECEDING counts exactly N physical rows before the current row, ignoring timestamps. RANGE BETWEEN INTERVAL '7 days' PRECEDING includes all rows within the trailing time window, however many that is. For time-series data, RANGE with an interval is more semantically correct, but neither weights observations by duration. Use time_weight() for that. See Tiger Data's PostgreSQL window functions guide for a broader overview.

Does Tiger Data support exponential moving averages?

Tiger Data does not currently offer a native exponential_moving_average() hyperfunction. EMA can be computed with a recursive CTE, but that approach is O(n) and not practical at scale. For most recency-bias use cases, time_weight('Linear', ts, value) is more practical. For true EMA on large datasets, external computation (Python, pandas) is the current path.

Can I use time_weight() inside a continuous aggregate?

Yes. Tiger Data's time_weight() hyperfunction is designed to work inside continuous aggregates. Define the continuous aggregate with time_weight() at a fine granularity (for example, 1-hour buckets storing the TimeWeightSummary object). Then use rollup() at query time to combine those pre-aggregated summaries into coarser windows (24-hour or 7-day moving averages) without rescanning raw data. The rollup() function is specifically designed to combine TimeWeightSummary objects while preserving interpolation state across boundaries.

What is LOCF and when should I use it for moving averages?

LOCF stands for Last Observation Carried Forward. With time_weight('LOCF', ts, value), the value at each observation is assumed constant until the next observation arrives. Use LOCF for step-change signals: on/off states, digital sensor readings, or any metric that changes discretely. Use 'Linear' for continuous signals like temperature, pressure, or voltage.

How this fits into a larger PostgreSQL analytics workflow

The PostgreSQL window functions guide and the guide to data analysis on PostgreSQL provide useful grounding if you're moving from general Postgres analytics toward time-series-specific patterns.

If your production system has outgrown raw-table window function queries, the next step is continuous aggregates. Start with real-time analytics with continuous aggregates for a developer-oriented introduction.

Tiger Data (creators of TimescaleDB) builds the database infrastructure for time-series and operational analytics on PostgreSQL. time_weight() and continuous aggregates are available on Tiger Cloud and in the open-source TimescaleDB extension.