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.
> So I think it deserves a "Caution" or "note" boxout, or at least to have
> > words "the originally stated or assumed time zone is not retained." made
> in
> > bold.
>
> I have created the attached patch which is more explicit and adds an
> <emphasis> tag.
>
> >
> > 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.
A caution, though: an earlier post in this thread mentioned wanting to
record "the fact that this date was recorded from the perspective of a New
Yorker" but that is a very problematic understanding of IANA timezone
names; it's more accurately "the timezone region whose boundaries can vary
over time and is identified by the large city 'New York'". South Bend,
Indiana is in that same time zone and so is Eastport, Maine.
> > 2. How to store a general timestamp in NO timezone (for example, to store
> > the concept that "Armistice Day is remembered at 11:00 on 11/11" - which
> is
> > the same for everyone across the world, even though the underlying UTC
> value
> > is undefined). Presumably you could store this as 2 columns (date, time),
> > but there's no compound "date+time" type, and it would not be the same as
> > timestamp.
>
> Doesn't TIMESTAMP WITHOUT TIME ZONE do this?
>
> CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZONE);
>
> INSERT INTO test VALUES ('now');
>
> SHOW timezone;
> TimeZone
> ------------------
> America/New_York
>
> SELECT * FROM test;
> x
> ----------------------------
> --> 2026-05-13 09:08:56.485716
>
> SET timezone = 'Asia/Tokyo';
>
> SELECT * FROM test;
> x
> ----------------------------
> --> 2026-05-13 09:08:56.485716
>
> > 3. While the docs caution against using "time with timezone", they don't
> > recommend whether to use "timestamp" or "timestamptz" as the default.
> Such a
> > recommendation might be helpful.
>
> Uh, I guess it depends on what the user wants. Should we make a clear
> recommendation?
>
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.
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.
>
>
