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

On Tue, Apr 15, 2008 at 04:04:20PM +0200, Karsten Hilbert wrote:
> On Tue, Apr 15, 2008 at 02:31:22PM +0100, Sam Mason wrote:
> > On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> > > 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).
> >
> > I was under the impression that "timestamp without time zone" does
> > precisely this.
> It doesn't. It keeps the time *value* untouched. But it
> doesn't even store *any* timezone information with it.

But I was under the impression that you didn't want any time zone
information. You wanted to know that that an appointment was at 3PM at
the patients local time, attempting to correct this for the local time
zone of any analyst is invalid.

> So,
> unless I *know* the original timezone by any other means I
> don't have *any* clue as to what point in time a particular
> timestamp value is. It less useful than "with time zone". The
> latter at least allows me to know the true (UTC-adjusted)
> time of an event without jumping through any hoops.

I must be missing something then, can you explain why the original time
zone matters?

> A different angle:
>
> Customer orders item at 23:15 on March 30. Item is on
> special offer March 30th only. DST change happens on March
> 30 to March-31. Dealer looks at orders and sees "item
> ordered March 31st 0:15" and does NOT apply the rebate for
> March 30th.

Except it probably wouldn't quite happen like this :) your time zone
is normally recorded as something like (for me) "Europe/London" which
then says that between these two dates this time zone applies, and
within these other two dates this other correction applies. So, in your
example, I'm not sure if there would be any visible difference between
with and without time zones. 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 (or would
it be 7, I can never remember!).

> Of course, it's the app developers fault, but the use case
> for keeping the original timezone (so it can be reapplied)
> is clearly there.

In some cases yes I'd agree, but I have a feeling the number of cases is
surprisingly small in practise.

Sam