On Wed, May 13, 2026 at 08:57:54AM -0700, Kirk Parker wrote:
> On Wed, May 13, 2026 at 6:13 AM Bruce Momjian <[email protected]> wrote:
>
> On Wed, May 13, 2026 at 03:21:11AM +0100, Richard Neill wrote:
> > Hi Bruce,
> >
> > Thanks for your reply. Yes I think it does need to be stated more boldly
> -
> > from a "Poka-Yoke" perspective (and despite using Postgresql for years),
> I
> > didn't properly understand it the first time.
> >
> > I think it's a gotcha, because, to me, the name suggests a different
> > behaviour.
> > I understood "timestamp with timezone" as "a data type which stores the
> > timestamp, and stores the timezone WITH (i.e. alongside) it".
>
> Yes, the name is confusing.
>
> If it could be done over, naming it "universal timestamp" or "utc timestamp"
> would be better, But of course there is no possibility of changing it at
> this late date.
Yes, the SQL standard requires the syntax we currently support.
> > While looking at the docs, I can also see 3 other things that would be
> > helpful to add:
> >
> > 1. How to actually store "timestamp_and_timezone" ? (i.e. I want to
> store
> > the UTC value, and I want to store and retrieve the original offset).
> > Presumably the answer is to store both timestamptz AND the integer
> tz_offset
> > (is there a "timezone" datatype, or should that just be a string?)
>
> Yes.
>
> Yes, not sure why this is a difficult concept. "A unique and universal
> representation of a specific moment of terrestrial timekeeping" is what the
> timestamptz stores. If you want to record the offset (or timezone region,
> which
> is not exactly the same thing) from the user's perspective, go ahead and
> create
> a column for that. FWIW I can't think of a single time in over 3 decades of
> building databases where I would have wanted that, but if your use case needs
> it the implementation is simple and obvious.
True, I have never seen this requested either.
> How could we usefully make a single recommendation? The only plausible
> recommenation is "use the datatype that best suits your purposes", and if the
> documentation doesn't do an adequate job of describing the differences and
> applicability, that's what should be addressed, rather than asking for a
> one-size-fits-all recommendation.
Agreed.
> And looking at the chapter again, I think table 8.9 does foster the confusion:
>
> Name Description
> timestamp [ without time zone ] == both date and time (no time zone)
> timestamp with time zone == both date and time, with time zone
>
> Here Description is presented generally, but in fact it only describes the i/o
> presentation of the two types. *Storage* for both types is identical (or at
> least appears that way to the user).
>
> What about something like: "Timestamp [Without Time Zone] takes the the date/
> time verbatim without any reference to time zones, whereas Timestamp With Time
> Zone converts from the specified offset (or session's time zone if no offset
> is
> specified) to UTC for storage, and on retrieval converts to the session's time
> zone". That's fairly wordy for a table entry, but it does do a better job of
> conveying what's actually happening and omits the implication that we store
> the
> session's time zone or offset along with the UTC timestamp.
I see your point. I went with adding the wording "no time zone
adjustment" and "with time zone adjustment" in the table. Patch
attached. You can see the output at:
https://momjian.us/tmp/pgsql/datatype-datetime.html
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index d8d91678e86..959b443a72e 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -252,25 +252,25 @@
<row>
<entry><type>time [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
<entry></entry>
- <entry>time of day (no time zone)</entry>
+ <entry>time of day (no time zone adjustment)</entry>
</row>
<row>
<entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
<entry><type>timetz</type></entry>
- <entry>time of day, including time zone</entry>
+ <entry>time of day, including time zone adjustment</entry>
</row>
<row>
<entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
<entry></entry>
- <entry>date and time (no time zone)</entry>
+ <entry>date and time (no time zone adjustment)</entry>
</row>
<row>
<entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
<entry><type>timestamptz</type></entry>
- <entry>date and time, including time zone</entry>
+ <entry>date and time, including time zone adjustment</entry>
</row>
<row>
@@ -1768,7 +1768,7 @@ SELECT 'abc \153\154\155 \052\251\124'::bytea;
<row>
<entry><type>timestamp [ (<replaceable>p</replaceable>) ] [ without time zone ]</type></entry>
<entry>8 bytes</entry>
- <entry>both date and time (no time zone)</entry>
+ <entry>both date and time (no time zone adjustment)</entry>
<entry>4713 BC</entry>
<entry>294276 AD</entry>
<entry>1 microsecond</entry>
@@ -1776,7 +1776,7 @@ SELECT 'abc \153\154\155 \052\251\124'::bytea;
<row>
<entry><type>timestamp [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
<entry>8 bytes</entry>
- <entry>both date and time, with time zone</entry>
+ <entry>both date and time, with time zone adjustment</entry>
<entry>4713 BC</entry>
<entry>294276 AD</entry>
<entry>1 microsecond</entry>
@@ -1800,7 +1800,7 @@ SELECT 'abc \153\154\155 \052\251\124'::bytea;
<row>
<entry><type>time [ (<replaceable>p</replaceable>) ] with time zone</type></entry>
<entry>12 bytes</entry>
- <entry>time of day (no date), with time zone</entry>
+ <entry>time of day (no date), with time zone adjustment</entry>
<!-- see MAX_TZDISP_HOUR in datatype/timestamp.h -->
<entry>00:00:00+1559</entry>
<entry>24:00:00-1559</entry>
@@ -2263,8 +2263,9 @@ 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 value is stored internally as UTC, and the
- originally stated or assumed time zone is not retained.
+ In either case, the value is stored internally as UTC. The
+ originally stated or assumed time zone is not retained and
+ <emphasis>cannot</emphasis> be retrieved later.
</para>
<para>
