In PostgreSQL, there are many functions that are being used to manipulate the date and time. One of the most important and commonly used functions is the date_bin() function. This function truncates the timestamp specified to the beginning of the nearest interval specified as an argument. The interval, origin timestamp, and the source timestamp are specified in the function as arguments.

In this article, we will have a deep look into the functioning of the date_bin() function.

How to Use date_bin() Function in PostgreSQL?

The date_bin() function enables us to “bin” the specified timestamp into given intervals in order to align it with the origin. The basic syntax of the function is given as:

date_bin(Stride INTERVAL, Source TIMESTAMP, Origin TIMESTAMP);

In the above syntax:

• The stride is the time interval. For example; if the stride is “10 minutes” it means that we will be using 10 minutes as our time interval. So there will be 6 time points for 10 minutes i.e. 0 min,10 min,20 min, 30 min,40 min, and 50 min.
• The source timestamp is the timestamp that we will be processing and working with.
• The third argument is the origin. If the timestamp of the origin contains the time points, we will use that time point as an offset for the source timestamp to truncate. We will cater to all these concepts with examples so it will be more understandable.
• The data type of Stride is INTERVAL, and the source and origin is TIMESTAMP. The function returns a "TIMESTAMP without time zone" data type value.

Let us expand on the subject with some examples.

Examples

Consider the query below as an example to make the topic more clear:

SELECT date_bin('10 minutes', TIMESTAMP '2023-08-29 10:34:19', TIMESTAMP '2000-05-01');

In the above query:

● The stride or the interval is specified as “10 minutes”.

● The timestamp that needs to be processed is “2023-08-29 10:34:19”.

● And lastly, the origin timestamp is “2000-05-01”.

Now we'll try to figure out how the query will work.

The stride/interval is “10 minutes” which means that the time points for this interval will be 0 mins,10 mins, 20 mins, 30 mins, 40 mins, and 50 mins. You can see that after 50 mins it will return to 0 mins then 10 min then 20 mins and so on. It means that the time points will remain the same for every hour and every day.

The same time points are repeated every hour hence every day. The same will be the time points every hour and in fact every day between “2000-05-01” to “2023-08-29 10:00:00”. Now we will see the time points from 10:00:00 onwards are; 10:10:00, 10:20:00, 10:30:00, 10:40:00, and so on.

So to truncate the source timestamp we basically look for the closest time point before the given source timestamp time. So in the above case, the closest time point before the “10:34:19” timestamp to truncate on is “10:30:00”. So the query will return it as output along with the date like this:

How it works:

This was a simple case with no time part in the origin timestamp. Now if there is a time part in the origin timestamp the scenario will be slightly different.

In case there is a time part in the origin timestamp, the basic functioning of the date_bin() function remains the same just an offset is added to the interval. The offset means that the interval will start from a specific time that is specified in the origin timestamp. Let's consider the following query to discuss this particular case:

SELECT date_bin('10 minutes', TIMESTAMP '2023-08-29 10:34:19', TIMESTAMP '2000-05-01 00:05:02');

In this case, a time part is added to the origin timestamp which is ”00:05:02”. This means that the intervals will start from “00:05:02” i.e. 05:02, 15:02, 25:02, 35:02, 45:02, and 55:02 for every hour and every day between “2000-05-01” to “2023-08-29 10:00:00”. Now we will see the time points from 10:05:02 onwards are; 10:15:02, 10:25:02, 10:35:02, 10:45:02, and so on. So to truncate the source timestamp we basically look for the closest time point before the given source timestamp time. So in the above case, the closest time point before the “10:34:19” timestamp to truncate on is “10:25:02”.

The query will return it as output along with the date like this:

This is the basic working of the date_bin() function.

Conclusion

The date_bin() function is used to truncate a specified timestamp into another timestamp using an interval based on the original timestamps. The interval, origin timestamp, and the source timestamp are specified in the function as arguments.