Thread: AT TIME ZONE correction
Looking over the AT TIME ZONE docs, I think they are subtly confusing. The order of conversion specific in the first example should _start_ with the assumption of local time zone for the time stamp, not something that happens after AT TIME ZONE is applied. The ordering in current docs makes the second example confusing too. The attached patch fixes this. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Attachment
Bruce Momjian <bruce@momjian.us> writes:
> Looking over the AT TIME ZONE docs, I think they are subtly confusing.
> The order of conversion specific in the first example should _start_
> with the assumption of local time zone for the time stamp, not something
> that happens after AT TIME ZONE is applied. The ordering in current
> docs makes the second example confusing too.
> The attached patch fixes this.
I think it's you that are confused. The text as written is correct,
or at least arguably so; your revision is definitely incorrect.
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
The way I'd describe this is that we start with a timestamp,
which has no particular timezone. The AT TIME ZONE says "Assume that
this zoneless timestamp is in MST, and convert it to timestamp with
time zone (which will be in UTC, internally)". Then after that, the
UTC timestamptz value is converted to PST8PDT for display purposes,
but that's done by timestamptz_out not AT TIME ZONE.
If we were going from PST to MST as your patch describes it, the
output would be one hour later not one hour earlier than the input.
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Here we've got a time value that was initially given in EST (-05),
but was converted to UTC by timestampz_in. Then the AT TIME ZONE
says "Please convert this UTC value to MST, and emit it as a zoneless
timestamp" (which will not be subject to any further conversion when
it's displayed).
The existing text is indeed a bit deficient, because it fails to
draw a clear boundary between what the AT TIME ZONE operator is
doing and what is being done by the timestamp(tz) I/O functions.
But you're not making it better.
regards, tom lane
On Sat, Sep 1, 2018 at 07:30:43PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Looking over the AT TIME ZONE docs, I think they are subtly confusing. > > The order of conversion specific in the first example should _start_ > > with the assumption of local time zone for the time stamp, not something > > that happens after AT TIME ZONE is applied. The ordering in current > > docs makes the second example confusing too. > > > The attached patch fixes this. > > I think it's you that are confused. The text as written is correct, > or at least arguably so; your revision is definitely incorrect. > > SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; > > The way I'd describe this is that we start with a timestamp, > which has no particular timezone. The AT TIME ZONE says "Assume that > this zoneless timestamp is in MST, and convert it to timestamp with > time zone (which will be in UTC, internally)". Then after that, the > UTC timestamptz value is converted to PST8PDT for display purposes, > but that's done by timestamptz_out not AT TIME ZONE. > > If we were going from PST to MST as your patch describes it, the > output would be one hour later not one hour earlier than the input. > > SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; > > Here we've got a time value that was initially given in EST (-05), > but was converted to UTC by timestampz_in. Then the AT TIME ZONE > says "Please convert this UTC value to MST, and emit it as a zoneless > timestamp" (which will not be subject to any further conversion when > it's displayed). > > The existing text is indeed a bit deficient, because it fails to > draw a clear boundary between what the AT TIME ZONE operator is > doing and what is being done by the timestamp(tz) I/O functions. > But you're not making it better. Yes, I am still researching and realize my diff is wrong. Let me keep working and I will repost. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On Sat, Sep 1, 2018 at 07:37:36PM -0400, Bruce Momjian wrote: > > Here we've got a time value that was initially given in EST (-05), > > but was converted to UTC by timestampz_in. Then the AT TIME ZONE > > says "Please convert this UTC value to MST, and emit it as a zoneless > > timestamp" (which will not be subject to any further conversion when > > it's displayed). > > > > The existing text is indeed a bit deficient, because it fails to > > draw a clear boundary between what the AT TIME ZONE operator is > > doing and what is being done by the timestamp(tz) I/O functions. > > But you're not making it better. > > Yes, I am still researching and realize my diff is wrong. Let me keep > working and I will repost. I have developed the attached patch, which I think is an improvement. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Attachment
Bruce Momjian <bruce@momjian.us> writes:
> ! The <literal>AT TIME ZONE</literal> construct allows the addition,
> ! conversion, and removal of time zones for time stamp values. <xref
> linkend="functions-datetime-zoneconvert-table"/> shows its
> variants.
Maybe it'd be more to the point to say that it allows conversion between
"timestamp with time zone" and "timestamp without time zone".
> ! The first example takes a time stamp without time zone and interprets
> ! it in the MST time zone (UTC-7), returning a time stamp with time
> ! zone value which is displayed in local time (PST, UTC-8). The second
> ! example takes a time stamp with time zone value (EST, UTC-5) and
> ! converts it to the date and time in MST (UTC-7) without time zone.
> ! Basically, the first example takes the date and time and puts it in
> ! the specified time zone. The second example takes a time stamp with
> ! time zone and shifts it to the specified time zone. (No time zone
> ! designation is returned.)
I still find this to be more confusing than helpful. In particular,
I do not think that it's possible to explain this behavior clearly
without mentioning that timestamp with time zone values are always
stored in UTC and what AT TIME ZONE really does is convert between UTC
and the specified zone (in a direction dependent on which type is
supplied as argument).
regards, tom lane
fOn Sun, Sep 2, 2018 at 02:21:58PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > ! The <literal>AT TIME ZONE</literal> construct allows the addition,
> > ! conversion, and removal of time zones for time stamp values. <xref
> > linkend="functions-datetime-zoneconvert-table"/> shows its
> > variants.
>
> Maybe it'd be more to the point to say that it allows conversion between
> "timestamp with time zone" and "timestamp without time zone".
>
> > ! The first example takes a time stamp without time zone and interprets
> > ! it in the MST time zone (UTC-7), returning a time stamp with time
> > ! zone value which is displayed in local time (PST, UTC-8). The second
> > ! example takes a time stamp with time zone value (EST, UTC-5) and
> > ! converts it to the date and time in MST (UTC-7) without time zone.
> > ! Basically, the first example takes the date and time and puts it in
> > ! the specified time zone. The second example takes a time stamp with
> > ! time zone and shifts it to the specified time zone. (No time zone
> > ! designation is returned.)
>
> I still find this to be more confusing than helpful. In particular,
> I do not think that it's possible to explain this behavior clearly
> without mentioning that timestamp with time zone values are always
> stored in UTC and what AT TIME ZONE really does is convert between UTC
> and the specified zone (in a direction dependent on which type is
> supplied as argument).
Agreed. The more I dig into this the more I learn. I have developed
the attached patch which I hope this time is an improvement.
Is there any value to showing these two queries which show how calling
AT TIME ZONE twice cancels itself out:
SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'America/Chicago';
timezone
---------------------
2018-09-02 07:09:19
SELECT '2018-09-02 07:09:19-04'::timestamptz AT TIME ZONE 'America/Chicago' AT TIME ZONE 'America/Chicago';
timezone
------------------------
2018-09-02 07:09:19-04
or this one which shows how to convert a date/time from one time zone to
another:
SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'Asia/Tokyo' AT
TIME ZONE 'America/Chicago';
timezone
---------------------
2018-09-01 17:09:19
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Attachment
On Sun, Sep 2, 2018 at 10:11:59PM -0400, Bruce Momjian wrote: > On Sun, Sep 2, 2018 at 02:21:58PM -0400, Tom Lane wrote: > > I still find this to be more confusing than helpful. In particular, > > I do not think that it's possible to explain this behavior clearly > > without mentioning that timestamp with time zone values are always > > stored in UTC and what AT TIME ZONE really does is convert between UTC > > and the specified zone (in a direction dependent on which type is > > supplied as argument). > > Agreed. The more I dig into this the more I learn. I have developed > the attached patch which I hope this time is an improvement. I polished the text some more and changed the three-letter time zone abbreviation (e.g., MST) to use the more general text, e.g. "America/Denver". We should not be encouraging people to specify the daylight savings time status based on the date in the date/time string. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Attachment
On Mon, Sep 3, 2018 at 09:20:34AM -0400, Bruce Momjian wrote:
> On Sun, Sep 2, 2018 at 10:11:59PM -0400, Bruce Momjian wrote:
> > On Sun, Sep 2, 2018 at 02:21:58PM -0400, Tom Lane wrote:
> > > I still find this to be more confusing than helpful. In particular,
> > > I do not think that it's possible to explain this behavior clearly
> > > without mentioning that timestamp with time zone values are always
> > > stored in UTC and what AT TIME ZONE really does is convert between UTC
> > > and the specified zone (in a direction dependent on which type is
> > > supplied as argument).
> >
> > Agreed. The more I dig into this the more I learn. I have developed
> > the attached patch which I hope this time is an improvement.
>
> I polished the text some more and changed the three-letter time zone
> abbreviation (e.g., MST) to use the more general text, e.g.
> "America/Denver". We should not be encouraging people to specify the
> daylight savings time status based on the date in the date/time string.
Patch applied through 9.3. I ended up adding a third example:
https://git.postgresql.org/pg/commitdiff/dd6073f22a6b5dd6181d8324465dd3c0bf1851e8
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +