On Wed, Apr 16, 2008 at 05:09:56PM +0200, Karsten Hilbert wrote: > On Tue, Apr 15, 2008 at 03:31:46PM +0100, Sam Mason wrote: > > But I was under the impression that you didn't want any time zone > > information. > Wrong impression. Doh, yes. > > I must be missing something then, can you explain why the original time > > zone matters? > > a) I want to be able to display when a patient's appointment > happened in local time. > > b) I must be able to aggregate appointments from different > time zones into a coherent EMR. For that I need to be able > to map them onto, say, UTC. Hum, what's an "EMR"? > Taken together this could be served nicely by a UTC-storing, > local-tz-remembering timestamp. Why not do: CREATE TYPE tstz AS ( ts TIMESTAMP WITH TIME ZONE, tz TEXT ); And use this instead? > > If you actually hardcoded your timezone as > > GMT+6, or whatever, then yes it may be different. But only if you went > > around at midnight March 31st, changing computers to be GMT+5 > The machines do that by themselves. What sort of machines do this? With computers I've used, if its time zone is set to the local time of some specific location then yes it will. If you set it to some specific offset then no it won't. These are independant cases, and not the one I was drawing your attention to above. These cases are also independant of the original problem as well. If it's adjusting for local time, then it'll know when to apply DST offsets. I don't think a timestamp should ever change just because you're looking at from different DST values. I think this is why TIMEs are somewhat awkward beasts, I've tried to stay away from them because I can't build a consistant model of how they should function. If TIME values are only used to store values and not to perform any calculations on then I see some utility. > > In some cases yes I'd agree, but I have a feeling the number of cases is > > surprisingly small in practise. > The sampling may not be that large but when the problem is > there it is painful. Yes, I'm sure it is! > Basically, akin to "there's no such thing as plain text" > there should be "there's no such thing as a timezone-less > timestamp". Or maybe, a programming language should allow you to define your own abstractions if the defaults don't fit. Sam