The Postgres manual says:

The AT TIME ZONE construct allows conversions of time stamps to
different time zones.

I'd guess most people would think what's meant here is something like
"unit conversion", and that the timestamp value stays the same (much
like 2 feet becomes 24 inches when it's being "converted"). But:

# SELECT NOW() = NOW() AT TIME ZONE 'UTC';
?column?
----------
f
(1 row)

--
dave

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

You misunderstand the TIMESTAMP WITH TIMEZONE type, it doesn't store
the timezone you gave it, it's just a point in time. Saying AT TIMEZONE
just converts it to a TIMESTAMP WITHOUT TIMEZONE with the local time it
was in the timezone you gave it. So you are complaring different
things. See:

# select now(), now() at time zone 'UTC';
now | timezone
-------------------------------+----------------------------
2004-11-01 14:07:24.563239+01 | 2004-11-01 13:07:24.563239
(1 row)

They have different hours and one has a timezone and the other doesn't.
Comparing these probably adds your local timezone to the one on the
right which means the result is false.

# select (now() at time zone 'UTC')::timestamptz;
timezone
-------------------------------
2004-11-01 13:10:01.136295+01
(1 row)

Hope this clarifies it for you...

On Mon, Nov 01, 2004 at 08:02:33PM +0700, David Garamond wrote:
--
Martijn van Oosterhout <kleptog (AT) svana (DOT) org> http://svana.org/kleptog/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBhjW9Y5Twig3Ge+YRApSqAKC6A17uNr/dy4QAOtgp6wPJwDiwTQCg2v84
fZUBd2lCMUJmo4km4XGeL6k=
=Xp4H
-----END PGP SIGNATURE-----

Martijn van Oosterhout wrote:
You're right, I forgot that AT TIME ZONE on timestamptz value currently
returns a timestamp, not timestamptz.

I do realize that currently timestamptz doesn't store the timezone
offset/timezone name (thus timestamp and timestamptz both require the
same amount of storage, 8 bytes). But I believe this probably won't be
so in the future. So the question remains, does AT TIME ZONE already do
what it's supposed to do (according to SQL standard, that is) or will
the behaviour be changed in the future? Will AT TIME ZONE returns
timestamptz in the future instead of timestamp, and will the "converted"
timestamp value be the same if compared with '=' operator?

--
dave


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

David Garamond <lists (AT) zara (DOT) 6.isreserved.com> writes:
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).

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Mon, Nov 01, 2004 at 11:00:10AM -0500, Tom Lane wrote:
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.

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/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBhmFmY5Twig3Ge+YRAoMIAJ9GmPDiNLlX8h2+n/By7IZAH9Oc5QCeILeT
RnV6U+luGY/oPqV8ysqeysk=
=JkfE
-----END PGP SIGNATURE-----

Martijn van Oosterhout <kleptog (AT) svana (DOT) org> writes:
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.

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

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