SQLearnAcademy
←Function reference
Dates and timesPostgreSQL only

DATE_BIN

Rounds a moment down to the start of an interval of any length: 15 minutes, 6 hours, 10 days.

Syntax

DATE_BIN(INTERVAL '<step>', <timestamp>, <origin>)

Like DATE_TRUNC, but the step can be anything, not just an hour or a day. The third argument is the origin the intervals are counted from: with it, DATE_BIN(INTERVAL '15 minutes', …) gives 15:30, 15:45, 16:00. Most often used to group events into 15-minute slots or shifts. Added in PostgreSQL 14.

Data for the examples

orders

idcreated_atamount
12026-07-03 10:15:00120.50
22026-07-28 18:40:0080.25
32026-08-09 09:05:00199.99
42026-08-21 14:30:0045.00
52026-09-02 11:00:0064.75

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

To the start of the 15-minute slot
SELECT DATE_BIN(INTERVAL '15 minutes', TIMESTAMP '2026-09-13 15:47', TIMESTAMP '2026-01-01') AS bin_start;
bin_start
2026-09-13 15:45:00
Orders by 12-hour shifts
SELECT id, created_at,
DATE_BIN(INTERVAL '12 hours', created_at, TIMESTAMP '2026-01-01') AS shift_start
FROM orders;
idcreated_atshift_start
12026-07-03 10:15:002026-07-03 00:00:00
22026-07-28 18:40:002026-07-28 12:00:00
32026-08-09 09:05:002026-08-09 00:00:00
42026-08-21 14:30:002026-08-21 12:00:00
52026-09-02 11:00:002026-09-02 00:00:00
The origin shifts the intervals
SELECT DATE_BIN(INTERVAL '12 hours', TIMESTAMP '2026-09-13 10:15', TIMESTAMP '2026-01-01 08:00') AS shift_from_8;
shift_from_8
2026-09-13 08:00:00

With an origin at 08:00, shifts start at 08:00 and 20:00, so 10:15 belongs to the morning shift.

Watch out

The step can't contain months or years: DATE_BIN(INTERVAL '1 month', …) throws timestamps cannot be binned into intervals containing months or years. For months, use DATE_TRUNC('month', …).

Intervals are counted from the origin, not from midnight. For 15 minutes any “round” origin will do, but for 7 days it decides which day of the week an interval starts on.