Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wNByP-000dkH-10 for pgsql-docs@arkaria.postgresql.org; Wed, 13 May 2026 15:58:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wNByN-009kn3-2j for pgsql-docs@arkaria.postgresql.org; Wed, 13 May 2026 15:58:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wNByN-009kmv-1a for pgsql-docs@lists.postgresql.org; Wed, 13 May 2026 15:58:11 +0000 Received: from mail-oo1-xc29.google.com ([2607:f8b0:4864:20::c29]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wNByL-00000000QMx-07Z7 for pgsql-docs@lists.postgresql.org; Wed, 13 May 2026 15:58:11 +0000 Received: by mail-oo1-xc29.google.com with SMTP id 006d021491bc7-6948ff6b006so4124277eaf.2 for ; Wed, 13 May 2026 08:58:08 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1778687887; cv=none; d=google.com; s=arc-20240605; b=QgMI6cBc9cbvWkCzSYdhJCqoPH4UeSG7I5aNRpVmwgc3Co9B49uM4lkqs16c0pAXnB 8noKgFZdGDpDlkIxY89oZTh3oMA6elsWd5A4cAhklXqgYAUCQHCFplEPws3jbYOBKa1E wwa9lCf1xq603MI719mpNzuQ6dC4zFEN5ywY8wGJd59xR94gbxCUUtT6lU/VvfcdXGL5 afxwHw9lAlTat/7JZwsCgM0UzQjEeP+G4JKx8cgIoSXH3tXP2N9NhnfUhJRUCBJKuC0h vB5UkvpCYS2BKLbLnIxEUnm9tC1baj45OBooNPwx5nhEbV6INiCR+HCFaGU7kbJ1y3Bh qOtw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=lo/us03WMtEyto/B5Jpdwf3uImYGBNRigMmbeRAIq8E=; fh=oHG/syxNqoH9vS+dUMc/xVoVs+7G7ZIK1PTp3s7gwBs=; b=bUr4wo6Jav26/f9inn0elpSIQAcbV3ZEBgH0QOFYhmDwNzRbmxFm+TUsZdifsCLgxz ZqTNmTCrzJikcuM5wHMZD1TdqOd7CPTLo7IGeksQfnA8+dMdVqD3KJnlDmpJ0xEmTl6D LaaJZ4uDzQrr73yxwcmZ649O+tmBcERtzZqOTXdNxJLybmF9HG19s31vhfSOzjBi+ZfT FO9zXe8T0QaFo9Tcg65j5GS7j623Bn/IYVLh8nZQnrwAIEVVsWpprswehCdHiX5+pa5G YFFfiJ6hVaZpxLnS0xbcFL0PUUDIigEAirFtaJSK/HPrNU+RuX7YcxOrWAi3RAAEoeII DAFw==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=equatoria-us.20251104.gappssmtp.com; s=20251104; t=1778687887; x=1779292687; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=lo/us03WMtEyto/B5Jpdwf3uImYGBNRigMmbeRAIq8E=; b=NZx5fl98A1PmPx3lYWzcPIpoAL7pw71sHaka6cnNvuw1sZi78MGAIPv6osa8Oz2ygt JbttTOgIwqiOE8QrQ4lSVMOfcnx4ja4m6hOpWFHjdOZuPYPCQcDctJHQShQ4diKE9Q7v Wxq+5S4s1s4AvK1C+Sn44dzaScWb3HGZKqm9v9ug9sacU0hbK4EYkcjXXTXYeGMW7fPk JXtc2g2msb+Q/EkcxwpU/PkxA7ItxGZSnsesefH6PdNE7gZF8+EO+VuhQumBnGFjNfLs wx+HUvY7QQ92pqradeIb6X3LZqdMQeWYRJw8BftUoGDjOQW/v+Fk38F/unQgjYmvH81L RqGw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1778687887; x=1779292687; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=lo/us03WMtEyto/B5Jpdwf3uImYGBNRigMmbeRAIq8E=; b=s0nsH6d2pW1+pLOF1St6RiQ/HzXDDro687sZvnjv4vf0oSxGkSTNLpjsHl2bqDYKUc lH/CQ+G3SUJAf3CZxaaqsbUQpVGRJ/lf/P/G8sHIX9MqijDIryw69HJh3ImAatdVmuvJ u8es33wAHJbFHroC+cXWoGu9qkA9g61BmS7BVxY9utyeKUrki5cclT0cyvfuMKPzgycS tC9W+8zEX7oIJP31uP/5ge2jRKLNOFLbWyzF1r4idTqissFFvcW22p0JwOCn3lvwd8RZ gItfA43kCpz2r3q4ZYUbHAJl/RsCIuRTVKHCxFlFeNw/BwLUZhe35+hp5ZR4ZxA2RqTF q/xQ== X-Forwarded-Encrypted: i=1; AFNElJ/YV3TkT2xnxQSZFaTcA6GaPDgjo6s0NngBSuvTqWtncbL4t7g1sxIZVlCvgb080CcmI0QdABwd9UWR@lists.postgresql.org X-Gm-Message-State: AOJu0Yw9XjQ7KR62FdbBmBLZ0ZPwZD1EQ/kbm8MsuluT22d6e/ocb0iA 8ktNDoX2jnzX8P/4svnDntcN+9brIYWBcnYdnLOCm5PLX1RsG7nI3sMQauANsyBaiJpJL+og/kx S+VIeG9UPngqXWIZx21/PBEcn39VWUYwUtmZ9Lq+E0hcIakEigXTmbqKyng== X-Gm-Gg: Acq92OGr5RBsl0rOiXADYL+oKyxoRXDYE2CGdU97M3mN0Y/WOI1KenIsFEWrxMsGZcj XRR8QSVxHmD5A52ut02btbDYp6Rlm+Gr0j/B2dlagle+ARbKJL04iNWhfxMXncv/Vfp58XDr++X b42kAbatKKQKvJwhqiwDImXRIud0qxq36RRENOrd5OD0iTl/2I+tiDhrNb+Ze0kRpkvD4VCw1FE 4aalP8sBFuWHxtQ4ByHHE/WTg8D6+O5dqkoz/GIf/1DM7DidPBbhgNB3i0SA2oCM9L8AOEjCss3 /Fzmc2GbrJgoM8YCJPex47+bjZjBFgMrdICc X-Received: by 2002:a05:6820:2907:b0:699:b131:d597 with SMTP id 006d021491bc7-69b7a9d781fmr1747420eaf.6.1778687887072; Wed, 13 May 2026 08:58:07 -0700 (PDT) MIME-Version: 1.0 References: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org> In-Reply-To: From: Kirk Parker Date: Wed, 13 May 2026 08:57:54 -0700 X-Gm-Features: AVHnY4JvFKITwkITba-8gSjbFMPN-pK1PYxLsvjH4VFmIhBRUcKiXEEfOaF-kJ0 Message-ID: Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. To: Bruce Momjian Cc: Richard Neill , github@richardneill.org, pgsql-docs@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000acf0550651b50bf9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000acf0550651b50bf9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, May 13, 2026 at 6:13=E2=80=AFAM Bruce Momjian wr= ote: > 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 boldl= y > - > > 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. > So I think it deserves a "Caution" or "note" boxout, or at least to have > > words "the originally stated or assumed time zone is not retained." mad= e > in > > bold. > > I have created the attached patch which is more explicit and adds an > tag. > > > > > 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 sto= re > > 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. A caution, though: an earlier post in this thread mentioned wanting to record "the fact that this date was recorded from the perspective of a New Yorker" but that is a very problematic understanding of IANA timezone names; it's more accurately "the timezone region whose boundaries can vary over time and is identified by the large city 'New York'". South Bend, Indiana is in that same time zone and so is Eastport, Maine. > > 2. How to store a general timestamp in NO timezone (for example, to sto= re > > the concept that "Armistice Day is remembered at 11:00 on 11/11" - whic= h > is > > the same for everyone across the world, even though the underlying UTC > value > > is undefined). Presumably you could store this as 2 columns (date, time= ), > > but there's no compound "date+time" type, and it would not be the same = as > > timestamp. > > Doesn't TIMESTAMP WITHOUT TIME ZONE do this? > > CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZONE); > > INSERT INTO test VALUES ('now'); > > SHOW timezone; > TimeZone > ------------------ > America/New_York > > SELECT * FROM test; > x > ---------------------------- > --> 2026-05-13 09:08:56.485716 > > SET timezone =3D 'Asia/Tokyo'; > > SELECT * FROM test; > x > ---------------------------- > --> 2026-05-13 09:08:56.485716 > > > 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? > 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. And looking at the chapter again, I think table 8.9 does foster the confusion: Name Description timestamp [ without time zone ] =3D=3D both date and time (no time zo= ne) timestamp with time zone =3D=3D 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. > > --000000000000acf0550651b50bf9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Wed, May 13,= 2026 at 6:13=E2=80=AFAM 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 bold= ly -
> 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 differe= nt
> 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 times= tamp" would=C2=A0 be better,=C2=A0 =C2=A0But of course there is no pos= sibility=C2=A0of changing it at this late date.

> So I think it deserves a "Caution" or "note" boxou= t, or at least to have
> words "the originally stated or assumed time zone is not retained= ." made in
> bold.

I have created the attached patch which is more explicit and adds an
<emphasis> tag.

>
> 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).<= br> > Presumably the answer is to store both timestamptz AND the integer tz_= offset
> (is there a "timezone" datatype, or should that just be a st= ring?)

