In PostgreSQL, the DATE_BIN() function enables us to “bin” a timestamp into a given interval aligned with a specific origin. In other words, we can use this function to map (or force) a timestamp to the nearest specified interval.

This can be handy when we want to truncate a timestamp to a given interval, for example a 10 minute interval. We can specify the interval (e.g. 10 minutes, 15 minutes, 30 minutes, etc), and we can specify the starting point for the interval. Therefore, we can have the interval starting at any odd time we want (it doesn’t need to start on the hour or anything like that).

The DATE_BIN() function works similar to DATE_TRUNC() when using full units (1 minute, 1 hour, etc.). The difference is that DATE_BIN() can truncate to an arbitrary interval.

The DATE_BIN() function was introduced in PostgreSQL 14.

Syntax

The syntax goes like this:

date_bin(stride, source, origin)

Where:

• source is a value expression of type timestamp or timestamp with time zone. (Values of type date are cast automatically to timestamp.) 
• stride is a value expression of type interval. This determines the recurring intervals that are used to “bin” source into. The stride interval must be greater than zero and cannot contain units of month or larger.
• origin is the origin of the stride. In other words, this is where the recurring interval starts.

The return value is of type timestamp or timestamp with time zone (depending on the input values), and it marks the beginning of the bin into which the source is placed.

Example

Here’s an example to demonstrate:

SELECT date_bin(
    '15 minutes', 
    TIMESTAMP '2022-01-01 15:07:00', 
    TIMESTAMP '2020-01-01'
    );

Result:

2022-01-01 15:00:00

Here, the original timestamp is 2022-01-01 15:07:00, but we wanted it to conform to a 15 minute interval that starts on 2020-01-01. Therefore we set our stride at 15 minutes, and our origin at 2020-01-01. The result is that our timestamp is adjusted to conform to the given interval.

Let’s tweak the origin value to show how that affects the outcome:

SELECT date_bin(
    '15 minutes', 
    TIMESTAMP '2022-01-01 15:07:00',
    TIMESTAMP '2020-01-01 00:10:00'
    );

Result:

2022-01-01 14:55:00

Now the origin starts 10 minutes after the hour, and so each 15 minute interval is based on that. Our source timestamp is a adjusted accordingly.

Let’s make another change. Let’s change the interval to 10 minutes:

SELECT date_bin(
    '10 minutes', 
    TIMESTAMP '2022-01-01 15:07:00', 
    TIMESTAMP '2020-01-01 00:10:00'
    );

Result:

2022-01-01 15:00:00

This time our timestamp is rounded to the nearest hour. This is because our 10 minute interval starts at 10 minutes passed the hour (i.e. it has an origin of 2020-01-01 00:10:00).

Now let’s make one more change. This time we’ll change the source timestamp:

SELECT date_bin(
    '10 minutes', 
    TIMESTAMP '2022-01-01 15:12:00', 
    TIMESTAMP '2020-01-01 00:10:00'
    );

Result:

2022-01-01 15:10:00

In this case I changed the minutes from 07 to 12, and we can see that our source timestamp now fits into the next stride/interval.