Re: date_trunc function in interval version

Yasir wrote on 19.05.2024 00:03:
> On Sun, May 19, 2024 at 2:20 AM Przemysław Sztoch
> <przemyslaw(at)sztoch(dot)pl <mailto:przemyslaw(at)sztoch(dot)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, 20minutes, 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;
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