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 (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
- PostgreSQL 14
- added (commit 49ab61f0)
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
References
- PostgreSQL 14 documentation: date_bin
Useful links
- PostgreSQL: mapping timestamps (date_bin) - April 2022 blog article by Hans-Jürgen Schönig / CyberTec
- Waiting for PostgreSQL 14 - Add date_bin function - March 2021 blog article by depesz