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.94.2) (envelope-from ) id 1tfcN2-003k1k-HI for pgsql-docs@arkaria.postgresql.org; Wed, 05 Feb 2025 10:11:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tfcN1-00DOjT-Ae for pgsql-docs@arkaria.postgresql.org; Wed, 05 Feb 2025 10:10:59 +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.94.2) (envelope-from ) id 1tezfE-00FE4V-GA for pgsql-docs@lists.postgresql.org; Mon, 03 Feb 2025 16:51:12 +0000 Received: from mail-ot1-x330.google.com ([2607:f8b0:4864:20::330]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tezfB-0034Gr-1R for pgsql-docs@lists.postgresql.org; Mon, 03 Feb 2025 16:51:12 +0000 Received: by mail-ot1-x330.google.com with SMTP id 46e09a7af769-726819aa3fcso844817a34.0 for ; Mon, 03 Feb 2025 08:51:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738601468; x=1739206268; 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=/S1tlzqnfKQDtpzQtVFginhqJE0byfUN37FxHMfenhI=; b=O8FuuRCyUd1QUTUtV8LgswPdRxR3G3UiFyv5anILR9VvaE4XVMG5OfZSAcFwtj4mKv yDc+WsFLLdVQI0N6CdHYYWPQYdYwQ1PKt6/xA9geS5JpSpUVbylc0qHURW4ySdL+e+6U zIxuK+Z/4SYLswJSRnyS+wQw3Qo37w0JizkslhB3ZMuQo16jdGBMuuZyAMjVEeiQva6H thdsBHpaYh+Vtv1Nb1WwgT8HgyVEa5zirpo/7cINjdm0fwgMrYK/gvLSzYbFQQhH8AVi KYrc59TSAqu8s01lCQQnWOExbbyERdwZS1G/ffKlMuXvhtDHI/59/3ZjfzPMU/7Hrru7 WAFA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738601468; x=1739206268; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=/S1tlzqnfKQDtpzQtVFginhqJE0byfUN37FxHMfenhI=; b=PQ4GhnA4o3+RQdbJJuRGUvx1mjBQEIU1YcuW78mvtmMjpwfgGyOytjR9Tv7qN1937w 6H5YI9reSlDwkuUg9DFD/vVhGFh0kgSjTdd03oN/pD7eFPKPIytA51Sv2CvuwOHQVrYP Bqf3Xw8jXNjRvWpW4jlUGpckgdAl2Ysi8ooGEN9b1gK2iHb502tSNIxTuCWZeDbFtnWp ON61GZJOtKfhHS04BgJiWkJ+tE2myoNLCVcu5KkmUO2cLkKabmdJfC011RKiRJck7PUH pSCMDYYlhAbHFH7rN2qRWmKjK2Ww4ju3i8zlDNCGry69S+5hpelttCHm7HzaV1wfyXZM WS5g== X-Forwarded-Encrypted: i=1; AJvYcCWw2onFzS+jFNCLd68MyGzGzjn0QT7JgLliphIf5+YIux9Yz1MQdoCA96OFc8A703hGqmY3mHcXnRkU@lists.postgresql.org X-Gm-Message-State: AOJu0YznBZZIcmujtDpT5RfAboJyrg0dt6q+J5jtgR9n10drFXMEw63R SJNreElraUDeMV02z7p9R1XFycGjCe0F3+Stm/g/xrMzGRWehIyDC3ndB64aDW1wnzMlcYyT/Xp l/ynlwc5aSjyRik8mZhXiGOUnyFSO0A== X-Gm-Gg: ASbGncsPkoX8250h0V+zCkXreXlGkzPmpEja9cAebaQnlNBxcpNpq1xF8SjDr0Vx47w uEdl/oKGVtu/zGA+ZqDWH+Inr66dkviQ/n9co6QiT/kKtB/mrD2nxwl/dVpw7srdAfMbCWJjoI2 Y1naJON5+kdp8krXj1unvh+SQW8cjInw== X-Google-Smtp-Source: AGHT+IFhvyEBedzMPNyCu7e2TvPG2KvZr+ntrGxpVK39zyGX7q6hI+ZMsAksh0Hjr3gbmB0W/5XJ1oabTJmwfHsA+mQ= X-Received: by 2002:a05:6830:3696:b0:71d:fdf7:f8d6 with SMTP id 46e09a7af769-726568f1a0bmr16614429a34.28.1738601468075; Mon, 03 Feb 2025 08:51:08 -0800 (PST) MIME-Version: 1.0 References: <173796426022.1064.9135167366862649513@wrigleys.postgresql.org> <7a7e09581d7e7fa548b4ab2cb823477e5cae14f7.camel@cybertec.at> <391380.1737988609@sss.pgh.pa.us> In-Reply-To: From: Jonathan Stanley Date: Mon, 3 Feb 2025 11:50:57 -0500 X-Gm-Features: AWEUYZmUmLyyKigGk7g5f2-hbasBPH0oA5479Mqzsqm9T1aHRvYEI_K_HVgCJo4 Message-ID: Subject: Re: timestamp with time zone ~> GMT To: Robert Treat Cc: Tom Lane , Laurenz Albe , pgsql-docs@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000e924e8062d3fb218" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e924e8062d3fb218 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you. I believe that explanation would have helped. FWIW, I ultimately decided to simply duplicate the column: 1. keep the timestamp column for sorting/filters/etc. 2. add a full iso 8601 column as text for straightforward client handling (avoids forcing the client to stitch the data back together; simplicity preferred over small data cost IMO). Perhaps this would be a preferred solution for many others' use cases? Certainly up to you all on whether this additional tip is worth including. I'll just say I'm a bit embarrassed to admit how long it took me to realize this answer! On Mon, Feb 3, 2025 at 10:59=E2=80=AFAM Robert Treat wrote= : > On Mon, Jan 27, 2025 at 9:36=E2=80=AFAM Tom Lane wrot= e: > > > > Laurenz Albe writes: > > > On Mon, 2025-01-27 at 07:51 +0000, PG Doc comments form wrote: > > >> Suggestion: Assuming my understanding is accurate - clarify for the > reader > > >> that time zone offset is lost (after conversion to UTC). At risk of > stating > > >> the obvious: "timestamp with time zone" is a rather misleading name. > > >> "timestamp coerced to UTC" or something would be more accurate. > > > > > Your understanding is correct. > > > I personally think of "timestamp with time zone" as an "absolute > timestamp". > > > > Yes. The datatype's behavior is not what you would expect from the > > SQL standard, which makes our choice of the standard-derived name > > rather unfortunate. That choice is well over 25 years old though, > > so there's not much chance of changing it now. > > > > This does seem to come up often enough that it probably is worth being > a bit more explicit about how this works; attached patch attempts > that. > > Note, I dropped the bit about GMT; that change was made ~40 years ago, > and I suspect it is close to noise for many people these days, though > it could be added back if folks feel strongly about it. > > > > To preserve the original time zone that was entered, you'd have to > store it > > > in a separate database column. > > > > The other problem is: what are you gonna store exactly? A numeric > > offset from UTC is unambiguous but doesn't bring much to the table > > compared to what we do now. A time zone name is a possibility, > > but (a) that's bulky and (b) the politicians keep changing the > > DST laws, so the meaning could change. In certain cases like > > appointment calendars, tracking local law is just what you want > > ... but in cases like flight schedules, probably not. > > > > As Tom notes above, what to store is debatable, and generally seems to > conflate storage and display desires together, which makes it hard to > imagine a generic enough implementation to put into core, but there > are some attempts to solve this problem floating around in extension > land. See https://github.com/mweber26/timestampandtz/blob/master/README.m= d > as one such attempt. > > > Robert Treat > https://xzilla.net > --000000000000e924e8062d3fb218 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you. I believe that explanation would have helped.
FWIW, I ultimately decided to simply duplicate the column= :
1. keep the timestamp column for sorting/filters/etc.
2. add a full iso 8601 column as text for straightforward client handling = (avoids forcing the client to stitch the data back together; simplicity pre= ferred over small data cost IMO).=C2=A0

Perhaps th= is would be a preferred=C2=A0solution for many others' use cases? Certa= inly up to you all on whether this additional tip is worth including. I'= ;ll just say I'm a bit embarrassed=C2=A0to admit how long it took me to= realize this answer!

On Mon, Feb 3, 2025 at 10:= 59=E2=80=AFAM Robert Treat <rob@xzilla= .net> wrote:
On Mon, Jan 27, 2025 at 9:36=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Mon, 2025-01-27 at 07:51 +0000, PG Doc comments form wrote: > >> Suggestion: Assuming my understanding is accurate - clarify f= or the reader
> >> that time zone offset is lost (after conversion to UTC). At r= isk of stating
> >> the obvious: "timestamp with time zone" is a rather= misleading name.
> >> "timestamp coerced to UTC"=C2=A0 or something would= be more accurate.
>
> > Your understanding is correct.
> > I personally think of "timestamp with time zone" as an = "absolute timestamp".
>
> Yes.=C2=A0 The datatype's behavior is not what you would expect fr= om the
> SQL standard, which makes our choice of the standard-derived name
> rather unfortunate.=C2=A0 That choice is well over 25 years old though= ,
> so there's not much chance of changing it now.
>

This does seem to come up often enough that it probably is worth being
a bit more explicit about how this works; attached patch attempts
that.

Note, I dropped the bit about GMT; that change was made ~40 years ago,
and I suspect it is close to noise for many people these days, though
it could be added back if folks feel strongly about it.

> > To preserve the original time zone that was entered, you'd ha= ve to store it
> > in a separate database column.
>
> The other problem is: what are you gonna store exactly?=C2=A0 A numeri= c
> offset from UTC is unambiguous but doesn't bring much to the table=
> compared to what we do now.=C2=A0 A time zone name is a possibility, > but (a) that's bulky and (b) the politicians keep changing the
> DST laws, so the meaning could change.=C2=A0 In certain cases like
> appointment calendars, tracking local law is just what you want
> ... but in cases like flight schedules, probably not.
>

As Tom notes above, what to store is debatable, and generally seems to
conflate storage and display desires together, which makes it hard to
imagine a generic enough implementation to put into core, but there
are some attempts to solve this problem floating around in extension
land. See https://github.com/mweber2= 6/timestampandtz/blob/master/README.md
as one such attempt.


Robert Treat
https:/= /xzilla.net
--000000000000e924e8062d3fb218--