---
title: "time-series"
description: "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."
canonical: "https://motherduck.com/glossary/time-series/"
related:
  - title: "Aggregate functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/aggregate-functions/"
  - title: "DuckDB Tutorial For Beginners | MotherDuck"
    url: "https://motherduck.com/videos/duckdb-tutorial-for-beginners/"
  - title: "Future Casting the Modern Data Stack"
    url: "https://motherduck.com/blog/future-casting-the-modern-data-stack/"
---

# 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

<img src="https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/forecast_graph_0ede5ca8a2.svg">

## 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):

```sql
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.

<img src="https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/window_function_e3e77e310a.svg">

```sql
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](https://motherduck.com/glossary/asof-join/):

```sql
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](https://www.influxdata.com/), [TimescaleDB](https://www.timescale.com/) (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.

<glossary-callout guide="duckdb-book-brief" />

## Examples of DuckDB for time-series data
The *DuckDB in Action* book, published by Manning (available as a [free PDF download](https://motherduck.com/duckdb-book-brief/)), uses a sample data set of power-generation data. The authors published some great [time-series queries](https://duckdbsnippets.com/snippets/148/duckdb-in-action-examples-from-chapters-3-and-4-having-fun-with-power-production-measurements) as a DuckDB Snippet. The Evidence team has also published [SQL Prophet](https://github.com/evidence-dev/sql-prophet), showing time-series forecasting with DuckDB and Evidence.