Question 89543 in PostgreSQL Question posted in
The official documentation can be found here.

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
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    PostgreSQL version 14+ adds date_bin() function. You can directly use date_bin() to extract the inforamtion you need.

    Here is the query:

    SELECT
   DATE_BIN('15 minutes', timestamp_column, TIMESTAMP '2001-01-01') AS interval_start,
   COUNT(1)
FROM
    my_table
GROUP BY
   interval_start
    interval_start count
    2023-01-25T02:00:00.000Z 5
    2023-01-25T02:15:00.000Z 3
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search