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

Does it have to be in postrgres? It may be easier to do this in application code. — Matt Johnson-Pint, Jul 28 '17 at 21:53
well I'd rather have everything in one place and be able to easily do JOINs. — Jerome WAGNER, Jul 29 '17 at 08:20

score 1 · Answer 1 · answered Jul 29 '17 at 12:21

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

answered Jul 29 '17 at 12:21

Clodoaldo Neto

118,695
26
233
260

Interesting. This gives you the date (does it work for positive & negative offsets ?) but not the exact timestamp when the modifications occur. Would it be efficient when hour/seconds are used in the serie ? – Jerome WAGNER Jul 29 '17 at 18:32

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

1 Answers1