DuckDB: Truncate a timestamp to a 15 minute interval?

Question

Is there function or equivalent that will truncate a timestamp to 15 minute intervals, e.g. the logical equivalent of

    select date_trunc('15 minutes', timestamp)?

Answer 1

time_bucket(interval, timestamp) will do this. This example shows both time_bucket and a modified Postgresql solution.

-- Truncate timestamps to N minute intervals. In this test, N=15.
WITH my_table(timestamp_column) as (
VALUES 
    (timestamp with time zone '2023-01-25T05:00:00+03:00'),
    (timestamp with time zone '2023-01-25T05:01:00+03:00'),
    (timestamp with time zone '2023-01-25T05:02:00+03:00'),
    (timestamp with time zone '2023-01-25T05:03:00+03:00'),
    (timestamp with time zone '2023-01-25T05:04:00+03:00'),
    (timestamp with time zone '2023-01-25T05:05:00+03:00'),
    (timestamp with time zone '2023-01-25T05:06:00+03:00'),
    (timestamp with time zone '2023-01-25T05:07:00+03:00'),
    (timestamp with time zone '2023-01-25T05:08:00+03:00'),
    (timestamp with time zone '2023-01-25T05:09:00+03:00'),
    (timestamp with time zone '2023-01-25T05:10:00+03:00'),
    (timestamp with time zone '2023-01-25T05:11:00+03:00'),
    (timestamp with time zone '2023-01-25T05:12:00+03:00'),
    (timestamp with time zone '2023-01-25T05:13:00+03:00'),
    (timestamp with time zone '2023-01-25T05:14:00+03:00'),
    (timestamp with time zone '2023-01-25T05:15:00+03:00'),
    (timestamp with time zone '2023-01-25T05:16:00+03:00')
)
SELECT
  timestamp_column,
  time_bucket(interval '15 minutes', timestamp_column) as with_bucket,
  date_trunc('hour', timestamp_column)
    + (floor(date_part('minute', timestamp_column) / 15)::int
        * interval '15 minute') AS like_postgres
FROM my_table
ORDER BY timestamp_column;

outputs:

timestamp_column	with_bucket	like_postgres
2023-01-25 04:00:00+02	2023-01-25 04:00:00+02	2023-01-25 04:00:00+02
2023-01-25 04:01:00+02	2023-01-25 04:00:00+02	2023-01-25 04:00:00+02
…	…	…
2023-01-25 04:14:00+02	2023-01-25 04:00:00+02	2023-01-25 04:00:00+02
2023-01-25 04:15:00+02	2023-01-25 04:15:00+02	2023-01-25 04:15:00+02
2023-01-25 04:16:00+02	2023-01-25 04:15:00+02	2023-01-25 04:15:00+02