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
