Обсуждение: date_trunc function in interval version
Hello.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour', '15 minute', '10 second' etc.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour', '15 minute', '10 second' etc.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Przemysław Sztoch | Mobile +48 509 99 00 66
Вложения
Hi
pá 22. 12. 2023 v 20:26 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl> napsal:
Hello.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour', '15 minute', '10 second' etc.
should not be named interval_trunc instead? In this case the good name can be hard to choose, but with the name date_trunc it can be hard to find it.
Regards
Pavel
--
Przemysław Sztoch | Mobile +48 509 99 00 66
In my opinion date_trunc is very good name.
Truncated data is timestamp type, not interval.
First parameter has same meaning in original date_trunc and in my new version.
New version provides only more granularity.
Pavel Stehule wrote on 12/22/2023 8:43 PM:
Truncated data is timestamp type, not interval.
First parameter has same meaning in original date_trunc and in my new version.
New version provides only more granularity.
Pavel Stehule wrote on 12/22/2023 8:43 PM:
Hipá 22. 12. 2023 v 20:26 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl> napsal:Hello.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour', '15 minute', '10 second' etc.should not be named interval_trunc instead? In this case the good name can be hard to choose, but with the name date_trunc it can be hard to find it.RegardsPavel--
Przemysław Sztoch | Mobile +48 509 99 00 66
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Przemysław Sztoch | Mobile +48 509 99 00 66
On Sat, Dec 23, 2023 at 5:26 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote: > > In my opinion date_trunc is very good name. > Truncated data is timestamp type, not interval. > First parameter has same meaning in original date_trunc and in my new version. > New version provides only more granularity. I haven't looked at the patch, but your description sounds awfully close to date_bin(), which already takes an arbitrary interval.
Hi
pá 22. 12. 2023 v 23:25 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl> napsal:
In my opinion date_trunc is very good name.
Truncated data is timestamp type, not interval.
First parameter has same meaning in original date_trunc and in my new version.
New version provides only more granularity.
ok, I miss it.
Regards
Pavel
Pavel Stehule wrote on 12/22/2023 8:43 PM:Hipá 22. 12. 2023 v 20:26 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl> napsal:Hello.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour', '15 minute', '10 second' etc.should not be named interval_trunc instead? In this case the good name can be hard to choose, but with the name date_trunc it can be hard to find it.RegardsPavel--
Przemysław Sztoch | Mobile +48 509 99 00 66--
Przemysław Sztoch | Mobile +48 509 99 00 66
so 23. 12. 2023 v 13:33 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
Hipá 22. 12. 2023 v 23:25 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl> napsal:In my opinion date_trunc is very good name.
Truncated data is timestamp type, not interval.
First parameter has same meaning in original date_trunc and in my new version.
New version provides only more granularity.ok, I miss it.
I was confused - I am sorry, I imagined something different. Then the name is correct.
Regards
Pavel
Pavel Stehule wrote on 12/22/2023 8:43 PM:Hipá 22. 12. 2023 v 20:26 odesílatel Przemysław Sztoch <przemyslaw@sztoch.pl> napsal:Hello.
There is date_trunc(interval, timestamptz, timezone) function.
First parameter can be '5 year', '2 month', '6 hour', '3 hour', '15 minute', '10 second' etc.should not be named interval_trunc instead? In this case the good name can be hard to choose, but with the name date_trunc it can be hard to find it.RegardsPavel--
Przemysław Sztoch | Mobile +48 509 99 00 66--
Przemysław Sztoch | Mobile +48 509 99 00 66
date_bin has big problem with DST.
In example, if you put origin in winter zone, then generated bin will be incorrect for summer input date.
date_trunc is resistant for this problem.
My version of date_trunc is additionally more flexible, you can select more granular interval, 12h, 8h, 6h, 15min, 10 min etc...
John Naylor wrote on 23.12.2023 01:32:
In example, if you put origin in winter zone, then generated bin will be incorrect for summer input date.
date_trunc is resistant for this problem.
My version of date_trunc is additionally more flexible, you can select more granular interval, 12h, 8h, 6h, 15min, 10 min etc...
John Naylor wrote on 23.12.2023 01:32:
On Sat, Dec 23, 2023 at 5:26 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:In my opinion date_trunc is very good name. Truncated data is timestamp type, not interval. First parameter has same meaning in original date_trunc and in my new version. New version provides only more granularity.I haven't looked at the patch, but your description sounds awfully close to date_bin(), which already takes an arbitrary interval.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Przemysław Sztoch | Mobile +48 509 99 00 66
Hi, Please don't too-post on this list. The custom is to bottom-post or reply inline, and it's much easier to follow such replies. On 12/23/23 23:45, Przemysław Sztoch wrote: > date_bin has big problem with DST. > In example, if you put origin in winter zone, then generated bin will be > incorrect for summer input date. > > date_trunc is resistant for this problem. > My version of date_trunc is additionally more flexible, you can select > more granular interval, 12h, 8h, 6h, 15min, 10 min etc... > I'm not very familiar with date_bin(), but is this issue inherent or could we maybe fix date_bin() to handle DST better? In particular, isn't part of the problem that date_bin() is defined only for timestamp and not for timestamptz? Also, date_trunc() allows to specify a timezone, but date_bin() does not. In any case, the patch needs to add the new stuff to the SGML docs (to doc/src/sgml/func.sgml), which now documents the date_trunc(text,...) variant only. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> On 18 Feb 2024, at 05:29, Tomas Vondra <tomas.vondra@enterprisedb.com> wrote: > > I'm not very familiar with date_bin(), but is this issue inherent or > could we maybe fix date_bin() to handle DST better? > > In particular, isn't part of the problem that date_bin() is defined only > for timestamp and not for timestamptz? Also, date_trunc() allows to > specify a timezone, but date_bin() does not. > > > In any case, the patch needs to add the new stuff to the SGML docs (to > doc/src/sgml/func.sgml), which now documents the date_trunc(text,...) > variant only. Hi Przemysław, Please address above notes. I’ve flipped CF entry [0] to “Waiting on author”, feel free to switch it back. Thank you! Best regards, Andrey Borodin. [0] https://commitfest.postgresql.org/47/4761/
Tomas Vondra wrote on 18.02.2024 01:29:
When I saw date_bin in the documentation, I thought it solved all my problems.
Unfortunately, DST problems have many corner cases.
I tried to change date_bin several times, but unfortunately in some cases it would start working differently than before.
Apparently the functionality is identical to date_bin.Hi, Please don't too-post on this list. The custom is to bottom-post or reply inline, and it's much easier to follow such replies. On 12/23/23 23:45, Przemysław Sztoch wrote:date_bin has big problem with DST. In example, if you put origin in winter zone, then generated bin will be incorrect for summer input date. date_trunc is resistant for this problem. My version of date_trunc is additionally more flexible, you can select more granular interval, 12h, 8h, 6h, 15min, 10 min etc...I'm not very familiar with date_bin(), but is this issue inherent or could we maybe fix date_bin() to handle DST better?
When I saw date_bin in the documentation, I thought it solved all my problems.
Unfortunately, DST problems have many corner cases.
I tried to change date_bin several times, but unfortunately in some cases it would start working differently than before.
Updated.In any case, the patch needs to add the new stuff to the SGML docs (to doc/src/sgml/func.sgml), which now documents the date_trunc(text,...) variant only.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Przemysław Sztoch | Mobile +48 509 99 00 66
Вложения
On Mon, Mar 4, 2024 at 5:03 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote: > Apparently the functionality is identical to date_bin. > When I saw date_bin in the documentation, I thought it solved all my problems. > Unfortunately, DST problems have many corner cases. > I tried to change date_bin several times, but unfortunately in some cases it would start working differently than before. So, first of all, thanks for taking an interest and sending a patch. In order for the patch to have a chance of being accepted, we would need to have a clear understanding of exactly how this patch is different from the existing date_bin(). If we knew that, we could decide either that (a) date_bin does the right thing and your patch does the wrong thing and therefore we should reject your patch, or we could decide that (b) date_bin does the wrong thing and therefore we should fix it, or we could decide that (c) both date_bin and what this patch does are correct, in the sense of being sensible things to do, and there is a reason to have both. But if we don't really understand how they are different, which seems to be the case right now, then we can't make any decisions. And what that means in practice is that nobody is going to be willing to commit anything, and we're just going to go around in circles. Typically, this kind of research is the responsibility of the patch author: you're the one who wants something changed, so that means you need to provide convincing evidence that it should be. If someone else volunteers to do it, that's also cool, but it absolutely has to be done in order for there to be a chance of progress here. No committer is going to say "well, we already have date_bin, but Przemysław says his date_trunc is different somehow, so let's have both without understanding how exactly they're different." That's just not a realistic scenario. Just to name one problem, how would we document each of them? Users would expect the documentation to explain how two closely-related functions differ, but we will be unable to explain that if we don't know the answer ourselves. If you can't figure out exactly what the differences are by code inspection, then maybe one thing you could do to help unblock things here is provide some very clear examples of when they deliver the same results and when they deliver different results. Although there are no guarantees, that might lead somebody else to jump in and suggest an explanation, or further avenues of analysis, or some other helpful comment. Personally, what I suspect is that there's already a way to do what you want using date_bin(), maybe in conjunction with some casting or some calls to other functions that we already have. But it's hard to be sure because we just don't have the details. "DST problems have many corner cases" and "in some cases [date_bin] would start working differently than before" may be true statements as far as they go, but they're not very specific complaints. If you can describe *exactly* how date_bin fails to meet your expectations, there is a much better chance that something useful will happen here. -- Robert Haas EDB: http://www.enterprisedb.com
Robert Haas wrote on 5/15/2024 9:29 PM:
My function date_trunc ( interval, timestamp, ...) is similar to original function date_trunc ( text, timestamp ...) .
My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's the same with minutes.
We can round to 30 minutes, 20 minutes, 15 minutes, etc.
Using date_bin has a similar effect, but requires specifying the origin. According to this origin,
subsequent buckets are then calculated. The need to provide this origin is sometimes a very big problem.
Especially since you cannot use one origin when changing from summer to winter time.
If we use one origin for example begin of year: 2024-01-01 00:00:00 then:
# SET timezone='Europe/Warsaw';
# SELECT date_bin('1 day', '2024-03-05 11:22:33', '2024-01-01 00:00:00'), date_trunc('day', '2024-03-05 11:22:33'::timestamptz);
2024-03-05 00:00:00+01 2024-03-05 00:00:00+01 date_bin works ok, because we are before DST
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '2024-01-01 00:00:00'), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
2024-05-05 01:00:00+02 2024-05-05 00:00:00+02 date_bin has problem, because we are in May after DST
If anyone has an idea how to make date_bin work like date_trunc, please provide an example.
I would also like to thank Robert for presenting the matter in detail.On Mon, Mar 4, 2024 at 5:03 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:Apparently the functionality is identical to date_bin. When I saw date_bin in the documentation, I thought it solved all my problems. Unfortunately, DST problems have many corner cases. I tried to change date_bin several times, but unfortunately in some cases it would start working differently than before.So, first of all, thanks for taking an interest and sending a patch. In order for the patch to have a chance of being accepted, we would need to have a clear understanding of exactly how this patch is different from the existing date_bin(). If we knew that, we could decide either that (a) date_bin does the right thing and your patch does the wrong thing and therefore we should reject your patch, or we could decide that (b) date_bin does the wrong thing and therefore we should fix it, or we could decide that (c) both date_bin and what this patch does are correct, in the sense of being sensible things to do, and there is a reason to have both. But if we don't really understand how they are different, which seems to be the case right now, then we can't make any decisions. And what that means in practice is that nobody is going to be willing to commit anything, and we're just going to go around in circles. Typically, this kind of research is the responsibility of the patch author: you're the one who wants something changed, so that means you need to provide convincing evidence that it should be. If someone else volunteers to do it, that's also cool, but it absolutely has to be done in order for there to be a chance of progress here. No committer is going to say "well, we already have date_bin, but Przemysław says his date_trunc is different somehow, so let's have both without understanding how exactly they're different." That's just not a realistic scenario. Just to name one problem, how would we document each of them? Users would expect the documentation to explain how two closely-related functions differ, but we will be unable to explain that if we don't know the answer ourselves. If you can't figure out exactly what the differences are by code inspection, then maybe one thing you could do to help unblock things here is provide some very clear examples of when they deliver the same results and when they deliver different results. Although there are no guarantees, that might lead somebody else to jump in and suggest an explanation, or further avenues of analysis, or some other helpful comment. Personally, what I suspect is that there's already a way to do what you want using date_bin(), maybe in conjunction with some casting or some calls to other functions that we already have. But it's hard to be sure because we just don't have the details. "DST problems have many corner cases" and "in some cases [date_bin] would start working differently than before" may be true statements as far as they go, but they're not very specific complaints. If you can describe *exactly* how date_bin fails to meet your expectations, there is a much better chance that something useful will happen here.
My function date_trunc ( interval, timestamp, ...) is similar to original function date_trunc ( text, timestamp ...) .
My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's the same with minutes.
We can round to 30 minutes, 20 minutes, 15 minutes, etc.
Using date_bin has a similar effect, but requires specifying the origin. According to this origin,
subsequent buckets are then calculated. The need to provide this origin is sometimes a very big problem.
Especially since you cannot use one origin when changing from summer to winter time.
If we use one origin for example begin of year: 2024-01-01 00:00:00 then:
# SET timezone='Europe/Warsaw';
# SELECT date_bin('1 day', '2024-03-05 11:22:33', '2024-01-01 00:00:00'), date_trunc('day', '2024-03-05 11:22:33'::timestamptz);
2024-03-05 00:00:00+01 2024-03-05 00:00:00+01 date_bin works ok, because we are before DST
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '2024-01-01 00:00:00'), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
2024-05-05 01:00:00+02 2024-05-05 00:00:00+02 date_bin has problem, because we are in May after DST
If anyone has an idea how to make date_bin work like date_trunc, please provide an example.
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Przemysław Sztoch | Mobile +48 509 99 00 66
On Sun, May 19, 2024 at 2:20 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:
Robert Haas wrote on 5/15/2024 9:29 PM:I would also like to thank Robert for presenting the matter in detail.On Mon, Mar 4, 2024 at 5:03 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:Apparently the functionality is identical to date_bin. When I saw date_bin in the documentation, I thought it solved all my problems. Unfortunately, DST problems have many corner cases. I tried to change date_bin several times, but unfortunately in some cases it would start working differently than before.So, first of all, thanks for taking an interest and sending a patch. In order for the patch to have a chance of being accepted, we would need to have a clear understanding of exactly how this patch is different from the existing date_bin(). If we knew that, we could decide either that (a) date_bin does the right thing and your patch does the wrong thing and therefore we should reject your patch, or we could decide that (b) date_bin does the wrong thing and therefore we should fix it, or we could decide that (c) both date_bin and what this patch does are correct, in the sense of being sensible things to do, and there is a reason to have both. But if we don't really understand how they are different, which seems to be the case right now, then we can't make any decisions. And what that means in practice is that nobody is going to be willing to commit anything, and we're just going to go around in circles. Typically, this kind of research is the responsibility of the patch author: you're the one who wants something changed, so that means you need to provide convincing evidence that it should be. If someone else volunteers to do it, that's also cool, but it absolutely has to be done in order for there to be a chance of progress here. No committer is going to say "well, we already have date_bin, but Przemysław says his date_trunc is different somehow, so let's have both without understanding how exactly they're different." That's just not a realistic scenario. Just to name one problem, how would we document each of them? Users would expect the documentation to explain how two closely-related functions differ, but we will be unable to explain that if we don't know the answer ourselves. If you can't figure out exactly what the differences are by code inspection, then maybe one thing you could do to help unblock things here is provide some very clear examples of when they deliver the same results and when they deliver different results. Although there are no guarantees, that might lead somebody else to jump in and suggest an explanation, or further avenues of analysis, or some other helpful comment. Personally, what I suspect is that there's already a way to do what you want using date_bin(), maybe in conjunction with some casting or some calls to other functions that we already have. But it's hard to be sure because we just don't have the details. "DST problems have many corner cases" and "in some cases [date_bin] would start working differently than before" may be true statements as far as they go, but they're not very specific complaints. If you can describe *exactly* how date_bin fails to meet your expectations, there is a much better chance that something useful will happen here.
My function date_trunc ( interval, timestamp, ...) is similar to original function date_trunc ( text, timestamp ...) .
My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's the same with minutes.
We can round to 30 minutes, 20 minutes, 15 minutes, etc.
Using date_bin has a similar effect, but requires specifying the origin. According to this origin,
subsequent buckets are then calculated. The need to provide this origin is sometimes a very big problem.
Especially since you cannot use one origin when changing from summer to winter time.
If we use one origin for example begin of year: 2024-01-01 00:00:00 then:
# SET timezone='Europe/Warsaw';
# SELECT date_bin('1 day', '2024-03-05 11:22:33', '2024-01-01 00:00:00'), date_trunc('day', '2024-03-05 11:22:33'::timestamptz);
2024-03-05 00:00:00+01 2024-03-05 00:00:00+01 date_bin works ok, because we are before DST
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '2024-01-01 00:00:00'), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
2024-05-05 01:00:00+02 2024-05-05 00:00:00+02 date_bin has problem, because we are in May after DST
If anyone has an idea how to make date_bin work like date_trunc, please provide an example.
Here is an example which will make date_bin() to behave like date_trunc():
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '0001-01-01'::timestamp), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
date_bin | date_trunc
---------------------+------------------------
2024-05-05 00:00:00 | 2024-05-05 00:00:00+02
(1 row)
In general, to make date_bin work similarly to date_trunc in PostgreSQL, you need to set the interval length appropriately and use an origin timestamp that aligns with the start of the interval you want to bin.
Here's how you can use date_bin to mimic the behavior of date_trunc:
Truncate to the Start of the Year:
# SELECT date_bin('1 year', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Month:
# SELECT date_bin('1 month', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Day:
# SELECT date_bin('1 day', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Hour:
# SELECT date_bin('1 hour', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Minute:
# SELECT date_bin('1 minute', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '0001-01-01'::timestamp), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
date_bin | date_trunc
---------------------+------------------------
2024-05-05 00:00:00 | 2024-05-05 00:00:00+02
(1 row)
In general, to make date_bin work similarly to date_trunc in PostgreSQL, you need to set the interval length appropriately and use an origin timestamp that aligns with the start of the interval you want to bin.
Here's how you can use date_bin to mimic the behavior of date_trunc:
Truncate to the Start of the Year:
# SELECT date_bin('1 year', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Month:
# SELECT date_bin('1 month', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Day:
# SELECT date_bin('1 day', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Hour:
# SELECT date_bin('1 hour', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Minute:
# SELECT date_bin('1 minute', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Yasir wrote on 19.05.2024 00:03:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') AS three_hours_bin
FROM generate_series('2022-03-26 21:00:00+00'::timestamptz,
'2022-03-27 07:00:00+00'::timestamptz,
'30 min'::interval,
'Europe/Warsaw') AS ts;
ts | one_hour_bin | two_hours_bin | three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 22:30:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:00:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:30:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-27 00:00:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 00:30:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:00:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:30:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:00:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:30:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 04:00:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 04:30:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 05:00:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 05:30:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:00:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:30:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 07:00:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 07:30:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:00:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:30:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 09:00:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 06:36:00+02
(21 rows)
We have 36 minutes offset (historical time change).
If we use origin from current year, we have wrong value after DST too:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') AS three_hours_bin
FROM generate_series('2022-03-26 21:00:00+00'::timestamptz,
'2022-03-27 07:00:00+00'::timestamptz,
'30 min'::interval,
'Europe/Warsaw') AS ts;^C
postgres=# \e
ts | one_hour_bin | two_hours_bin | three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 22:30:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:00:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:30:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 00:30:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:00:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:30:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 03:30:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 04:00:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 04:30:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:30:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:00:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:30:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 07:30:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:00:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:30:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 07:00:00+02
(21 rows)
Please, use it with timestamptz for '2 hours' or '3 hours' interval.I would also like to thank Robert for presenting the matter in detail.
My function date_trunc ( interval, timestamp, ...) is similar to original function date_trunc ( text, timestamp ...) .
My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's the same with minutes.
We can round to 30 minutes, 20 minutes, 15 minutes, etc.
Using date_bin has a similar effect, but requires specifying the origin. According to this origin,
subsequent buckets are then calculated. The need to provide this origin is sometimes a very big problem.
Especially since you cannot use one origin when changing from summer to winter time.
If we use one origin for example begin of year: 2024-01-01 00:00:00 then:
# SET timezone='Europe/Warsaw';
# SELECT date_bin('1 day', '2024-03-05 11:22:33', '2024-01-01 00:00:00'), date_trunc('day', '2024-03-05 11:22:33'::timestamptz);
2024-03-05 00:00:00+01 2024-03-05 00:00:00+01 date_bin works ok, because we are before DST
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '2024-01-01 00:00:00'), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
2024-05-05 01:00:00+02 2024-05-05 00:00:00+02 date_bin has problem, because we are in May after DST
If anyone has an idea how to make date_bin work like date_trunc, please provide an example.Here is an example which will make date_bin() to behave like date_trunc():
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '0001-01-01'::timestamp), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
date_bin | date_trunc
---------------------+------------------------
2024-05-05 00:00:00 | 2024-05-05 00:00:00+02
(1 row)
In general, to make date_bin work similarly to date_trunc in PostgreSQL, you need to set the interval length appropriately and use an origin timestamp that aligns with the start of the interval you want to bin.
Here's how you can use date_bin to mimic the behavior of date_trunc:
Truncate to the Start of the Year:
# SELECT date_bin('1 year', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Month:
# SELECT date_bin('1 month', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Day:
# SELECT date_bin('1 day', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Hour:
# SELECT date_bin('1 hour', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Minute:
# SELECT date_bin('1 minute', timestamp_column, '0001-01-01'::timestamp) FROM your_table;--
Przemysław Sztoch | Mobile +48 509 99 00 66
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') AS three_hours_bin
FROM generate_series('2022-03-26 21:00:00+00'::timestamptz,
'2022-03-27 07:00:00+00'::timestamptz,
'30 min'::interval,
'Europe/Warsaw') AS ts;
ts | one_hour_bin | two_hours_bin | three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 22:30:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:00:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:30:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-27 00:00:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 00:30:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:00:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:30:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:00:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:30:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 04:00:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 04:30:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 05:00:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 05:30:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:00:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:30:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 07:00:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 07:30:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:00:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:30:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 09:00:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 06:36:00+02
(21 rows)
We have 36 minutes offset (historical time change).
If we use origin from current year, we have wrong value after DST too:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') AS three_hours_bin
FROM generate_series('2022-03-26 21:00:00+00'::timestamptz,
'2022-03-27 07:00:00+00'::timestamptz,
'30 min'::interval,
'Europe/Warsaw') AS ts;^C
postgres=# \e
ts | one_hour_bin | two_hours_bin | three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 22:30:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:00:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:30:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 00:30:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:00:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:30:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 03:30:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 04:00:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 04:30:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:30:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:00:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:30:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 07:30:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:00:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:30:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 07:00:00+02
(21 rows)
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Przemysław Sztoch | Mobile +48 509 99 00 66
Yasir wrote on 19.05.2024 00:03:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') AS three_hours_bin
FROM generate_series('2022-03-26 21:00:00+00'::timestamptz,
'2022-03-27 07:00:00+00'::timestamptz,
'30 min'::interval,
'Europe/Warsaw') AS ts;
ts | one_hour_bin | two_hours_bin | three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 22:30:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:00:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:30:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-27 00:00:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 00:30:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:00:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:30:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:00:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:30:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 04:00:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 04:30:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 05:00:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 05:30:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:00:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:30:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 07:00:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 07:30:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:00:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:30:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 09:00:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 06:36:00+02
(21 rows)
We have 36 minutes offset (historical time change).
If we use origin from current year, we have wrong value after DST too:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') AS three_hours_bin
FROM generate_series('2022-03-26 21:00:00+00'::timestamptz,
'2022-03-27 07:00:00+00'::timestamptz,
'30 min'::interval,
'Europe/Warsaw') AS ts;^C
postgres=# \e
ts | one_hour_bin | two_hours_bin | three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 22:30:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:00:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:30:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 00:30:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:00:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:30:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 03:30:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 04:00:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 04:30:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:30:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:00:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:30:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 07:30:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:00:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:30:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 07:00:00+02
(21 rows)
Please, use it with timestamptz for '2 hours' or '3 hours' interval.On Sun, May 19, 2024 at 2:20 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:I would also like to thank Robert for presenting the matter in detail.
My function date_trunc ( interval, timestamp, ...) is similar to original function date_trunc ( text, timestamp ...) .
My extension only gives more granularity.
We don't have a jump from hour to day. We can use 6h and 12h. It's the same with minutes.
We can round to 30 minutes, 20 minutes, 15 minutes, etc.
Using date_bin has a similar effect, but requires specifying the origin. According to this origin,
subsequent buckets are then calculated. The need to provide this origin is sometimes a very big problem.
Especially since you cannot use one origin when changing from summer to winter time.
If we use one origin for example begin of year: 2024-01-01 00:00:00 then:
# SET timezone='Europe/Warsaw';
# SELECT date_bin('1 day', '2024-03-05 11:22:33', '2024-01-01 00:00:00'), date_trunc('day', '2024-03-05 11:22:33'::timestamptz);
2024-03-05 00:00:00+01 2024-03-05 00:00:00+01 date_bin works ok, because we are before DST
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '2024-01-01 00:00:00'), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
2024-05-05 01:00:00+02 2024-05-05 00:00:00+02 date_bin has problem, because we are in May after DST
If anyone has an idea how to make date_bin work like date_trunc, please provide an example.Here is an example which will make date_bin() to behave like date_trunc():
# SELECT date_bin('1 day', '2024-05-05 11:22:33', '0001-01-01'::timestamp), date_trunc('day', '2024-05-05 11:22:33'::timestamptz);
date_bin | date_trunc
---------------------+------------------------
2024-05-05 00:00:00 | 2024-05-05 00:00:00+02
(1 row)
In general, to make date_bin work similarly to date_trunc in PostgreSQL, you need to set the interval length appropriately and use an origin timestamp that aligns with the start of the interval you want to bin.
Here's how you can use date_bin to mimic the behavior of date_trunc:
Truncate to the Start of the Year:
# SELECT date_bin('1 year', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Month:
# SELECT date_bin('1 month', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Day:
# SELECT date_bin('1 day', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Hour:
# SELECT date_bin('1 hour', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
Truncate to the Start of the Minute:
# SELECT date_bin('1 minute', timestamp_column, '0001-01-01'::timestamp) FROM your_table;
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') AS three_hours_bin
FROM generate_series('2022-03-26 21:00:00+00'::timestamptz,
'2022-03-27 07:00:00+00'::timestamptz,
'30 min'::interval,
'Europe/Warsaw') AS ts;
ts | one_hour_bin | two_hours_bin | three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 22:30:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:00:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-26 23:30:00+01 | 2022-03-26 22:36:00+01 | 2022-03-26 21:36:00+01 | 2022-03-26 20:36:00+01
2022-03-27 00:00:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 00:30:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:00:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 01:30:00+01 | 2022-03-27 00:36:00+01 | 2022-03-26 23:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:00:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 03:30:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 01:36:00+01 | 2022-03-26 23:36:00+01
2022-03-27 04:00:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 04:30:00+02 | 2022-03-27 03:36:00+02 | 2022-03-27 01:36:00+01 | 2022-03-27 03:36:00+02
2022-03-27 05:00:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 05:30:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:00:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 06:30:00+02 | 2022-03-27 05:36:00+02 | 2022-03-27 04:36:00+02 | 2022-03-27 03:36:00+02
2022-03-27 07:00:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 07:30:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:00:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 08:30:00+02 | 2022-03-27 07:36:00+02 | 2022-03-27 06:36:00+02 | 2022-03-27 06:36:00+02
2022-03-27 09:00:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 08:36:00+02 | 2022-03-27 06:36:00+02
(21 rows)
We have 36 minutes offset (historical time change).
If we use origin from current year, we have wrong value after DST too:
SET timezone TO 'Europe/Warsaw';
SELECT ts,
date_bin('1 hour'::interval, ts, '0001-01-01 00:00:00') AS one_hour_bin,
date_bin('2 hour'::interval, ts, '0001-01-01 00:00:00') AS two_hours_bin,
date_bin('3 hour'::interval, ts, '0001-01-01 00:00:00') AS three_hours_bin
FROM generate_series('2022-03-26 21:00:00+00'::timestamptz,
'2022-03-27 07:00:00+00'::timestamptz,
'30 min'::interval,
'Europe/Warsaw') AS ts;^C
postgres=# \e
ts | one_hour_bin | two_hours_bin | three_hours_bin
------------------------+------------------------+------------------------+------------------------
2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 22:30:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:00:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-26 23:30:00+01 | 2022-03-26 23:00:00+01 | 2022-03-26 22:00:00+01 | 2022-03-26 21:00:00+01
2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 00:30:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:00:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 01:30:00+01 | 2022-03-27 01:00:00+01 | 2022-03-27 00:00:00+01 | 2022-03-27 00:00:00+01
2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 03:30:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 00:00:00+01
2022-03-27 04:00:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 04:30:00+02 | 2022-03-27 04:00:00+02 | 2022-03-27 03:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 05:30:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:00:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 06:30:00+02 | 2022-03-27 06:00:00+02 | 2022-03-27 05:00:00+02 | 2022-03-27 04:00:00+02
2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 07:30:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:00:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 08:30:00+02 | 2022-03-27 08:00:00+02 | 2022-03-27 07:00:00+02 | 2022-03-27 07:00:00+02
2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 09:00:00+02 | 2022-03-27 07:00:00+02
(21 rows)
--
Przemysław Sztoch | Mobile +48 509 99 00 66
Przemysław Sztoch | Mobile +48 509 99 00 66