MIME-Version: 1.0
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>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thu, 14 May 2026 09:35:19 -0700
Message-ID: 
 <CAKFQuwYR_W1tCKhaYPjfzd9NYVg5U_jfWN0Oa5YJxx9N+MMvLg@mail.gmail.com>
Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the
 timezone.
To: Richard Neill <rjn@richardneill.org>
Cc: Bruce Momjian <bruce@momjian.us>,
 "github@richardneill.org" <github@richardneill.org>,
	"pgsql-docs@lists.postgresql.org" <pgsql-docs@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ad56e60651c9ae79"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwYR_W1tCKhaYPjfzd9NYVg5U_jfWN0Oa5YJxx9N%2BMMvLg%40mail.gmail.com>
Precedence: bulk

--000000000000ad56e60651c9ae79
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=99t how it is documented.  Timestamp without timez=
one has
no timezone context so interpreting it as =E2=80=9Cin utc=E2=80=9D is not j=
ustified 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 conversio=
n
> if they want it)


Doesn=E2=80=99t exist, don=E2=80=99t 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 logic=
al
> 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=E2=80=99m not against examples of common usages and documenting gotchas b=
ut 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.

--000000000000ad56e60651c9ae79
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wednesday, May 13, 2026, Richard Neill &lt;<a href=3D"mailto:rjn@richard=
neill.org">rjn@richardneill.org</a>&gt; wrote:<blockquote class=3D"gmail_qu=
ote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex=
">
<br>
No, I think doesn&#39;t. That&#39;s probably the root of the confusion.<br>
<br>
Using &quot;timestamp without timezone&quot; could be interpreted as:<br>
<br>
=C2=A0 &quot;Armistice Day is at 11:00 on 11/11 in UTC&quot;<br>
=C2=A0 (and we&#39;ll intentionally overlook the formatting of this to bodg=
e it<br>
=C2=A0 =C2=A0for display in different timezones as if it were local)</block=
quote><div><br></div><div>Sure, but that isn=E2=80=99t how it is documented=
.=C2=A0 Timestamp without timezone has no timezone context so interpreting =
it as =E2=80=9Cin utc=E2=80=9D is not justified by the docs.</div><div><br>=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-l=
eft:1px #ccc solid;padding-left:1ex">
<br>So I actually think there are 4 data-types we need to distinguish:<br><=
br>
1. timestamp=C2=A0 (a UTC value, where the user must do the timezone conver=
sion if they want it)</blockquote><div><br></div><div>Doesn=E2=80=99t exist=
, don=E2=80=99t see it being added.</div><div>=C2=A0</div><blockquote class=
=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padd=
ing-left:1ex">
<br>
2. timestamptz (a UTC value, where the database does the conversion implici=
tly for your configured local timezone, or explicitly if you ask)</blockquo=
te><div><br></div><div>Done.</div><div>=C2=A0</div><blockquote class=3D"gma=
il_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-lef=
t:1ex">
<br>
3. timestamp_and_timezone (a struct, which contains timestamp, AND the time=
zone (or longitude) of the human who recorded it)</blockquote><div><br></di=
v><div>Not in core.</div><div><br></div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
4. timestamp_civil (a date and time which is always local, and cannot be ex=
pressed as a single UTC value). This might be achievable as a compound of d=
ate+time.</blockquote><div><br></div><div>This is what already exists in ti=
mestamp.</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
Of these, (3) doesn&#39;t exist, and probably should, hence my original bug=
 report, and (4) is commonly achieved by mis-using (1), but that&#39;s a lo=
gical bug because the types are incompatible.</blockquote><div><br></div><d=
iv>A valid perspective but not one shared by the project.=C2=A0 The absence=
 of 3 is not a bug.</div><div><br></div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
3. While the docs caution against using &quot;time with timezone&quot;, the=
y don&#39;t<br>
recommend whether to use &quot;timestamp&quot; or &quot;timestamptz&quot; a=
s the default. Such a<br>
recommendation might be helpful.<br>
</blockquote>
<br>
Uh, I guess it depends on what the user wants.=C2=A0 Should we make a clear=
<br>
recommendation?<br>
</blockquote>
<br>
Yes. I think the recommendation should probably be something like.</blockqu=
ote><div><br></div><div>I=E2=80=99m not against examples of common usages a=
nd documenting gotchas but they have to be in line with the design we have.=
<br></div><div><br></div><div>As you noted, however, both timestamp and tim=
estamptz exist because data in the world fall into the two categories - loc=
al time and point-in-time.</div><div><br></div><div>David J.</div><div><br>=
</div>

--000000000000ad56e60651c9ae79--