group by 15-minute interval in PostgreSQL

here’s a sample from my table in Metabase PostgreSQL:

timestamp
2023-01-25T05:00:01+03:00
2023-01-25T05:01:01+03:00
2023-01-25T05:05:01+03:00
2023-01-25T05:13:01+03:00
2023-01-25T05:14:01+03:00
2023-01-25T05:20:01+03:00
2023-01-25T05:24:01+03:00
2023-01-25T05:29:01+03:00

I want to group by 15-minute interval and count events that happen in the interval:

timestamp            count
2023-01-25T05:00     5
2023-01-25T05:15     3

this didn’t work

SELECT date_trunc('hour', timestamp_column) + (floor(date_part('minute', timestamp_column) / 15) * interval '15 minute') AS interval_start,
       COUNT(*)
FROM my_table
GROUP BY interval_start

and raised an error:

   Code: 46. DB::Exception: Unknown function date_part: While processing dateTrunc('hour', timestamp) + (floor(date_part('minute', timestamp) / 15) * toIntervalMinute(15)) AS interval_start. 
(UNKNOWN_FUNCTION) (version 22.12.1.1752 (official build)) , server ClickHouseNode [uri=http://clickhouse:8123/table_group, options={socket_timeout=300000,use_server_time_zone_for_dates=true}]@-196298079

>Solution :

I have run your code and it is working fine:

CREATE TABLE my_table (
    timestamp_column TIMESTAMP WITH TIME ZONE
);

INSERT INTO my_table (timestamp_column)
VALUES 
    ('2023-01-25T05:00:01+03:00'),
    ('2023-01-25T05:01:01+03:00'),
    ('2023-01-25T05:05:01+03:00'),
    ('2023-01-25T05:13:01+03:00'),
    ('2023-01-25T05:14:01+03:00'),
    ('2023-01-25T05:20:01+03:00'),
    ('2023-01-25T05:24:01+03:00'),
    ('2023-01-25T05:29:01+03:00');

SELECT date_trunc('hour', timestamp_column) + (floor(date_part('minute', timestamp_column) / 15) * interval '15 minute') AS interval_start,
       COUNT(*)
FROM my_table
GROUP BY interval_start

enter image description here

In your error message we got info about ClickHouseNode. For ClickHOuse you can use toStartOfInterval instead as the date_part funcion is not avaible there. From the docs it seems to be something like:

toStartOfInterval(t, INTERVAL 15 minute)

but I am not able to test it.

Leave a Reply