Skip to content

Time series utilities overview

Functions for time bucketing, ordered selection, and time-based calculations

Time series utilities provide essential functions for working with time-series data, including bucketing data by time intervals, selecting values based on temporal ordering, and performing time-based calculations.

Bucket temperature readings into 5-minute intervals and calculate the average:

SELECT time_bucket('5 minutes', time) AS five_min, avg(temperature)
FROM readings
GROUP BY five_min
ORDER BY five_min DESC;

Get the first and last temperature values for each device in 1-hour buckets:

SELECT
device_id,
time_bucket('1 hour', time) AS hour,
first(temperature, time) AS first_temp,
last(temperature, time) AS last_temp
FROM readings
GROUP BY device_id, hour
ORDER BY hour DESC;

Normalize monthly sales metrics to account for varying month lengths:

SELECT
time_bucket('1 month', sale_date) AS month,
SUM(amount) AS total_sales,
month_normalize(SUM(amount), time_bucket('1 month', sale_date)) AS normalized_sales
FROM sales
GROUP BY month
ORDER BY month;
  • first(): get the first value in one column when rows are ordered by another column
  • last(): get the last value in one column when rows are ordered by another column
  • days_in_month(): calculate the number of days in a month given a timestamp
  • month_normalize(): normalize a monthly metric based on the number of days in the month