Yes.

Yes, not sure why this is a diffic= ult concept.=C2=A0 "A unique and universal representation of a specifi= c moment=C2=A0of terrestrial timekeeping" is what the timestamptz stor= es.=C2=A0If you want to record the offset (or timezone region, which is not= exactly the same thing) from the user's perspective, go ahead and crea= te a column for that.=C2=A0 FWIW I can't think of a single time in over= 3 decades of building databases where I would have wanted that, but if you= r use case needs it the implementation is simple and obvious.
A caution, though: an earlier post in this thread mentioned wan= ting to record "the fact that this date was recorded from the perspect= ive of a New Yorker" but that is a very problematic understanding of I= ANA timezone names; it's more accurately "the timezone region=C2= =A0whose boundaries can vary over time=C2=A0 and is identified by the large= city 'New York'".=C2=A0 South Bend, Indiana is in that same t= ime zone and so is Eastport, Maine.
=C2=A0
> 2. How to store a general timestamp in NO timezone (for example, to st= ore
> the concept that "Armistice Day is remembered at 11:00 on 11/11&q= uot; - which is
> the same for everyone across the world, even though the underlying UTC= value
> is undefined). Presumably you could store this as 2 columns (date, tim= e),
> but there's no compound "date+time" type, and it would n= ot be the same as
> timestamp.

