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

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. 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'd also hazard a guess that we don't hear about it more because most
> people just work within a single time zone and hence don't even notice
> the difference between the two.

Any DST change will highlight the difference quite clearly.
I don't even have to change locations. Any tstz stored
before a DST changeover will (quite logically) show up as
shifted one hour after the changeover. This happens twice a
year.

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.

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.

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