Re: date_trunc function in interval version

On Mon, May 20, 2024 at 11:58 AM Przemysław Sztoch <przemyslaw(at)sztoch(dot)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