Hyperfunctions overview

Functions that enable you to analyze time-series data efficiently. These functions provide essential capabilities for time bucketing, data distribution analysis, and gapfilling.

For additional hyperfunctions, use the TimescaleDB Toolkit Postgres extension.

Samples

Time bucketing with aggregates

Bucket temperature readings into 5-minute intervals and calculate statistics:

SELECT
  time_bucket('5 minutes', time) AS bucket,
  avg(temperature) AS avg_temp,
  first(temperature, time) AS first_temp,
  last(temperature, time) AS last_temp
FROM readings
GROUP BY bucket
ORDER BY bucket DESC;

Gapfilling missing data

Fill gaps in time-series data using last observation carried forward (LOCF):

SELECT
  time_bucket_gapfill('1 hour', time) AS bucket,
  device_id,
  locf(avg(temperature)) AS filled_avg_temp
FROM readings
WHERE time >= NOW() - INTERVAL '1 day'
GROUP BY bucket, device_id
ORDER BY bucket DESC;

Analyzing data distribution

Create a histogram of response times to understand distribution patterns:

SELECT histogram(response_time_ms, 0, 1000, 10)
FROM api_requests
WHERE time > NOW() - INTERVAL '1 day';

Approximate row counting

Get a fast estimate of table size without scanning all data:

SELECT approximate_row_count('readings');

Available functions

Time series utilities

• time_bucket(): bucket rows by time interval
• first(): get the first value ordered by another column
• last(): get the last value ordered by another column
• days_in_month(): calculate days in a month
• month_normalize(): normalize monthly metrics

Gapfilling

• time_bucket_gapfill(): bucket time and fill gaps in results
• locf(): last observation carried forward for filling gaps
• interpolate(): linear interpolation for filling gaps

Distribution analysis

• histogram(): create histograms to visualize data distribution
• approximate_row_count(): fast approximate count of rows in a table

Additional hyperfunctions

For advanced time-series analysis including statistical analysis, percentile approximation, state tracking, and more, see the TimescaleDB Toolkit API reference.

Deprecated hyperfunctions

• time_bucket_ng(): next generation time bucketing with additional features