public inbox for [email protected]help / color / mirror / Atom feed
AT TIME ZONE correction 8+ messages / 2 participants [nested] [flat]
* AT TIME ZONE correction @ 2018-09-01 22:55 Bruce Momjian <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Bruce Momjian @ 2018-09-01 22:55 UTC (permalink / raw) To: pgsql-docs 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 <[email protected]> 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 + Attachments: [text/x-diff] time_zone.diff (1.2K, 2-time_zone.diff) download | inline diff: 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'; <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput> </screen> ! 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). </para> <para> --- 8145,8154 ---- SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput> </screen> ! 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. </para> <para> ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AT TIME ZONE correction @ 2018-09-01 23:30 Tom Lane <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Tom Lane @ 2018-09-01 23:30 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs Bruce Momjian <[email protected]> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AT TIME ZONE correction @ 2018-09-01 23:37 Bruce Momjian <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Bruce Momjian @ 2018-09-01 23:37 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-docs On Sat, Sep 1, 2018 at 07:30:43PM -0400, Tom Lane wrote: > Bruce Momjian <[email protected]> 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 <[email protected]> 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 + ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AT TIME ZONE correction @ 2018-09-02 03:28 Bruce Momjian <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Bruce Momjian @ 2018-09-02 03:28 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-docs 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 <[email protected]> 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 + Attachments: [text/x-diff] time_zone.diff (2.2K, 2-time_zone.diff) download | inline diff: 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 **** </indexterm> <para> ! The <literal>AT TIME ZONE</literal> construct allows conversions ! of time stamps to different time zones. <xref linkend="functions-datetime-zoneconvert-table"/> shows its variants. </para> --- 8082,8089 ---- </indexterm> <para> ! 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. </para> *************** 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'; <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput> </screen> ! 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). </para> <para> --- 8145,8159 ---- SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput> </screen> ! 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.) </para> <para> ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AT TIME ZONE correction @ 2018-09-02 18:21 Tom Lane <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Tom Lane @ 2018-09-02 18:21 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs Bruce Momjian <[email protected]> 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 ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AT TIME ZONE correction @ 2018-09-03 02:11 Bruce Momjian <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Bruce Momjian @ 2018-09-03 02:11 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-docs fOn Sun, Sep 2, 2018 at 02:21:58PM -0400, Tom Lane wrote: > Bruce Momjian <[email protected]> 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 <[email protected]> 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 + Attachments: [text/x-diff] time_zone.diff (2.2K, 2-time_zone.diff) download | inline diff: 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 **** </indexterm> <para> ! The <literal>AT TIME ZONE</literal> construct allows conversions ! of time stamps to different time zones. <xref ! linkend="functions-datetime-zoneconvert-table"/> shows its ! variants. </para> <table id="functions-datetime-zoneconvert-table"> --- 8082,8092 ---- </indexterm> <para> ! The <literal>AT TIME ZONE</literal> converts time ! stamp <emphasis>without time zone</emphasis> to/from ! time stamp <emphasis>with time zone</emphasis>, and ! <emphasis>time</emphasis> values to different time zones. <xref ! linkend="functions-datetime-zoneconvert-table"/> shows its variants. </para> <table id="functions-datetime-zoneconvert-table"> *************** 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'; <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput> </screen> ! 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). </para> <para> --- 8146,8158 ---- SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput> </screen> ! 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 <varname>TimeZone</varname> value. Converting ! <emphasis>time</emphasis> values to other time zones uses the currently ! active time zone rules since no date is present. </para> <para> ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AT TIME ZONE correction @ 2018-09-03 13:20 Bruce Momjian <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Bruce Momjian @ 2018-09-03 13:20 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-docs 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 <[email protected]> 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 + Attachments: [text/x-diff] time_zone.diff (2.9K, 2-time_zone.diff) download | inline diff: diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index bb794e0..2135799 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -8082,10 +8082,11 @@ SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); </indexterm> <para> - The <literal>AT TIME ZONE</literal> construct allows conversions - of time stamps to different time zones. <xref - linkend="functions-datetime-zoneconvert-table"/> shows its - variants. + The <literal>AT TIME ZONE</literal> converts time + stamp <emphasis>without time zone</emphasis> to/from + time stamp <emphasis>with time zone</emphasis>, and + <emphasis>time</emphasis> values to different time zones. <xref + linkend="functions-datetime-zoneconvert-table"/> shows its variants. </para> <table id="functions-datetime-zoneconvert-table"> @@ -8130,24 +8131,29 @@ SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); <para> In these expressions, the desired time zone <replaceable>zone</replaceable> can be - specified either as a text string (e.g., <literal>'PST'</literal>) + specified either as a text string (e.g., <literal>'America/Los_Angeles'</literal>) or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>). In the text case, a time zone name can be specified in any of the ways described in <xref linkend="datatype-timezones"/>. </para> <para> - Examples (assuming the local time zone is <literal>PST8PDT</literal>): + Examples (assuming the local time zone is <literal>America/Los_Angeles</literal>): <screen> -SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST'; +SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver'; <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput> -SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; +SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver'; <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput> </screen> - 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). + The first example adds a time zone to a value that lacks it, and + displays the value using the current <varname>TimeZone</varname> + setting. 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 <varname>TimeZone</varname> setting. Converting + <emphasis>time</emphasis> values to other time zones uses the currently + active time zone rules since no date is supplied. </para> <para> ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AT TIME ZONE correction @ 2018-09-05 02:35 Bruce Momjian <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Bruce Momjian @ 2018-09-05 02:35 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: pgsql-docs 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 <[email protected]> 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 + ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2018-09-05 02:35 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-09-01 22:55 AT TIME ZONE correction Bruce Momjian <[email protected]> 2018-09-01 23:30 ` Tom Lane <[email protected]> 2018-09-01 23:37 ` Bruce Momjian <[email protected]> 2018-09-02 03:28 ` Bruce Momjian <[email protected]> 2018-09-02 18:21 ` Tom Lane <[email protected]> 2018-09-03 02:11 ` Bruce Momjian <[email protected]> 2018-09-03 13:20 ` Bruce Momjian <[email protected]> 2018-09-05 02:35 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox