public inbox for pgsql-docs@postgresql.org  

help / color / mirror / Atom feed

From: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: github@richardneill.org
Subject: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
Date: Wed, 22 Apr 2026 11:40:49 +0000
Message-ID: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org> (raw)

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/18/datatype-datetime.html
Description:

Hi,

Just a small bit of feedback on this page:
https://www.postgresql.org/docs/current/datatype-datetime.html

There is a gotcha for the unwary, in that it does not preserve the original
timezone after the data is recorded.
The user needs to understand that timestamp with timezone is performing
implicit conversion for them, but it does not save the TZ value.

One might, naively, expect it to do the "obvious" thing, storing the
original timezone alongside the timestamp, and therefore
allow you to query the data and discover what the timezone was when the data
was input.
(i.e. the on-disk representation of  "17th May at 4pm New York" should be
different from "17th May at 1pm in Los Angeles", and that a query
should be able to do something like  not just the UTC value in the local
timezone, but also the fact that this date was recorded from the perspective
of a New Yorker).

Or in other words,  one might expect that:
SELECT EXTRACT (timezone FROM '2026-04-16 13:00:00+09'::timestamptz);
should be able to tell me the "+09", regardless of my own local time.

May I suggest adding a "Caution" to draw greater attention to this fact, and
recommending an alternative if there is one.

Thanks - Richard

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-docs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, github@richardneill.org
  Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
  In-Reply-To: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox