PostgreSQL date_bin() Function

The PostgreSQL date_bin() function truncates the specified timestamp to the beginning of the nearest specified interval.

date_bin() Syntax

Here is the syntax of the PostgreSQL date_bin() function:

date_bin(stride INTERVAL, source TIMESTAMP, origin TIMESTAMP) -> TIMESTAMP

Parameters

stride

Required. It indicates the interval to use. For example 15 minutes means that to use 15 minutes as an interval, and there are four time points of the interval: 0 minute, 15 minute, 30 minute and 45 minute.

source

Required. The timestamp to process.

origin

Required. If it has a time part, the time part will be added to the result time part as an offset.

Return value

The PostgreSQL date_bin() function returns a timestamp that is the start of the most recent interval specified by the parameter stride.

date_bin() Examples

This example shows how to use a PostgreSQL date_bin() function to truncate time to the beginning of a 10-minute interval.

SELECT date_bin('10 minutes', TIMESTAMP '2022-05-16 12:41:13', TIMESTAMP '2001-01-01');

      date_bin
---------------------
 2022-05-16 12:40:00

Here, since the interval is 10 minutes, the possible minutes in the result time are: 0 minutes, 10 minutes, 20 minutes, 30 minutes, 40 minutes, 50 minutes. The start of the interval that 41 falls is 40.

You can also use 15 minutes as a interval, for example:

SELECT date_bin('15 minutes', TIMESTAMP '2022-05-16 12:41:13', TIMESTAMP '2001-01-01');

      date_bin
---------------------
 2022-05-16 12:30:00

Here, since the interval is 15 minutes, the possible minutes in the result time are: 0 minutes, 15 minutes, 30 minutes, 45 minutes.

If the origin argument has a time part, the time part is used as an offset for the result time, for example:

SELECT date_bin('15 minutes', TIMESTAMP '2022-05-16 12:41:13', TIMESTAMP '2001-01-01 00:05:02');

      date_bin
---------------------
 2022-05-16 12:35:02

Here, 05:02 as an offset will be added to 2022-05-16 12:30:00, the result is: 2022-05-16 12:35:02.

Likewise, you can use 1 day as the interval, for example:

SELECT date_bin('1 day', TIMESTAMP '2022-05-16 12:41:13', TIMESTAMP '2001-01-01');

      date_bin
---------------------
 2022-05-16 00:00:00

and with the origin argument,

SELECT date_bin('1 day', TIMESTAMP '2022-05-16 12:41:13', TIMESTAMP '2001-01-01 01:05:02');

      date_bin
---------------------
 2022-05-16 01:05:02