Re: AT TIME ZONE: "convert"?

On Mon, Nov 01, 2004 at 11:00:10AM -0500, Tom Lane wrote:
> David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> writes:
> > So the question remains, does AT TIME ZONE already do
> > what it's supposed to do (according to SQL standard, that is)
>
> 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.

> Certainly not. We can't have timestamptz values that are in fact distinct
> comparing as equal. My guess is that the sort order for timestamptz
> should be UTC-equivalent time as major sort key, with equal UTC times
> sorted somehow on their timezone specs.

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.

It's a backward incompatable change (or is it?), and the current result
is useful in a sense...
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.