Re: Storage sizes for dates/times (documentation bug?)

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