Re: AT TIME ZONE: "convert"?

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).

Right now I get

regression=# begin;
BEGIN
regression=# select now();
now
-------------------------------
2004-11-01 10:48:19.715019-05
(1 row)

regression=# select now() at time zone 'PST';
timezone
----------------------------
2004-11-01 07:48:19.715019
(1 row)

but once we redo timestamptz according to recent discussion I would
expect the last result to be "2004-11-01 07:48:19.715019-08" (or
possibly "2004-11-01 07:48:19.715019 PST" depending on DateStyle
settings).

> ... will the "converted" timestamp value be the same if compared with
> '=' operator?

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.

regards, tom lane