postgresql - how to extract a list of past and known future offset changes for a timezone

Question

Postgresql is rather good at handling timezones, using the classic tzdata database.

The server can convert past and future timestamps between the different timezones, following the rules in tzdata (offsets, dst changes, ..)

Is there a simple and efficient way, for a given timezone and a given date range, to extract all the timestamps within that range when a timezone modification event occured ?

the result should more or less contain the equivalent of the output of the zdump linux command.

zdump -v /usr/share/zoneinfo/America/Los_Angeles | grep 2017

Sun Mar 12 09:59:59 2017 UTC = Sun Mar 12 01:59:59 2017 PST isdst=0 gmtoff=-28800
Sun Mar 12 10:00:00 2017 UTC = Sun Mar 12 03:00:00 2017 PDT isdst=1 gmtoff=-25200
Sun Nov  5 08:59:59 2017 UTC = Sun Nov  5 01:59:59 2017 PDT isdst=1 gmtoff=-25200
Sun Nov  5 09:00:00 2017 UTC = Sun Nov  5 01:00:00 2017 PST isdst=0 gmtoff=-28800

Answer 1

select d::date
from (
    select
        d at time zone 'America/Los_Angeles' as la,
        lead(d at time zone 'America/Los_Angeles') over (order by d) as la_,
        d 
    from generate_series (
        '2017-01-01'::timestamp,
        '2017-12-31', '1 day'
    ) gs (d)
) s
where la::time <> la_::time;
     d      
------------
 2017-03-12
 2017-11-05