24.1. Grouping Timestamps into Time Intervals (time_bucket) #
Required privileges: Postgres Pro AXE administrator only. For a full list of stored procedures and privileges, refer to Section 12.1.
Execute the following command on the Postgres Pro AXE server:
SELECT time_bucket(INTERVAL 'time_interval',timestamp_column[,alignment_timestamp]) ON CONFLICT DO NOTHING;
Where:
time_interval: The time interval for grouping timestamps, e.g.,1 houror15 minutes.timestamp_column: The column with timestamps that must be grouped into time intervals.alignment_timestamp: The timestamp that determines the starting point for time intervals. By default, intervals start at the Unix epoch, so the first interval starts at 1970-01-01 00:00:00 UTC.Optional parameter.
Postgres Pro AXE returns the starting point of the time interval for each timestamp as a TIMESTAMP value.
Example 24.1. Executing the time_bucket Stored Procedure
Grouping events by hour:
SELECT time_bucket(INTERVAL '1 hour', created_at) AS hour_bucket, COUNT(*) FROM events GROUP BY hour_bucket ORDER BY hour_bucket;
Grouping sensor data by 15-minute time intervals:
SELECT time_bucket(INTERVAL '15 minutes', timestamp_col), AVG(value) FROM sensor_data WHERE timestamp_col >= '2024-01-01' GROUP BY 1 ORDER BY 1;