Re: date_trunc function in interval version
| От | Kirk Wolak |
|---|---|
| Тема | Re: date_trunc function in interval version |
| Дата | |
| Msg-id | CACLU5mSsuGZneX2eoVpx2aqavbDgcbgjJE_KsyzYthixteAEAg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: date_trunc function in interval version (Przemysław Sztoch <przemyslaw@sztoch.pl>) |
| Ответы |
Re: date_trunc function in interval version
|
| Список | pgsql-hackers |
On Mon, May 20, 2024 at 11:58 AM Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:
Yasir wrote on 19.05.2024 00:03:...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.
Please, use it with timestamptz for '2 hours' or '3 hours' interval.
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
Forgive me, I saw this in the CF and wanted to review it because this looked useful.
I cannot tell from your output what the differences would be vs. your proposed date_trunc().
I was actually expecting columns: RowNum(), ts, date_bin, new date_trunc()
Where you explained the differences (maybe using the row number).
It appears your issue is the 36 Minutes. And it does beg the question "Where is that coming from".
Finally, I assume that: even if you could fix it by using "AT UTC" to do the grouping, and then change it to 'Europe/Warsaw'...
That 36 minutes probably creeps back in.
It "Feels" like the wrong answer, considering the inputs.
Finally... NOBODY Chimed in after you provided this evidence. Was this accepted as proof, or was MORE expected?
Kirk
Where you explained the differences (maybe using the row number).
It appears your issue is the 36 Minutes. And it does beg the question "Where is that coming from".
Finally, I assume that: even if you could fix it by using "AT UTC" to do the grouping, and then change it to 'Europe/Warsaw'...
That 36 minutes probably creeps back in.
It "Feels" like the wrong answer, considering the inputs.
Finally... NOBODY Chimed in after you provided this evidence. Was this accepted as proof, or was MORE expected?
Kirk
В списке pgsql-hackers по дате отправления: