Re: AT TIME ZONE: "convert"?

Added TODO description:

* Allow TIMESTAMP WITH TIME ZONE to store the original timezone
information, either zone name or offset from UTC

If the TIMESTAMP value is stored with a time zone name, interval
computations should adjust based on the time zone rules, e.g. adding
24 hours to a timestamp would yield a different result from adding one
day.

---------------------------------------------------------------------------

Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > On Mon, Nov 01, 2004 at 11:00:10AM -0500, Tom Lane wrote:
> >> It does not really. By my reading of SQL99, the result should always be
> >> timestamptz, and the behavior when the input is already timestamptz
> >> should be that the new timezone spec is inserted while preserving the
> >> same absolute time (UTC-equivalent timestamp).
>
> > That's quite a different use of timestamptz. Does the SQL standard
> > decide what defines a timestamp with a timezone, does it only allow
> > the 'number of hours relative to UTC' or does it also allow different
> > places in the world.
>
> The SQL spec thinks that a timezone is a numeric offset from UTC, full stop.
>
> My vision of what we will actually support is either numeric offsets or
> named time zones --- basically, anything that you can now say either in
> SET TIMEZONE or as a zone name in timestamptz input ought to work in
> both places (as well as in AT TIME ZONE's second parameter). And a
> stored timestamptz value ought to retain the full information about what
> zone spec was given (for instance it should remember "PST8PDT" not just
> "PST"). There was extensive discussion about this just last week.
>
> > That's an interesting one, Is Australia/Sydney before or after
> > Australia/Brisbane. It is questionable if there is any meaningful order
> > to timezones. Alphabetical will make no-one happy, by
> > longatude/latitude is way too complex. Maybe base offset, then
> > alphabetical.
>
> We can probably arrange to sort by UTC offset, but the sort order within
> equal UTC offsets will likely be arbitrary (basically in order of the
> numeric identifiers we assign to time zone names ... though that might
> be user-configurable to some extent).
>
> > It's a backward incompatable change (or is it?)
>
> Some things will break, no doubt, but I don't think it will be too bad.
> Certainly no worse than the changes we've made in these data types in
> prior releases to move them closer to SQL spec.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073