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 1wNXqn-000tRw-1p for pgsql-docs@arkaria.postgresql.org; Thu, 14 May 2026 15:19:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wNXqm-00DL29-17 for pgsql-docs@arkaria.postgresql.org; Thu, 14 May 2026 15:19:48 +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 1wNB3Q-009TRV-1j for pgsql-docs@lists.postgresql.org; Wed, 13 May 2026 14:59:20 +0000 Received: from mx1.mythic-beasts.com ([2a00:1098:0:86:1000:0:2:1]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1wNB3M-00000000Psx-33nZ for pgsql-docs@lists.postgresql.org; Wed, 13 May 2026 14:59:19 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=richardneill.org; s=mythic-beasts-k1; h=From:To:Subject:Date; bh=6dy6H3F9CzOc+I86Wm+avozkaghbDczrHxMPb/TjXgY=; b=wEqfBfK9kzjakm7XTrhJ4dPw4X TlD1Xcs76IYpj8AQdegW9Z6v4bxdBJIdvzpGEXRHbvnzYr6S8/yR73wehPHl2EAFnxVCxI6lvUYXd iiLhUcxD2G1nz90SLDcCWTvjK8Kvk7/8DFYjd9qQbzunakmSQHGe2nlU0YtI0o5zMO1MTbZn7jtEO 48lA8H9ikcTmQeKn4GZUvd2h4H6KPeS9Xu9GbBgjug2ggL/Hh0sl85FC0qBgIauJObjQkB5QA65++ 5bQg+20U5oYlm9EfWIYo/RPgvrOOtDgFl5t+fcPZZ/9ZZ5DNKadVJSrA/muiTjRjoe0ooMBMBArOe CqbThq/A==; Received: by mailhub-cam-d.mythic-beasts.com with esmtpsa (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wNB3L-00EbnM-2H; Wed, 13 May 2026 15:59:15 +0100 Message-ID: Date: Wed, 13 May 2026 15:59:11 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Please clarify that Timestamp with Timezone doesn't preserve the timezone. To: Bruce Momjian Cc: github@richardneill.org, pgsql-docs@lists.postgresql.org References: <177685804902.403059.7237129094698721575@wrigleys.postgresql.org>

Content-Language: en-US From: Richard Neill In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-BlackCat-Spam-Score: 0 X-Spam-Status: No, score=-0.1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Thanks for your reply, On 13/05/2026 14:13, Bruce Momjian wrote: > On Wed, May 13, 2026 at 03:21:11AM +0100, Richard Neill wrote: >> Hi Bruce, >> >> 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." made in >> bold. > > I have created the attached patch which is more explicit and adds an > tag. Thanks. >> 2. How to store a general timestamp in NO timezone (for example, to store >> the concept that "Armistice Day is remembered at 11:00 on 11/11" - 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, 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 = 'Asia/Tokyo'; > > SELECT * FROM test; > x > ---------------------------- > --> 2026-05-13 09:08:56.485716 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) What we actually need here is a data-type to logically represent the concept of "This is a local civil date/time, which explicitly does not have a single well-defined UTC value. I think that such a data-type should not be able to be cast to a timestamptz without resulting in an error (unless it were first cast a string). For example: imagine that I'm storing a calendar. I want to represent, in this data-type, two values: * "Armistice Day is remembered at 11:00 on 11th day of 11th month" * The next presidential inauguration happens at noon (Eastern time) on 20th January 2028 at noon. If I'm in London, then Armistice Day is remembered at 11:00 am (local-time), but the presidential inauguration happens at 5pm (local-time). i.e. one of the data-values should convert for display in local-time, while the other one is already defined in local-time and should not be converted. This problem applies in many contexts: * The start of the new year (midnight, local-time, all timezones) * Business statistics are measured from 9am Mon-5pm Friday (worldwide) * Daily production figures (24 hour long days, where we explicitly ignore the 23 or 25-hour day as the clocks go back/forward). * Shift patterns, where a shift is always 8 hours long, always begins at 00:00, 08:00, 16:00, and you need to be able to use generate_series() to get the recorded data-points over a year (i.e. we are explicitly 'lying' about the clocks going back/forward, rather than ignoring it). 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) 2. timestamptz (a UTC value, where the database does the conversion implicitly for your configured local timezone, or explicitly if you ask) 3. timestamp_and_timezone (a struct, which contains timestamp, AND the timezone (or longitude) of the human who recorded it) 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. 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. > >> 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. --- begin --- Whether you use timestamp, or timestamptz, the underlying record will be stored in UTC: * Use timestamp if you are working in UTC * Use timestamptz if you want to always "think" in local time, and have the database implicitly handle the input/output conversions." * (Mis)use timestamp if you want to work in the local civil timezones, where time is a purely local value, and UTC doesn't exist, e.g. "the working day begins at 9am" or "Data from Monday-to-Friday" where you have factories in different parts of the world. For example, "Breakfast time is 8am (wherever you are)", rather than "Breakfast time, 8am in your specific timezone, happened at XXX value of UTC". i.e. you're still recording the value in UTC in the database, but interpreting it as if it were not. ---end-- Best wishes, Richard