date_bin()
date_bin() is a system function for coercing an arbitrary timestamp to the start of the nearest specified interval. For example, for scheduling purposes it may be desirable to determine which particular 15 minute interval within an hour a timestamp is associated with.
date_bin() was added in PostgreSQL 14.
Usage
date_bin (strideINTERVAL,sourceTIMESTAMP,originTIMESTAMP) →TIMESTAMP
date_bin (strideINTERVAL,sourceTIMESTAMP WITH TIME ZONE,originTIMESTAMP WITH TIME ZONE) →TIMESTAMP WITH TIME ZONE
Change history
- PostgreSQL 14
- added (commit 49ab61f0)
Examples
Basic usage example for date_bin():
postgres=# SELECT NOW(), date_bin('10 minutes', NOW(), '2001-01-01');
now | date_bin
-------------------------------+------------------------
2021-03-25 17:25:56.817198+02 | 2021-03-25 17:20:00+02
(1 row)
Here, the time 17:25:56.817198+02 has been "rounded down" to the most recent regular 10 minute mark, i.e.: 2021-03-25 17:20:00+02.
If the timestamp provided as the third parameter (the "origin") is later than the input timestamp, the returned timestamp will be "rounded up" :
postgres=# SELECT now(), date_bin('10 minutes', now(), '2999-01-01');
now | date_bin
-------------------------------+------------------------
2021-03-25 18:30:16.082261+02 | 2021-03-25 18:40:00+02
(1 row)
If it contains a particular time, that will be used as an offset for the returned timestamp, e.g.:
postgres=# SELECT now(), date_bin('10 minutes', now(), '1999-01-01 00:05:00');
now | date_bin
------------------------------+------------------------
2021-03-25 18:32:09.61745+02 | 2021-03-25 18:25:00+02
(1 row)
It is not possible to use months or years as the interval:
postgres=# SELECT date_bin('10 years', '2021-01-01', '2001-01-01');
ERROR: timestamps cannot be binned into intervals containing months or years
References
- PostgreSQL 14 documentation: date_bin
Useful links
- Waiting for PostgreSQL 14 - Add date_bin function - March 2021 blog article by depesz