Contents

date_bin()

A function for converting a timestamp to the start of the nearest specified interval

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

Emulating date_bin() in PostgreSQL 13 and earlier

A function such as the following should provide equivalent functionality to date_bin() in PostgreSQL 13 and earlier.

CREATE OR REPLACE FUNCTION date_bin (
  stride INTERVAL,
  source_ts TIMESTAMPTZ,
  base_ts TIMESTAMPTZ)
RETURNS TIMESTAMPTZ
LANGUAGE SQL
IMMUTABLE
AS $$
SELECT
  base_ts
  + FLOOR(EXTRACT(epoch FROM source_ts - base_ts) / EXTRACT(epoch FROM trunc_period))::BIGINT
  * trunc_period;
$$;

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()