bug(expr): date_trunc handles ambiguity during DST backward incorrectly

[xiangjinwu]

Describe the bug

Our implementation of date_trunc on timestamptz includes a rewrite: the time is converted to naive local time first, truncated, and then converted back. This introduces unnecessary ambiguity that is impossible to resolve correctly: the first conversion may convert different timestamptz to the same naive local time, which cannot be convert back to different values in the last step.

Error message/log

Silently produce a wrong value without errors. See repro below:

To Reproduce

RisingWave:

dev=> select extract(epoch from date_trunc('hour', '2023-11-05 01:40:00-07:00'::timestamptz, 'US/Pacific'));
      extract      
-------------------
 1699174800.000000
(1 row)

dev=> select extract(epoch from date_trunc('hour', '2023-11-05 01:40:00-08:00'::timestamptz, 'US/Pacific'));
      extract      
-------------------
 1699174800.000000
(1 row)

Expected behavior

PostgreSQL:

test=# select extract(epoch from date_trunc('hour', '2023-11-05 01:40:00-07:00'::timestamptz, 'US/Pacific'));
      extract      
-------------------
 1699171200.000000
(1 row)

test=# select extract(epoch from date_trunc('hour', '2023-11-05 01:40:00-08:00'::timestamptz, 'US/Pacific'));
      extract      
-------------------
 1699174800.000000
(1 row)

How did you deploy RisingWave?

No response

The version of RisingWave

No response

Additional context

Inspired by #12062 when reasoning whether date_trunc is always monotonic in all cases.