MIME-Version: 1.0
References: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org>
 <agPDkRGXmdyD1EcR@momjian.us>
 <ddf41f033a8add84e1f28a095defafae@richardneill.org>
 <agR45nws-Y1StCCH@momjian.us>
 <CANwZ8rmZdajKmt1KmTRhO7ex-RUeQNwu7yQw19mWaR08B+BdHA@mail.gmail.com>
 <agS4qwKSeb-8CrJc@momjian.us>
In-Reply-To: <agS4qwKSeb-8CrJc@momjian.us>
From: Robert Treat <rob@xzilla.net>
Date: Thu, 14 May 2026 07:57:21 -0400
Message-ID: 
 <CABV9wwPSTVtyBpe8J2kJ1JA29ZKTVtB3+Wjo2gFLJYWZCbqo-Q@mail.gmail.com>
Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the
 timezone.
To: Bruce Momjian <bruce@momjian.us>
Cc: Kirk Parker <khp@equatoria.us>, Richard Neill <rjn@richardneill.org>,
 github@richardneill.org,
	pgsql-docs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABV9wwPSTVtyBpe8J2kJ1JA29ZKTVtB3%2BWjo2gFLJYWZCbqo-Q%40mail.gmail.com>
Precedence: bulk

On Wed, May 13, 2026 at 1:45=E2=80=AFPM Bruce Momjian <bruce@momjian.us> wr=
ote:
> On Wed, May 13, 2026 at 08:57:54AM -0700, Kirk Parker wrote:
> > On Wed, May 13, 2026 at 6:13=E2=80=AFAM Bruce Momjian <bruce@momjian.us=
> 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 differ=
ent
> >     > behaviour.
> >     > I understood "timestamp with timezone" as "a data type which stor=
es 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 times=
tamp"
> > would  be better,   But of course there is no possibility of changing i=
t 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 wou=
ld 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 offs=
et).
> >     > Presumably the answer is to store both timestamptz AND the intege=
r
> >     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 regio=
n, which
> > is not exactly the same thing) from the user's perspective, go ahead an=
d create
> > a column for that.  FWIW I can't think of a single time in over 3 decad=
es 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.
>

It is actually requested so often, there is an extension for it.
https://pgxn.org/dist/timestampandtz/

That said, I would note that most people who go down that path usually
find that it has a hard time holding up, due to general complexity and
misunderstanding of how time, dates, and timezones really work. For
example, both upthread examples of where this would apply are not
always true; new years don't always start at midnight (some lunar
calendars mark the new year based on visibility of the moon, not a
specific time like midnight; not to mention places that may operate
under multiple (ie local vs official timezones). Similarly, if you can
find a global business that will let you measure its business
statistics 9-5 m-f, well, what a way to make a living I guess.

But my point here is that if you think this is what you need, check
out the extension above.

> > 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 con=
fusion:
> >
> >     Name                                 Description
> >     timestamp [ without time zone ]  =3D=3D  both date and time (no tim=
e zone)
> >     timestamp with time zone         =3D=3D  both date and time, with t=
ime 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 Wi=
th Time
> > Zone converts from the specified offset (or session's time zone if no o=
ffset 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 s=
tore 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:
>

The quibble I have with the term "time zone adjustment" is that it
implies there will be some adjustment, but while you can change your
timezone preferences to display a timestamptz value in whatever time
zone you want, the value itself is never adjusted; it is stored as a
universal moment in time that doesn't change, regardless of the window
dressing around it. For this reason I have taken to describing that
difference between the two as that timestamptz stores the date and
time with timezone awareness, and timestamp is date and time with no
timezone awareness. Perhaps that might work better here as well?


Robert Treat
https://xzilla.net