agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedtimestamp with time zone ~> GMT
12+ messages / 7 participants
[nested] [flat]
* timestamp with time zone ~> GMT
@ 2025-01-27 07:51 PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 12+ messages in thread
From: PG Doc comments form @ 2025-01-27 07:51 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: bristleconeweb@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/17/datatype-datetime.html
Description:
Thank you for postgres. I wanted to offer clarification would may help
others in the docs on time stamps (after discovering subtle issues have
significant impact for me)
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIME-STAMPS
"An input value that has an explicit time zone specified is converted to
UTC"
"When a timestamp with time zone value is output, it is always converted
from UTC to the current timezone zone"
After re-testing behavior, it appears this means:
1. input DROPS the offset after conversion to UTC
2. output is system time or according to settings (DROPS utc and original
time zone)
To help illustrate the dilemma: consider an example use case where an
airline is emailing flight departure and arrival times. Passengers typically
need to know the times relative to the departure and destination time zones.
Passengers would be confused to see all times according to their current
time zone (which may be entirely different from the time zones of the
flight). Additionally, iCal must know both time zones to determine the true
flight time and render an accurate calendar.
Suggestion: Assuming my understanding is accurate - clarify for the reader
that time zone offset is lost (after conversion to UTC). At risk of stating
the obvious: "timestamp with time zone" is a rather misleading name.
"timestamp coerced to UTC" or something would be more accurate.
Since timestamp with time zone doesn't record the input time zone, there is
an associated issue: how to record the input time zone. I'm unable to locate
a recommendation through postgres docs. Certainly text or similar would
"work" for IANA time zones... however it would be helpful to have a little
more guidance, such as validation to the enum
https://www.postgresql.org/docs/17/view-pg-timezone-names.html I considered
using "time with time zone" but I see this is also coerced to UTC.
Hopefully these suggestions are helpful. Thanks again!
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
@ 2025-01-27 08:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Laurenz Albe @ 2025-01-27 08:51 UTC (permalink / raw)
To: bristleconeweb@gmail.com; pgsql-docs@lists.postgresql.org
On Mon, 2025-01-27 at 07:51 +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/17/datatype-datetime.html
> Description:
>
> Thank you for postgres. I wanted to offer clarification would may help
> others in the docs on time stamps (after discovering subtle issues have
> significant impact for me)
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIME-STAMPS
>
> "An input value that has an explicit time zone specified is converted to
> UTC"
> "When a timestamp with time zone value is output, it is always converted
> from UTC to the current timezone zone"
>
> After re-testing behavior, it appears this means:
> 1. input DROPS the offset after conversion to UTC
> 2. output is system time or according to settings (DROPS utc and original
> time zone)
>
> To help illustrate the dilemma: consider an example use case where an
> airline is emailing flight departure and arrival times. Passengers typically
> need to know the times relative to the departure and destination time zones.
> Passengers would be confused to see all times according to their current
> time zone (which may be entirely different from the time zones of the
> flight). Additionally, iCal must know both time zones to determine the true
> flight time and render an accurate calendar.
>
> Suggestion: Assuming my understanding is accurate - clarify for the reader
> that time zone offset is lost (after conversion to UTC). At risk of stating
> the obvious: "timestamp with time zone" is a rather misleading name.
> "timestamp coerced to UTC" or something would be more accurate.
>
> Since timestamp with time zone doesn't record the input time zone, there is
> an associated issue: how to record the input time zone. I'm unable to locate
> a recommendation through postgres docs. Certainly text or similar would
> "work" for IANA time zones... however it would be helpful to have a little
> more guidance, such as validation to the enum
> https://www.postgresql.org/docs/17/view-pg-timezone-names.html I considered
> using "time with time zone" but I see this is also coerced to UTC.
>
> Hopefully these suggestions are helpful. Thanks again!
Your understanding is correct.
I personally think of "timestamp with time zone" as an "absolute timestamp".
To preserve the original time zone that was entered, you'd have to store it
in a separate database column.
We welcome a documentation patch!
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-01-27 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 15:59 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2025-01-27 14:36 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: bristleconeweb@gmail.com; pgsql-docs@lists.postgresql.org
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Mon, 2025-01-27 at 07:51 +0000, PG Doc comments form wrote:
>> Suggestion: Assuming my understanding is accurate - clarify for the reader
>> that time zone offset is lost (after conversion to UTC). At risk of stating
>> the obvious: "timestamp with time zone" is a rather misleading name.
>> "timestamp coerced to UTC" or something would be more accurate.
> Your understanding is correct.
> I personally think of "timestamp with time zone" as an "absolute timestamp".
Yes. The datatype's behavior is not what you would expect from the
SQL standard, which makes our choice of the standard-derived name
rather unfortunate. That choice is well over 25 years old though,
so there's not much chance of changing it now.
> To preserve the original time zone that was entered, you'd have to store it
> in a separate database column.
The other problem is: what are you gonna store exactly? A numeric
offset from UTC is unambiguous but doesn't bring much to the table
compared to what we do now. A time zone name is a possibility,
but (a) that's bulky and (b) the politicians keep changing the
DST laws, so the meaning could change. In certain cases like
appointment calendars, tracking local law is just what you want
... but in cases like flight schedules, probably not.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-02-03 15:59 ` Robert Treat <rob@xzilla.net>
2025-02-03 16:21 ` Re: timestamp with time zone ~> GMT Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-02-03 16:50 ` Re: timestamp with time zone ~> GMT Jonathan Stanley <bristleconeweb@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Robert Treat @ 2025-02-03 15:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; bristleconeweb@gmail.com; pgsql-docs@lists.postgresql.org
On Mon, Jan 27, 2025 at 9:36 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Mon, 2025-01-27 at 07:51 +0000, PG Doc comments form wrote:
> >> Suggestion: Assuming my understanding is accurate - clarify for the reader
> >> that time zone offset is lost (after conversion to UTC). At risk of stating
> >> the obvious: "timestamp with time zone" is a rather misleading name.
> >> "timestamp coerced to UTC" or something would be more accurate.
>
> > Your understanding is correct.
> > I personally think of "timestamp with time zone" as an "absolute timestamp".
>
> Yes. The datatype's behavior is not what you would expect from the
> SQL standard, which makes our choice of the standard-derived name
> rather unfortunate. That choice is well over 25 years old though,
> so there's not much chance of changing it now.
>
This does seem to come up often enough that it probably is worth being
a bit more explicit about how this works; attached patch attempts
that.
Note, I dropped the bit about GMT; that change was made ~40 years ago,
and I suspect it is close to noise for many people these days, though
it could be added back if folks feel strongly about it.
> > To preserve the original time zone that was entered, you'd have to store it
> > in a separate database column.
>
> The other problem is: what are you gonna store exactly? A numeric
> offset from UTC is unambiguous but doesn't bring much to the table
> compared to what we do now. A time zone name is a possibility,
> but (a) that's bulky and (b) the politicians keep changing the
> DST laws, so the meaning could change. In certain cases like
> appointment calendars, tracking local law is just what you want
> ... but in cases like flight schedules, probably not.
>
As Tom notes above, what to store is debatable, and generally seems to
conflate storage and display desires together, which makes it hard to
imagine a generic enough implementation to put into core, but there
are some attempts to solve this problem floating around in extension
land. See https://github.com/mweber26/timestampandtz/blob/master/README.md
as one such attempt.
Robert Treat
https://xzilla.net
From 359a2df63065e3327ca3ab7cb0cfb73da6934a31 Mon Sep 17 00:00:00 2001
From: Robert Treat <rob@xzilla.net>
Date: Mon, 3 Feb 2025 10:42:39 -0500
Subject: [PATCH v1] Clarify timestamptz input time zone behavior
Content-Type: text/plain; charset="utf-8"
---
doc/src/sgml/datatype.sgml | 12 ++++++------
1 file changed, 6 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 1d9127e94e..ef5c90775a 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2254,12 +2254,12 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
</para>
<para>
- For <type>timestamp with time zone</type>, the internally stored
- value is always in UTC (Universal
- Coordinated Time, traditionally known as Greenwich Mean Time,
- <acronym>GMT</acronym>). An input value that has an explicit
- time zone specified is converted to UTC using the appropriate offset
- for that time zone. If no time zone is stated in the input string,
+ For <type>timestamp with time zone</type>, an input value that has an
+ explicit time zone specified will be converted to
+ UTC (Universal Coordinated Time) using the appropriate offset for that
+ time zone. This value will be stored internally as UTC and the original
+ specified time zone is not retained.
+ If no time zone is stated in the input string,
then it is assumed to be in the time zone indicated by the system's
<xref linkend="guc-timezone"/> parameter, and is converted to UTC using the
offset for the <varname>timezone</varname> zone.
--
2.24.3 (Apple Git-128)
Attachments:
[text/plain] v1-0001-Clarify-timestamptz-input-time-zone-behavior.patch.txt (1.6K, ../../CABV9wwOfPK_NtzfPf4Kh4SvkxUHZpP6YprquQwPTndEnB7JSwA@mail.gmail.com/2-v1-0001-Clarify-timestamptz-input-time-zone-behavior.patch.txt)
download | inline diff:
From 359a2df63065e3327ca3ab7cb0cfb73da6934a31 Mon Sep 17 00:00:00 2001
From: Robert Treat <rob@xzilla.net>
Date: Mon, 3 Feb 2025 10:42:39 -0500
Subject: [PATCH v1] Clarify timestamptz input time zone behavior
Content-Type: text/plain; charset="utf-8"
---
doc/src/sgml/datatype.sgml | 12 ++++++------
1 file changed, 6 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 1d9127e94e..ef5c90775a 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2254,12 +2254,12 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
</para>
<para>
- For <type>timestamp with time zone</type>, the internally stored
- value is always in UTC (Universal
- Coordinated Time, traditionally known as Greenwich Mean Time,
- <acronym>GMT</acronym>). An input value that has an explicit
- time zone specified is converted to UTC using the appropriate offset
- for that time zone. If no time zone is stated in the input string,
+ For <type>timestamp with time zone</type>, an input value that has an
+ explicit time zone specified will be converted to
+ UTC (Universal Coordinated Time) using the appropriate offset for that
+ time zone. This value will be stored internally as UTC and the original
+ specified time zone is not retained.
+ If no time zone is stated in the input string,
then it is assumed to be in the time zone indicated by the system's
<xref linkend="guc-timezone"/> parameter, and is converted to UTC using the
offset for the <varname>timezone</varname> zone.
--
2.24.3 (Apple Git-128)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 15:59 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
@ 2025-02-03 16:21 ` Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-02-03 17:23 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 17:50 ` Re: timestamp with time zone ~> GMT Dave Cramer <davecramer@postgres.rocks>
1 sibling, 2 replies; 12+ messages in thread
From: Álvaro Herrera @ 2025-02-03 16:21 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; bristleconeweb@gmail.com; pgsql-docs@lists.postgresql.org
On 2025-Feb-03, Robert Treat wrote:
> This does seem to come up often enough that it probably is worth being
> a bit more explicit about how this works; attached patch attempts
> that.
LGTM.
> Note, I dropped the bit about GMT; that change was made ~40 years ago,
> and I suspect it is close to noise for many people these days, though
> it could be added back if folks feel strongly about it.
I don't feel strongly about it, but another option might be to add a
<footnote> so that it is still there but less intrusive. Grepping for
GMT in the postgres repo there are still over 1400 matches of all kinds.
> As Tom notes above, what to store is debatable, and generally seems to
> conflate storage and display desires together, which makes it hard to
> imagine a generic enough implementation to put into core, but there
> are some attempts to solve this problem floating around in extension
> land. See https://github.com/mweber26/timestampandtz/blob/master/README.md
> as one such attempt.
Another simple option is to store a timezone name in a separate column,
which you can then use in the AT TIME ZONE clause. This is of course
more wasteful than storing a 2-byte zone identifier as the
timestampandtz extension does.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos (bis) / con todos los humanos acabaré ¡acabaré! (Bender)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 15:59 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
2025-02-03 16:21 ` Re: timestamp with time zone ~> GMT Álvaro Herrera <alvherre@alvh.no-ip.org>
@ 2025-02-03 17:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 21:43 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2025-02-03 17:23 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Robert Treat <rob@xzilla.net>; Laurenz Albe <laurenz.albe@cybertec.at>; bristleconeweb@gmail.com; pgsql-docs@lists.postgresql.org
=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org> writes:
> On 2025-Feb-03, Robert Treat wrote:
>> This does seem to come up often enough that it probably is worth being
>> a bit more explicit about how this works; attached patch attempts
>> that.
> LGTM.
Hmm, I kind of like the up-front statement that timestamptz stores
UTC. How about this simpler change?
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 1d9127e94e..269809dc81 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2263,6 +2263,8 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
then it is assumed to be in the time zone indicated by the system's
<xref linkend="guc-timezone"/> parameter, and is converted to UTC using the
offset for the <varname>timezone</varname> zone.
+ In either case, the originally stated or assumed time zone is not
+ retained.
</para>
<para>
>> Note, I dropped the bit about GMT; that change was made ~40 years ago,
>> and I suspect it is close to noise for many people these days, though
>> it could be added back if folks feel strongly about it.
> I don't feel strongly about it, but another option might be to add a
> <footnote> so that it is still there but less intrusive. Grepping for
> GMT in the postgres repo there are still over 1400 matches of all kinds.
I think we'd better not remove the gloss for GMT just yet. It's still
the magic boot value for the timezone GUC for example (cf pgtz.c),
and it's still embedded in the IANA timezone database:
$ ls /usr/share/zoneinfo/Etc
GMT GMT+11 GMT+4 GMT+8 GMT-10 GMT-14 GMT-5 GMT-9 UTC
GMT+0 GMT+12 GMT+5 GMT+9 GMT-11 GMT-2 GMT-6 GMT0 Universal
GMT+1 GMT+2 GMT+6 GMT-0 GMT-12 GMT-3 GMT-7 Greenwich Zulu
GMT+10 GMT+3 GMT+7 GMT-1 GMT-13 GMT-4 GMT-8 UCT
Maybe we could move the info to the Glossary, but that seems like
a separate matter from what's under discussion here.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 15:59 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
2025-02-03 16:21 ` Re: timestamp with time zone ~> GMT Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-02-03 17:23 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-02-03 21:43 ` Robert Treat <rob@xzilla.net>
2025-02-06 22:33 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Robert Treat @ 2025-02-03 21:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>; Laurenz Albe <laurenz.albe@cybertec.at>; bristleconeweb@gmail.com; pgsql-docs@lists.postgresql.org
On Mon, Feb 3, 2025 at 12:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org> writes:
> > On 2025-Feb-03, Robert Treat wrote:
> >> This does seem to come up often enough that it probably is worth being
> >> a bit more explicit about how this works; attached patch attempts
> >> that.
>
> > LGTM.
>
> Hmm, I kind of like the up-front statement that timestamptz stores
> UTC. How about this simpler change?
>
> diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
> index 1d9127e94e..269809dc81 100644
> --- a/doc/src/sgml/datatype.sgml
> +++ b/doc/src/sgml/datatype.sgml
> @@ -2263,6 +2263,8 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
> then it is assumed to be in the time zone indicated by the system's
> <xref linkend="guc-timezone"/> parameter, and is converted to UTC using the
> offset for the <varname>timezone</varname> zone.
> + In either case, the originally stated or assumed time zone is not
> + retained.
> </para>
I thought the re-order made sense since the preceding paragraph talks
exclusively about behavior, so this paragraph first contrasts the
behavioral difference between the two, and then mentions the storage
aspects as part of that story.
I actually like the above as well, but if it were me I'd move all
mentions of storage (the existing + the above) to the end of the
paragraph after the behavior aspects.
> >> Note, I dropped the bit about GMT; that change was made ~40 years ago,
> >> and I suspect it is close to noise for many people these days, though
> >> it could be added back if folks feel strongly about it.
>
> > I don't feel strongly about it, but another option might be to add a
> > <footnote> so that it is still there but less intrusive. Grepping for
> > GMT in the postgres repo there are still over 1400 matches of all kinds.
>
> I think we'd better not remove the gloss for GMT just yet. It's still
> the magic boot value for the timezone GUC for example (cf pgtz.c),
> and it's still embedded in the IANA timezone database:
>
> $ ls /usr/share/zoneinfo/Etc
> GMT GMT+11 GMT+4 GMT+8 GMT-10 GMT-14 GMT-5 GMT-9 UTC
> GMT+0 GMT+12 GMT+5 GMT+9 GMT-11 GMT-2 GMT-6 GMT0 Universal
> GMT+1 GMT+2 GMT+6 GMT-0 GMT-12 GMT-3 GMT-7 Greenwich Zulu
> GMT+10 GMT+3 GMT+7 GMT-1 GMT-13 GMT-4 GMT-8 UCT
>
> Maybe we could move the info to the Glossary, but that seems like
> a separate matter from what's under discussion here.
>
Yeah, I think for end users this isn't compelling, but I figured there
might be some push back :-)
Robert Treat
https://xzilla.net
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 15:59 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
2025-02-03 16:21 ` Re: timestamp with time zone ~> GMT Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-02-03 17:23 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 21:43 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
@ 2025-02-06 22:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-07 15:50 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2025-02-06 22:33 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>; Laurenz Albe <laurenz.albe@cybertec.at>; bristleconeweb@gmail.com; pgsql-docs@lists.postgresql.org
Robert Treat <rob@xzilla.net> writes:
> On Mon, Feb 3, 2025 at 12:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmm, I kind of like the up-front statement that timestamptz stores
>> UTC. How about this simpler change?
> I thought the re-order made sense since the preceding paragraph talks
> exclusively about behavior, so this paragraph first contrasts the
> behavioral difference between the two, and then mentions the storage
> aspects as part of that story.
> I actually like the above as well, but if it were me I'd move all
> mentions of storage (the existing + the above) to the end of the
> paragraph after the behavior aspects.
OK, it makes more sense when considering the previous para as well.
Here's a combined proposal that also adds glossary entries.
regards, tom lane
Attachments:
[text/x-diff] v2-0001-Clarify-timestamptz-input-time-zone-behavior.patch (2.7K, ../../1209685.1738881227@sss.pgh.pa.us/2-v2-0001-Clarify-timestamptz-input-time-zone-behavior.patch)
download | inline diff:
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 1d9127e94e..b20241feb5 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2245,24 +2245,27 @@ TIMESTAMP '2004-10-19 10:23:54+02'
<programlisting>
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
</programlisting>
+ </para>
- In a literal that has been determined to be <type>timestamp without time
+ <para>
+ In a value that has been determined to be <type>timestamp without time
zone</type>, <productname>PostgreSQL</productname> will silently ignore
any time zone indication.
That is, the resulting value is derived from the date/time
- fields in the input value, and is not adjusted for time zone.
+ fields in the input string, and is not adjusted for time zone.
</para>
<para>
- For <type>timestamp with time zone</type>, the internally stored
- value is always in UTC (Universal
- Coordinated Time, traditionally known as Greenwich Mean Time,
- <acronym>GMT</acronym>). An input value that has an explicit
- time zone specified is converted to UTC using the appropriate offset
+ For <type>timestamp with time zone</type> values, an input string
+ that includes an explicit time zone will be converted to UTC
+ (<glossterm linkend="glossary-utc">Universal Coordinated
+ Time</glossterm>) using the appropriate offset
for that time zone. If no time zone is stated in the input string,
then it is assumed to be in the time zone indicated by the system's
<xref linkend="guc-timezone"/> parameter, and is converted to UTC using the
offset for the <varname>timezone</varname> zone.
+ In either case, the value is stored internally as UTC, and the
+ originally stated or assumed time zone is not retained.
</para>
<para>
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index f54f25c1c6..c0f812e3f5 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -851,6 +851,11 @@
</glossdef>
</glossentry>
+ <glossentry>
+ <glossterm>GMT</glossterm>
+ <glosssee otherterm="glossary-utc" />
+ </glossentry>
+
<glossentry id="glossary-grant">
<glossterm>Grant</glossterm>
<glossdef>
@@ -2047,6 +2052,17 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-utc">
+ <glossterm>UTC</glossterm>
+ <glossdef>
+ <para>
+ Universal Coordinated Time, the primary global time reference,
+ approximately the time prevailing at the zero meridian of longitude.
+ Often but inaccurately referred to as GMT (Greenwich Mean Time).
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-vacuum">
<glossterm>Vacuum</glossterm>
<glossdef>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 15:59 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
2025-02-03 16:21 ` Re: timestamp with time zone ~> GMT Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-02-03 17:23 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 21:43 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
2025-02-06 22:33 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-02-07 15:50 ` Robert Treat <rob@xzilla.net>
2025-02-07 17:41 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Robert Treat @ 2025-02-07 15:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>; Laurenz Albe <laurenz.albe@cybertec.at>; bristleconeweb@gmail.com; pgsql-docs@lists.postgresql.org
On Thu, Feb 6, 2025 at 5:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Treat <rob@xzilla.net> writes:
> > On Mon, Feb 3, 2025 at 12:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Hmm, I kind of like the up-front statement that timestamptz stores
> >> UTC. How about this simpler change?
>
> > I thought the re-order made sense since the preceding paragraph talks
> > exclusively about behavior, so this paragraph first contrasts the
> > behavioral difference between the two, and then mentions the storage
> > aspects as part of that story.
> > I actually like the above as well, but if it were me I'd move all
> > mentions of storage (the existing + the above) to the end of the
> > paragraph after the behavior aspects.
>
> OK, it makes more sense when considering the previous para as well.
> Here's a combined proposal that also adds glossary entries.
>
+1
Robert Treat
https://xzilla.net
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 15:59 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
2025-02-03 16:21 ` Re: timestamp with time zone ~> GMT Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-02-03 17:23 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 21:43 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
2025-02-06 22:33 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-07 15:50 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
@ 2025-02-07 17:41 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2025-02-07 17:41 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>; Laurenz Albe <laurenz.albe@cybertec.at>; bristleconeweb@gmail.com; pgsql-docs@lists.postgresql.org
Robert Treat <rob@xzilla.net> writes:
> On Thu, Feb 6, 2025 at 5:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Here's a combined proposal that also adds glossary entries.
> +1
OK, pushed.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 15:59 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
2025-02-03 16:21 ` Re: timestamp with time zone ~> GMT Álvaro Herrera <alvherre@alvh.no-ip.org>
@ 2025-02-03 17:50 ` Dave Cramer <davecramer@postgres.rocks>
1 sibling, 0 replies; 12+ messages in thread
From: Dave Cramer @ 2025-02-03 17:50 UTC (permalink / raw)
To: Álvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Robert Treat <rob@xzilla.net>; Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; bristleconeweb@gmail.com; pgsql-docs@lists.postgresql.org
On Mon, 3 Feb 2025 at 11:21, Álvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> On 2025-Feb-03, Robert Treat wrote:
>
> > This does seem to come up often enough that it probably is worth being
> > a bit more explicit about how this works; attached patch attempts
> > that.
>
> LGTM.
>
> > Note, I dropped the bit about GMT; that change was made ~40 years ago,
> > and I suspect it is close to noise for many people these days, though
> > it could be added back if folks feel strongly about it.
>
> I don't feel strongly about it, but another option might be to add a
> <footnote> so that it is still there but less intrusive. Grepping for
> GMT in the postgres repo there are still over 1400 matches of all kinds.
>
> > As Tom notes above, what to store is debatable, and generally seems to
> > conflate storage and display desires together, which makes it hard to
> > imagine a generic enough implementation to put into core, but there
> > are some attempts to solve this problem floating around in extension
> > land. See
> https://github.com/mweber26/timestampandtz/blob/master/README.md
> > as one such attempt.
>
> Another simple option is to store a timezone name in a separate column,
> which you can then use in the AT TIME ZONE clause. This is of course
> more wasteful than storing a 2-byte zone identifier as the
> timestampandtz extension does.
>
If anything I'd advocate creating a type which stored both. Storing the TZ
in another column would complicate things if the data were to be sent in
binary.
Is there a case where we would send two attributes for one in binary ?
Dave
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: timestamp with time zone ~> GMT
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Re: timestamp with time zone ~> GMT Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Re: timestamp with time zone ~> GMT Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 15:59 ` Re: timestamp with time zone ~> GMT Robert Treat <rob@xzilla.net>
@ 2025-02-03 16:50 ` Jonathan Stanley <bristleconeweb@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Jonathan Stanley @ 2025-02-03 16:50 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-docs@lists.postgresql.org
Thank you. I believe that explanation would have helped.
FWIW, I ultimately decided to simply duplicate the column:
1. keep the timestamp column for sorting/filters/etc.
2. add a full iso 8601 column as text for straightforward client handling
(avoids forcing the client to stitch the data back together; simplicity
preferred over small data cost IMO).
Perhaps this would be a preferred solution for many others' use cases?
Certainly up to you all on whether this additional tip is worth including.
I'll just say I'm a bit embarrassed to admit how long it took me to realize
this answer!
On Mon, Feb 3, 2025 at 10:59 AM Robert Treat <rob@xzilla.net> wrote:
> On Mon, Jan 27, 2025 at 9:36 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > > On Mon, 2025-01-27 at 07:51 +0000, PG Doc comments form wrote:
> > >> Suggestion: Assuming my understanding is accurate - clarify for the
> reader
> > >> that time zone offset is lost (after conversion to UTC). At risk of
> stating
> > >> the obvious: "timestamp with time zone" is a rather misleading name.
> > >> "timestamp coerced to UTC" or something would be more accurate.
> >
> > > Your understanding is correct.
> > > I personally think of "timestamp with time zone" as an "absolute
> timestamp".
> >
> > Yes. The datatype's behavior is not what you would expect from the
> > SQL standard, which makes our choice of the standard-derived name
> > rather unfortunate. That choice is well over 25 years old though,
> > so there's not much chance of changing it now.
> >
>
> This does seem to come up often enough that it probably is worth being
> a bit more explicit about how this works; attached patch attempts
> that.
>
> Note, I dropped the bit about GMT; that change was made ~40 years ago,
> and I suspect it is close to noise for many people these days, though
> it could be added back if folks feel strongly about it.
>
> > > To preserve the original time zone that was entered, you'd have to
> store it
> > > in a separate database column.
> >
> > The other problem is: what are you gonna store exactly? A numeric
> > offset from UTC is unambiguous but doesn't bring much to the table
> > compared to what we do now. A time zone name is a possibility,
> > but (a) that's bulky and (b) the politicians keep changing the
> > DST laws, so the meaning could change. In certain cases like
> > appointment calendars, tracking local law is just what you want
> > ... but in cases like flight schedules, probably not.
> >
>
> As Tom notes above, what to store is debatable, and generally seems to
> conflate storage and display desires together, which makes it hard to
> imagine a generic enough implementation to put into core, but there
> are some attempts to solve this problem floating around in extension
> land. See https://github.com/mweber26/timestampandtz/blob/master/README.md
> as one such attempt.
>
>
> Robert Treat
> https://xzilla.net
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2025-02-07 17:41 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-27 07:51 timestamp with time zone ~> GMT PG Doc comments form <noreply@postgresql.org>
2025-01-27 08:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-01-27 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 15:59 ` Robert Treat <rob@xzilla.net>
2025-02-03 16:21 ` Álvaro Herrera <alvherre@alvh.no-ip.org>
2025-02-03 17:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 21:43 ` Robert Treat <rob@xzilla.net>
2025-02-06 22:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-07 15:50 ` Robert Treat <rob@xzilla.net>
2025-02-07 17:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-03 17:50 ` Dave Cramer <davecramer@postgres.rocks>
2025-02-03 16:50 ` Jonathan Stanley <bristleconeweb@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox