DOCS
← Back to Glossary
time-series
A time series is a sequence of data points ordered chronologically. Time-series analysis uncovers trends, seasonality, and forecasts in data like metrics, sensor readings, and financial ticks — and DuckDB handles most of these patterns in plain SQL.
A time series is a sequence of data points collected and ordered chronologically, usually at regular intervals. In data analysis and engineering, time-series data represents how a measurement changes over time — stock prices, temperature readings, application metrics, or website traffic. What makes it distinct is its temporal ordering: the sequence and spacing of points carry the information you care about, such as trends, seasonality, and cycles.
Use cases for time-series analysis
Trend analysis: identifying long-term direction in the data.
Seasonality detection: recognizing recurring patterns at fixed intervals.
Forecasting: predicting future values from historical data.
Monitoring and anomaly detection: tracking metrics over time and flagging outliers.
Forecasting example
Common time-series query patterns
Most time-series work comes down to a few operations: filtering to a time window, grouping into time buckets (hourly or daily aggregates), computing rolling or moving statistics, and finding the most recent value at or before a point in time. DuckDB handles all of these in plain SQL.
Time bucketing
Group raw events into fixed intervals with date_trunc (or a time_bucket-style expression):
Copy code
SELECT date_trunc('hour', ts) AS hour, avg(value) AS avg_value
FROM sensor_readings
GROUP BY ALL
ORDER BY ALL;
Moving averages with window functions
Here is a window function computing a 7-day moving average of daily sales.
Copy code
SELECT
date,
sales,
AVG(sales) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM daily_sales
ORDER BY date;
ASOF joins
A signature time-series operation is matching each row to the most recent prior row in another table — for example, pairing each trade with the latest quote at or before it. DuckDB supports this directly with ASOF JOIN:
Copy code
SELECT t.trade_id, t.ts, q.price
FROM trades t
ASOF JOIN quotes q ON t.ts >= q.ts;
Time-series databases
A time-series database (TSDB) is a database purpose-built for time-indexed data that arrives continuously and is almost always queried by time range. Dedicated examples include InfluxDB, TimescaleDB (a Postgres extension), and Prometheus. They typically add storage optimized for append-heavy, time-ordered writes; strong compression for repetitive numeric metric data; automatic downsampling or rollups (storing coarser aggregates for older data); and retention policies that expire old data automatically.
Do you need a dedicated time-series database?
DuckDB is not a dedicated TSDB — it has no built-in retention policies or streaming-ingestion engine — but its SQL covers the analysis patterns above efficiently, and its columnar, vectorized engine is fast over large historical datasets. For ad hoc analysis of time-series data already sitting in Parquet or CSV, DuckDB is often a fast, zero-infrastructure alternative to standing up a TSDB. For high-frequency ingestion with automated retention and rollups, a purpose-built time-series database is generally the better long-term fit.
Examples of DuckDB for time-series data
The DuckDB in Action book, published by Manning (available as a free PDF download), uses a sample data set of power-generation data. The authors published some great time-series queries as a DuckDB Snippet. The Evidence team has also published SQL Prophet, showing time-series forecasting with DuckDB and Evidence.
Related terms
ASOF JOIN →
An ASOF JOIN matches each row in one table to the closest preceding (or following) row in another table based on an ordering column, commonly a timestamp -- a core building block for time-series analysis.
window functions →Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row, similar to aggregate functions,…
GENERATE_SERIES →GENERATE_SERIES(start, stop, step) produces a sequence of numeric, date, or timestamp values, commonly used to build calendars or fill gaps in time-series data.
timestamps →Timestamps are a fundamental data type in databases and programming languages that represent a specific point in time, typically including both the date and…
SQL analytics →SQL analytics refers to using SQL queries to analyze data and derive insights, typically working with large datasets stored in databases or data warehouses.
data pipeline →A data pipeline is a series of interconnected processes that extract data from various sources, transform it into a usable format, and load it into a…
FAQS
DuckDB is not a purpose-built time-series database — it has no automatic retention or streaming ingestion — but it handles time-series analysis (bucketing, moving averages, ASOF joins, forecasting inputs) efficiently in SQL and is a fast, zero-infrastructure option for analyzing historical time-series data stored in Parquet or CSV.
An ASOF JOIN matches each row to the most recent row in another table at or before its timestamp — for example, attaching the latest quote to each trade. It is a common time-series need that is awkward to express in standard SQL, and DuckDB supports it natively.
Use date_trunc (for example, date_trunc('hour', ts)) or a time_bucket-style expression in your GROUP BY to roll raw events up into hourly, daily, or other fixed intervals before aggregating.
A time series is the data itself — points ordered over time. A time-series database is storage software optimized for that data, adding time-ordered write performance, compression, downsampling, and retention on top.
