Re: Comment on timezone and interval types

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Comment on timezone and interval types
Date: 2004-10-29 16:16:40
Message-ID: 17005.1099066600@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Stuart Bishop <stuart(at)stuartbishop(dot)net> writes:
> If you add a 'day' to a timestamp, it should be identical to adding 24
> hours.

No, it should not --- at least not when the addition traverses a DST
switchover time.

> For example, what is '2am April 3rd 2004 US/Eastern + 1 day'? 2am on
> April 4th 2004 didn't exist in that timezone because the clocks were put
> forward and that hour skipped.

The times right at the DST transition are questionable no matter what
we do, but that does not justify your claim that we do not need to fix
this. For instance, I think 10pm April 3rd (EST) plus '24 hours' ought
to be 11pm April 4th (EDT), but adding '1 day' ought to yield 10pm EDT.
There isn't really any ambiguity about what people will consider the
right answer there.

I think your example has about as much validity as claiming that we
shouldn't support "+ '1 month'" because it's not clear what to do when
adding '1 month' to 'Jan 31'. Yes, you end up having to define some
corner-case behaviors, but that doesn't render the main cases worthless.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Guy Fraser 2004-10-29 17:14:31 Re: Comment on timezone and interval types
Previous Message Cott Lang 2004-10-29 16:12:26 UTF-8 -> ISO8859-1 conversion problem