Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.

On Wed, May 13, 2026 at 08:57:54AM -0700, Kirk Parker wrote:
> On Wed, May 13, 2026 at 6:13 AM 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 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  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.