On Wed, Aug 20, 2025 at 5:13 PM Przemysław Sztoch <[email protected]> wrote:
> On 8/20/2025 9:52 PM, Kirk Wolak wrote: > > On Mon, May 20, 2024 at 11:58 AM Przemysław Sztoch <[email protected]> > 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 > > 1. date_bin works good if you do not have changed time zone (for example > from summer to winter time). > > date_bin simply adds constant interval - if you want to round your time to > 3 hours, 6 hours or 12 hours then you have problem if you want to pass time > zone changing point, because some times you want to add interval without > lack hour or add interval with extra hour. > > original date_trunc works very good with DST problem, but has limited > granularity, you can't round timestamp to 5 min, 10 min, 30 min, 3 hours, 6 > hours etc. > > My data_trunc version with interval as period argument is able to > correctly overcome the time points at which the change from summer to > winter time and vice versa occurred for custom periods. > > Additionally, it does not require specifying the origin time, which can > sometimes be very difficult to determine. You can't simple use '0001-01-01 > 00:00:00' because it is problematic for some timezones. > > Then it must be defined differently for each time zone. This complicates > queries when you work with different zones. > -- > Przemysław Sztoch | Mobile +48 509 99 00 66 > Again, I want to help you get this reviewed, accepted. But I cannot tell the difference between date_bin() and what you are proposing. You are "Describing" the problem as dealing with "timezones". >From a previous post: >> Robert Haas: 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) ... We are both asking. SHOW us lines with date_bin() and your trunc_date() function. So we can see the differences, side by side. I've never used date_bin(), but I am interested, because I've had to do 15 Minute intervals recently and it was "Wordy". Kirk
