Handling DST changes in PostgreSQL

So most DBAs would tell you the best way to handle Daylight Saving Time in the database is to run your server in UTC time and just avoid the complications all together. That is definitely ideal! But not everyone follows that advice and if a system has been running in a timezone with DST for a long time, changing to UTC can be a huge hassle.

One big issue that can crop up due to DST is with systems that do time-based replication from one database to another via specialized jobs (not streaming replication or the built in master/slave stuff). An example would be replicating an insert-only table for archival purposes and doing it incrementally based on a timestamp column in the table. You need a way to know when the change has occurred so the replication can handle it appropriately (ex. avoid duplication or missing data due to key violation errors when the clock is set back). It would also be nice to have a way that is universal and will account for when (not if) the DST days change.

One of my coworkers (depesz) came up with a handy little function to do this check that I implemented into the incremental replication mentioned above.

UPDATE: Thanks to Misha reporting an issue with to_char not always getting the correct timezone abbreviation, I’ve updated the function to instead use date_part (or extract works too) to more accurately get the timezone data to compare.

CREATE FUNCTION dst_change(date timestamp with time zone) RETURNS boolean
    LANGUAGE sql
    AS $_$
    SELECT date_part('timezone', date_trunc('day', now())) <> date_part('timezone',
    date_trunc('day', now()) + '1 day'::interval);
$_$;

This function is called every time the replication function runs to see if the timezone is different the next day. If it is, it returns true so you know you have to handle things a little differently than usual. This is a snippet of the code from our incremental replication function to see how it’s used

DECLARE
[...]
    v_now           timestamp with time zone := now();
    v_dstcheck      boolean := false;
    v_norun_start   int := 30;
    v_norun_stop    int := 230;
[...]

BEGIN
[...]
   SELECT * into v_dstcheck from otools.dst_change(v_now);
    IF v_dstcheck THEN
        IF to_number(to_char(v_now, 'HH24MM'), '0000') > v_norun_start AND
        to_number(to_char(v_now, 'HH24MM'), '0000') < v_norun_stop THEN
            [...]
            RETURN 'Cannot run during DST time change';
        END IF;
    END IF;
[...]

We avoid the DST issues in this case by just not allowing the replication to run between 12:30am and 2:30am on a DST day. Our systems can handle having the replication delayed during that time period, so this was the easiest solution.

This is just one example of how to use that function, but it at least this gives you an idea of how a system that runs in a DST timezone can handle these types of issues.