Hyperfunctions overview

For additional hyperfunctions, use the TimescaleDB Toolkit PostgreSQL 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