Re: date_trunc function in interval version

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.

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

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)