public inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedFrom: David G. Johnston <david.g.johnston@gmail.com>
To: Richard Neill <rjn@richardneill.org>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: github@richardneill.org <github@richardneill.org>
Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone.
Date: Thu, 14 May 2026 09:35:19 -0700
Message-ID: <CAKFQuwYR_W1tCKhaYPjfzd9NYVg5U_jfWN0Oa5YJxx9N+MMvLg@mail.gmail.com> (raw)
In-Reply-To: <f26af05e-fbfa-4714-a541-e2738b7072fe@richardneill.org>
References: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org>
<agPDkRGXmdyD1EcR@momjian.us>
<ddf41f033a8add84e1f28a095defafae@richardneill.org>
<agR45nws-Y1StCCH@momjian.us>
<f26af05e-fbfa-4714-a541-e2738b7072fe@richardneill.org>
On Wednesday, May 13, 2026, Richard Neill <rjn@richardneill.org> wrote:
>
>
> No, I think doesn't. That's probably the root of the confusion.
>
> Using "timestamp without timezone" could be interpreted as:
>
> "Armistice Day is at 11:00 on 11/11 in UTC"
> (and we'll intentionally overlook the formatting of this to bodge it
> for display in different timezones as if it were local)
Sure, but that isn’t how it is documented. Timestamp without timezone has
no timezone context so interpreting it as “in utc” is not justified by the
docs.
> So I actually think there are 4 data-types we need to distinguish:
>
> 1. timestamp (a UTC value, where the user must do the timezone conversion
> if they want it)
Doesn’t exist, don’t see it being added.
>
> 2. timestamptz (a UTC value, where the database does the conversion
> implicitly for your configured local timezone, or explicitly if you ask)
Done.
>
> 3. timestamp_and_timezone (a struct, which contains timestamp, AND the
> timezone (or longitude) of the human who recorded it)
Not in core.
> 4. timestamp_civil (a date and time which is always local, and cannot be
> expressed as a single UTC value). This might be achievable as a compound of
> date+time.
This is what already exists in timestamp.
>
> Of these, (3) doesn't exist, and probably should, hence my original bug
> report, and (4) is commonly achieved by mis-using (1), but that's a logical
> bug because the types are incompatible.
A valid perspective but not one shared by the project. The absence of 3 is
not a bug.
>
>> 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?
>>
>
> Yes. I think the recommendation should probably be something like.
I’m not against examples of common usages and documenting gotchas but they
have to be in line with the design we have.
As you noted, however, both timestamp and timestamptz exist because data in
the world fall into the two categories - local time and point-in-time.
David J.
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: david.g.johnston@gmail.com, rjn@richardneill.org, 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: <CAKFQuwYR_W1tCKhaYPjfzd9NYVg5U_jfWN0Oa5YJxx9N+MMvLg@mail.gmail.com>
* 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