Re: AT TIME ZONE correction
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 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 +
Re: AT TIME ZONE correction
fOn Sun, Sep 2, 2018 at 02:21:58PM -0400, Tom Lane wrote:
> Bruce Momjian writes:
> > ! The AT TIME ZONE construct allows the addition,
> > ! conversion, and removal of time zones for time stamp values. > 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 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 +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index bb794e0..5e3d54f
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*** SELECT date_trunc('year', TIMESTAMP '200
*** 8082,8091
! The AT TIME ZONE construct allows conversions
! of time stamps to different time zones. shows its
! variants.
--- 8082,8092
! The AT TIME ZONE converts time
! stamp without time zone to/from
! time stamp with time zone, and
! time values to different time zones. shows its variants.
*** SELECT TIMESTAMP '2001-02-16 20:38:40' A
*** 8145,8153
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40
! The first example takes a time stamp without time zone and interprets it as MST time
! (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes
! a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).
--- 8146,8158
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40
! The first example adds a time zone to a value that lacks it.
! The second example shifts the time stamp with time zone value
! to the specified time zone, and returns the value without a
! time zone. This allows storage and display of values different
! from the current TimeZone value. Converting
! time values to other time zones uses the currently
! active time zone rules since no date is present.
Re: AT TIME ZONE correction
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 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 +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index bb794e0..53aa515
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*** SELECT date_trunc('year', TIMESTAMP '200
*** 8082,8089
! The AT TIME ZONE construct allows conversions
! of time stamps to different time zones. shows its
variants.
--- 8082,8089
! The AT TIME ZONE construct allows the addition,
! conversion, and removal of time zones for time stamp values. shows its
variants.
*** SELECT TIMESTAMP '2001-02-16 20:38:40' A
*** 8145,8153
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40
! The first example takes a time stamp without time zone and interprets it as MST time
! (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes
! a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).
--- 8145,8159
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40
! 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.)
Re: AT TIME ZONE correction
On Sat, Sep 1, 2018 at 07:30:43PM -0400, Tom Lane wrote: > Bruce Momjian 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 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 +
Re: AT TIME ZONE correction
Bruce Momjian 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
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 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 + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml new file mode 100644 index bb794e0..ba22b7d *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *** SELECT TIMESTAMP '2001-02-16 20:38:40' A *** 8145,8153 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; Result: 2001-02-16 18:38:40 ! The first example takes a time stamp without time zone and interprets it as MST time ! (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes ! a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7). --- 8145,8154 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; Result: 2001-02-16 18:38:40 ! The first example takes a time stamp without time zone, converts it ! to local time (PST, UTC-8), then converts it to time in MST (UTC-7) ! for display. The second example takes a time stamp specified in EST ! (UTC-5) and converts it to time in MST (UTC-7) for display.
