Date and time are relevant to pretty much every PostgreSQL application. A new function was added to PostgreSQL 14 to solve a problem which has caused challenges for many users out there over the years: How can we map timestamps to time bins? The function is called date_bin.
Table of Contents
What people often do is round a timestamp to a full hour. That's commonly done using the date_trunc function. But what if you want to round data so that it fits into a 30-minute or a 15-minute grid?
Let's take a look at an example:
|
1 2 3 4 5 6 |
test=# SELECT date_bin('30 minutes', '2022-03-04 14:34', '1970-01-01 00:00'); date_bin ------------------------ 2022-03-04 14:30:00+00 (1 row) |
In that case, we want to round for the next half hour: the date_bin function will do the job. The first parameter defines the size of the bins. In our case, it's 30 minutes. The second parameter is the variable we want to round. What's interesting is the third variable. Look at it this way: If we round to a precision of 30 min., what value do we want to start at? Is it 30 min after the full hour, or maybe 35 min? The third value is therefore similar to a baseline where we want to start. In our case, we want to round to 30 min intervals relative to a full hour. Therefore the result is 14:30.
The following parameter sets the baseline to 14:31 and the interval should be 20 min. That's why it comes up with a result of 14:31:
|
1 2 3 4 5 6 |
test=# SELECT date_bin('20 minutes', '2022-03-04 14:49', '1970-01-01 14:31'); date_bin ------------------------ 2022-03-04 14:31:00+00 (1 row) |
If we use a slightly higher value, PostgreSQL will put it into the next time bin:
|
1 2 3 4 5 6 |
test=# SELECT date_bin('20 minutes', '2022-03-04 14:54', '1970-01-01 14:31'); date_bin ------------------------ 2022-03-04 14:51:00+00 (1 row) |
date_bin is super useful, because it gives us a lot of flexibility which can't be achieved using the date_trunc function alone. date_trunc can basically only round to full hours, full days, and so forth.
The date_bin function is adaptable and offers many new features on top of what PostgreSQL already has to offer.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
Nice addition indeed! Btw, about those "challenges over the years", as not everyone is on v14 yet - this is one way how to achieve similar for older versions:
SELECT date_trunc('hour'::text, now()) floor(date_part('minute'::text, now()) / 10::double precision) * '10min'::interval;