Re: Storage sizes for dates/times (documentation bug?) - Mailing list pgsql-general

On Mon, Apr 14, 2008 at 09:39:57PM -0400, Tom Lane wrote:

> considering alignment...).  ISTM that we have defined timestamptz
> in such a way that it solves many real-world problems, and timestamp
> also solves real-world problems,
No doubt about it.

> but the use-case for a timestamp plus an explicit time
> zone is much less clear.

GNUmed is a medical record. We store data about patients.
Our public database is accessed from within different time
zones. Imagine a user from Los Angeles and another one from
San Francisco. Later on it is not readily apparent wherefrom
a particular entry was made unless we store the originating
timezone and/or location (which we do). Even if the location
is stored one cannot *easily* derive the appropriate
timezone from it (and thereby the local time of entering
data).

This is particularly important in medicine - expected
hormone levels (say, cortisol) are markedly different at
different times of the day. Say, when a doctor in London
enters a cortisol level measured at 7:00am his time which is
later displayed in a location B with local time = GMT + 8
hours the level will appear to have been taken at 3:00pm --
for which the level is out of bounds.

Of course, the actual time stored in the database in UTC is
quite correct - it was indeed 3pm in location B when it was
7am in London. But we need to know the original local time
(and also be able to know UTC since we want to correlate
times). What we do now is to explicitely store the original
timezone with the timestamp for individual doctor-patient
encounters which is a bit of leap-of-faith but works (one
would actually have to store the timezone for each and every
timestamptz field). We then use that information to say
"SELECT ... AT TIME ZONE ... FROM ...".

Another scenario: Recently Germany underwent the annual DST
change. Suddenly times in the EMR entered before DST
appeared shifted one hour forward. Which, technically, is
correct - it's the same UTC time as before the DST onset,
just shifted by one more hour. They'll appear shifted back
to correct times when we go back to non-DST time. But then
DST-entered times will appear shifted back, too, until DST
starts again.

Of course, all this is solvable by explicitely keeping track
of which timestamps mean what but it'd be a whole lot easier
if one could just say:

    select value, ts_blood_drawn at original time zone
    from lab_results
    where type = 'cortisol';

I (for one) would happily store more bytes if that's what it
takes to reliably get at correct results (given the above
circumstances).

Yes, I know about tagged types but have shyed away from them
so far courtesy of them not being adjustable after the fact.

Should I be using a custom domain for this ?

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