Re: Best practices: Handling Daylight-saving time

For the record, if people search the archives for solutions.

Problem:

You need the time zone in use for <timestamp with time zone>
when data was previously inserted/updated.

Discussion:

> > > As others have mentioned, store timestamps on the server in UTC,
> >
> > 1) As long as I store them as <timestamp with time zone> I should
> > not need to care what they are stored as on the backend as
> > long as I provide the proper timezone for the client location.
> > Correct ?
> >
> > 2) If I then retrieve them as "... at time zone <...>" I will get
> > the equivalent time in the time zone of the retrieving client.
> > The same could be be achieved with "set timezone" per session.
> > Correct ?
>
> Yes and Yes
>
> > 3) If I retrieve them without "at time zone" I will get them with
> > the time zone that was stored in the first place, right ?
> [...]
>
> This would be news to me.
...
> Anyway, afaik, Postgres does not store the "original" timezone
> anywhere, and so could not possibly retrieve it.

Solution:

GnuMed now uses a trigger to store the time zone at the time
of data insertion. This was close enough for our needs at the
time (yes, we are old, triggers still need to return opaque on
some of our installations...).

Code:

\unset ON_ERROR_STOP
drop trigger tr_set_encounter_timezone on clin_encounter;
drop function f_set_encounter_timezone();
\set ON_ERROR_STOP 1

create function f_set_encounter_timezone() returns opaque as '
begin
    if TG_OP = ''INSERT'' then
        NEW.source_time_zone := (select (extract(timezone from (select now()))::text || ''seconds'')::interval);
    else
    NEW.source_time_zone := OLD.source_time_zone;
    end if;
    return NEW;
end;
' language 'plpgsql';

create trigger tr_set_encounter_timezone
    before insert or update on clin_encounter
    for each row execute procedure f_set_encounter_timezone()
;

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346