DST diffs across the pond #JoelKallmanDay

It's that time of the year.
It's time to say goodbye to summer.
It's time to set the clock and get back to normal time.
...

It's also time for #JoelKallmanDay which is worldwide and so causes me once again to try and remember which weeks of the year is it that the time difference between me in Denmark and my friends in the USA is an hour different than most of the year?

The US and Europe both use Daylight Savings Time, but the changeover day is different on each side of the Atlantic. That typically causes confusion for a period of time each spring and fall where one side already has changed and the other side has not yet changed.

I can usually remember that the difference from me in Denmark to for example Chicago is 7 hours - most of the year. But I can never remember exactly when it is only 6 hours?

Luckily I have SQL, where I can generate the timestamp 10 AM for each day of the year in Chicago and figure out what time that is in Denmark:

with year as (
   select
      chicago
    , chicago at time zone 'Europe/Copenhagen' as copenhagen
   from (
      select
         to_timestamp_tz(
            '2025-' || to_char(level, 'FM099') || ' 10:00:00 America/Chicago'
          , 'YYYY-DDD HH24:MI:SS TZR'
         ) as chicago
      from dual
      connect by level <= 365
   )
)
select *
from year
match_recognize (
   order by chicago
   measures
      to_char(first(chicago), 'YYYY-MM-DD') as start_date
    , to_char(last (chicago), 'YYYY-MM-DD') as end_date
    , to_char(first(chicago)   , 'HH24') as chicago_hour
    , to_char(first(copenhagen), 'HH24') as copenhagen_hour
    , to_char(first(chicago)   , 'TZH:TZM') as chicago_offset
    , to_char(first(copenhagen), 'TZH:TZM') as copenhagen_offset
    , extract(timezone_hour from first(chicago))
       - extract(timezone_hour from first(copenhagen)) as diff
   one row per match
   pattern (strt same*)
   define
      same as extract(timezone_hour from chicago) - extract(timezone_hour from copenhagen)
            = extract(timezone_hour from prev(chicago)) - extract(timezone_hour from prev(copenhagen))
);

With those generated timestamps and that use of MATCH_RECOGNIZE, I get which periods Chicago is 6 hours away and which periods Chicago is 7 hours away:

START_DATE END_DATE   CH CO CHICAG COPENH       DIFF
---------- ---------- -- -- ------ ------ ----------
2025-01-01 2025-03-08 10 17 -06:00 +01:00         -7
2025-03-09 2025-03-29 10 16 -05:00 +01:00         -6
2025-03-30 2025-10-25 10 17 -05:00 +02:00         -7
2025-10-26 2025-11-01 10 16 -05:00 +01:00         -6
2025-11-02 2025-12-31 10 17 -06:00 +01:00         -7

So for this years #JoelKallmanDay on the 15th of October, the time zone difference is normal - it won't be until the 26th of October that confusion sets in and confusion will only last a single week.

Phew, it's not bad this time 😅. And once again MATCH_RECOGNIZE saves the day.