public inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedFrom: Bruce Momjian <bruce@momjian.us>
To: github@richardneill.org
To: pgsql-docs@lists.postgresql.org
Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
Date: Tue, 12 May 2026 20:19:29 -0400
Message-ID: <agPDkRGXmdyD1EcR@momjian.us> (raw)
In-Reply-To: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org>
References: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org>
On Wed, Apr 22, 2026 at 11:40:49AM +0000, PG Doc comments form wrote:
> 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.
The documentation says:
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT
For timestamp with time zone values, an input string that
includes an explicit time zone will be converted to UTC
(Universal Coordinated Time) using the appropriate offset for
that time zone. If no time zone is stated in the input string,
then it is assumed to be in the time zone indicated by the
system's TimeZone parameter, and is converted to UTC using the
--> offset for the timezone zone. In either case, the value is stored
--> internally as UTC, and the originally stated or assumed time zone
--> is not retained.
Does this need to be stated more boldly?
--
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.
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: bruce@momjian.us, github@richardneill.org, pgsql-docs@lists.postgresql.org
Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
In-Reply-To: <agPDkRGXmdyD1EcR@momjian.us>
* 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