Re: AT TIME ZONE: "convert"?

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