Distinguishing between times during a daylight savings changeover

Question

I was looking through some outputs that were presented from a UK timezone that crossed the daylight savings changeover (For any unaware, during the morning hours of the last Sunday of October, 1am is repeated to subtract an hour, and likewise on the last Sunday of March, it shifts forwards by 1 hour). The output showed events that appeared to go backwards in time - obviously this is a result of the hour resetting, however without any time offset listings, the only logical way to ascertain the correct time was to see their order. I understand that Postgres stores timestamp with timezone internally at UTC, but when timezone is specified the offset is ommitted e.g.

select
    '2019-10-27 00:30:00 UTC'::timestamptz at time zone 'Europe/London' time1,
    '2019-10-27 01:30:00 UTC'::timestamptz at time zone 'Europe/London' time2,
    '2020-03-29 00:30:00 UTC'::timestamptz at time zone 'Europe/London' time3,
    '2020-03-29 01:30:00 UTC'::timestamptz at time zone 'Europe/London' time4;

Below you can see October changeovers will give the exact same time during the 00:30 and 01:30 period, but it does not display the timezone offset so there is no way to tell the difference between the two.

        time1        |        time2        |        time3        |        time4        
---------------------+---------------------+---------------------+---------------------
 2019-10-27 01:30:00 | 2019-10-27 01:30:00 | 2020-03-29 00:30:00 | 2020-03-29 02:30:00

This is obviously not just a problem that affects only Postgres, and the output was to a logfile that does not use offsets annoyingly so there would be no clear way to retrospectively cast the times. I was lucky that the order does tell the difference, but supposing there was only a single event during such a 2 hour period what would be the best practice to handle this (reasonably irregular) issue in the future? Can offsets be shown only during these periods or is there some other solution?

Answer 1

The solution is simple: don't log local timestamps.

Either log the time zone information along with the timestamps or convert them to UTC before logging:

SET timezone = 'Europe/London';

select                         
    '2019-10-27 00:30:00 UTC'::timestamptz time1,
    '2019-10-27 01:30:00 UTC'::timestamptz time2,
    '2020-03-29 00:30:00 UTC'::timestamptz time3,
    '2020-03-29 01:30:00 UTC'::timestamptz time4;

         time1          |         time2          |         time3          |         time4          
------------------------+------------------------+------------------------+------------------------
 2019-10-27 01:30:00+01 | 2019-10-27 01:30:00+00 | 2020-03-29 00:30:00+00 | 2020-03-29 02:30:00+01
(1 row)

select                         
    '2019-10-27 00:30:00 UTC'::timestamptz at time zone 'UTC' time1,
    '2019-10-27 01:30:00 UTC'::timestamptz at time zone 'UTC' time2,
    '2020-03-29 00:30:00 UTC'::timestamptz at time zone 'UTC' time3,
    '2020-03-29 01:30:00 UTC'::timestamptz at time zone 'UTC' time4;

        time1        |        time2        |        time3        |        time4        
---------------------+---------------------+---------------------+---------------------
 2019-10-27 00:30:00 | 2019-10-27 01:30:00 | 2020-03-29 00:30:00 | 2020-03-29 01:30:00
(1 row)