AT TIME ZONE: "convert"?

[David Garamond]

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

[Martijn van Oosterhout]

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:

Sorry, hit Sent too early...

David Garamond wrote:

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)
Compare with:

# select timestamptz '2004-11-01 12:00:00-05' =
timestamptz '2004-11-01 17:00:00-00';
?column?
----------
t
(1 row)

The question is: does AT TIME TIME ZONE already do what it's supposed to
do currently?

--
dave

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

--
Martijn van Oosterhout <kl*****@svana.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.

-----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]

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:

Sorry, hit Sent too early...

David Garamond wrote:

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)
Compare with:

# select timestamptz '2004-11-01 12:00:00-05' =
timestamptz '2004-11-01 17:00:00-00';
?column?
----------
t
(1 row)

The question is: does AT TIME TIME ZONE already do what it's supposed to
do currently?

--
dave

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

--
Martijn van Oosterhout <kl*****@svana.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.

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

[David Garamond]

Martijn van Oosterhout wrote:

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:

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]

Martijn van Oosterhout wrote:

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:

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

[Tom Lane]

David Garamond <li***@zara.6.isreserved.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

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

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

[Tom Lane]

David Garamond <li***@zara.6.isreserved.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

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

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

[Martijn van Oosterhout]

On Mon, Nov 01, 2004 at 11:00:10AM -0500, Tom Lane wrote:

David Garamond <li***@zara.6.isreserved.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 <kl*****@svana.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.

-----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]

On Mon, Nov 01, 2004 at 11:00:10AM -0500, Tom Lane wrote:

David Garamond <li***@zara.6.isreserved.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 <kl*****@svana.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.

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

[Tom Lane]

Martijn van Oosterhout <kl*****@svana.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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Martijn van Oosterhout <kl*****@svana.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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly