---
title: "Moving Averages in SQL: A Time-Series Developer's Guide"
description: "Learn how to compute moving averages in SQL, why standard window functions break on irregular timestamps, and how to use time_weight() and continuous aggregates to get correct results at production scale."
section: "General"
---
> **TimescaleDB is now Tiger Data.**
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](https://www.tigerdata.com/blog/what-time-weighted-averages-are-and-why-you-should-care).
## 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](https://www.tigerdata.com/docs/reference/toolkit/time_weight/time_weight). For the mathematical treatment, see [time-weighted averages in depth](https://www.tigerdata.com/blog/what-time-weighted-averages-are-and-why-you-should-care).
### 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](https://www.tigerdata.com/blog/how-to-write-better-queries-for-time-series-data-analysis-using-custom-sql-functions).
## 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](https://www.tigerdata.com/docs/learn/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](https://www.tigerdata.com/docs/learn/columnar-storage/understand-hypercore) columnar storage further accelerates queries that hit raw data for recent, unmaterialized rows.
Two tradeoffs to account for:
1. **Schema complexity.** Continuous aggregates add a materialized view layer that must be defined, maintained, and understood by anyone operating the system.
2. **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](https://www.tigerdata.com/blog/real-time-analytics-for-time-series-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](https://www.tigerdata.com/learn/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, t`ime_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](https://www.tigerdata.com/blog/what-time-weighted-averages-are-and-why-you-should-care) 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](https://www.tigerdata.com/learn/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](https://www.tigerdata.com/learn/postgresql-window-functions) guide and the [guide to data analysis on PostgreSQL](https://www.tigerdata.com/learn/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](https://www.tigerdata.com/blog/real-time-analytics-for-time-series-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*](https://www.tigerdata.com/cloud)* and in the open-source TimescaleDB extension.*