date_bin()

A function for converting a timestamp to the start of the nearest specified interval
This entry relates to a PostgreSQL feature which is part of PostgreSQL 14, due to be released in late 2021.

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 (stride INTERVAL, source TIMESTAMP, origin TIMESTAMP) → TIMESTAMP
date_bin (stride INTERVAL, source TIMESTAMP WITH TIME ZONE, origin TIMESTAMP WITH TIME ZONE) → TIMESTAMP WITH TIME ZONE

Change history

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
  • PostgreSQL 14 documentation: date_bin

Categories

System function

See also

date_trunc()