Re: [PATCH] handling transitions in timestamptz_trunc_*

Hi all,

On Mon, Sep 7, 2026 at 12:18 PM Alexandre Felipe
<o(dot)alexandre(dot)felipe(at)gmail(dot)com> wrote:
>
> This issue was noted by Zsolt during his review of my last patchset on SLOPE thead [1].
>
> date_trunc(unit,'1916-07-27 22:26:08+00', 'Europe/Athens')
>
> day 1916-07-28 00:00:00.000000
> hour 1916-07-27 23:34:52.000000
> min 1916-07-28 00:00:52.000000
>
> date_trunc documentation [1] states that
> The return value is likewise of type timestamp, timestamp with time zone, or interval, and it has all fields that are less significant than the selected one set to zero (or one, for day and month).
>
> At UTC we can be seen that truncated hour ends up before truncated days
>
> day 1916-07-27 22:25:08.000000
> hour 1916-07-27 22:00:00.000000
> min 1916-07-27 22:26:00.000000
>
> This happened because hour, there was a transition to the middle of the hour
> or the minute being truncated, after removing the seconds or minutes, the
> timestamp was nonexistent in the target time zone. But regardless of the
> existence, this revealed a problem with the current implementation that will
> only result in 0 minutes and 0 seconds if
> (1) every transition starts at the start of the hour i.e. hh:00:00 and or (2)
> every transition changes the offset by a multiple of 3600 seconds.
>
> Initially I was unsure about how this could be handled, divided between snapping
> to the transition, or moving further to the previous valid timestamp ending
> with zeroes.
>
> At some point I wondered why this worked for date_trunc('day', ...), checking
> the implementation and noticing this redotz applied only for days and larger
> somehow indicates that the intended behaviour was already encoded but
> disabled. So, I expect this to not be so polemic :)
>
>
> [1] https://www.postgresql.org/message-id/CAN4CZFO8RQi2j4cEYtQAik_wYL1emy8SeMrq7oja0pea2Xu42w%40mail.gmail.com
> [2] https://www.postgresql.org/docs/18/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
>

Thank you for the patch.
I reviewed and tested the patch. The change in
timestamptz_trunc_internal() looks correct to me. Had verified that
the existing redotz handling was previously applied only for day and
larger units, and this patch extends it to hour and minute truncation.
This correctly handles cases where truncation crosses a historical
timezone transition and the resulting local timestamp may be
non-existent. I also tested the reported 'Europe/Athens' transition
case using timestamps immediately before and after the transition. The
results matched the expected regression output from the patch and did
not find any issues with the implementation or the added regression
test.
This patch looks good to me.

Regards,
Solai