Doesn't TIMESTAMP WITHOUT TIME ZONE do this?

=C2=A0 =C2=A0 =C2=A0 =C2=A0 CREATE TABLE test (x TIMESTAMP WITHOUT TIME ZON= E);

=C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT INTO test VALUES ('now');

=C2=A0 =C2=A0 =C2=A0 =C2=A0 SHOW timezone;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0TimeZone
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ------------------
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0America/New_York

=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT * FROM test;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0x
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----------------------------
-->=C2=A0 =C2=A0 =C2=A0 2026-05-13 09:08:56.485716

=C2=A0 =C2=A0 =C2=A0 =C2=A0 SET timezone =3D 'Asia/Tokyo';

=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT * FROM test;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0x
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ----------------------------
-->=C2=A0 =C2=A0 =C2=A0 2026-05-13 09:08:56.485716

> 3. While the docs caution against using "time with timezone"= , they don't
> recommend whether to use "timestamp" or "timestamptz&qu= ot; as the default. Such a
> recommendation might be helpful.

Uh, I guess it depends on what the user wants.=C2=A0 Should we make a clear=
recommendation?

How could we usefully m= ake a single recommendation?=C2=A0 The only plausible recommenation is &quo= t;use the datatype that best suits your purposes", and if the document= ation doesn't do an adequate job of describing the differences and appl= icability, that's what should be addressed, rather than asking for a on= e-size-fits-all recommendation.

And looking at the= chapter again, I think table 8.9 does foster the confusion:

=
=C2=A0 =C2=A0 Name=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Description
=C2=A0 =C2=A0 timestamp [ without ti= me zone ]=C2=A0 =3D=3D=C2=A0 both date and time (no time zone)
=C2=A0 = =C2=A0 timestamp with time zone=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D=3D=C2= =A0 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.=C2=A0 *Storage* for both types is identical = (or at least appears that way to the user).

What a= bout 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 t= he session's time zone".=C2=A0 That's fairly wordy for a table= entry, but it does do a better job of conveying what's actually happen= ing and omits the implication that we store the session's time zone or = offset along with the UTC timestamp.

--000000000000acf0550651b50bf9--