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.

Samples

Time bucketing

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;

Ordered selection

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;

Month normalization

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;

Available functions

Time bucketing

• time_bucket(): bucket rows by time interval to calculate aggregates
• time_bucket_ng(): next generation time bucketing with additional features

Ordered selection

• 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

Time utilities

• 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