On Mon, Jan 27, 2025 at 9:36 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> Laurenz Albe <laurenz.a...@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 <r...@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)
