Обсуждение: AT TIME ZONE: "convert"?
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
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
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 majordomo@postgresql.org
--
Martijn van Oosterhout <kleptog@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.
Вложения
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
David Garamond <lists@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
On Mon, Nov 01, 2004 at 11:00:10AM -0500, Tom Lane wrote: > David Garamond <lists@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 <kleptog@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.
Вложения
Martijn van Oosterhout <kleptog@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
Added TODO description: * Allow TIMESTAMP WITH TIME ZONE to store the original timezone information, either zone name or offset from UTC If the TIMESTAMP value is stored with a time zone name, interval computations should adjust based on the time zone rules, e.g. adding 24 hours to a timestamp would yield a different result from adding one day. --------------------------------------------------------------------------- Tom Lane wrote: > Martijn van Oosterhout <kleptog@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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073