Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pMos9-0000cL-AI for pgsql-general@arkaria.postgresql.org; Tue, 31 Jan 2023 11:32:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pMos8-000244-0m for pgsql-general@arkaria.postgresql.org; Tue, 31 Jan 2023 11:32:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pMos7-0001zR-K9 for pgsql-general@lists.postgresql.org; Tue, 31 Jan 2023 11:32:19 +0000 Received: from mail-pf1-x434.google.com ([2607:f8b0:4864:20::434]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pMos4-00063h-Uf for pgsql-general@lists.postgresql.org; Tue, 31 Jan 2023 11:32:19 +0000 Received: by mail-pf1-x434.google.com with SMTP id n2so10003515pfo.3 for ; Tue, 31 Jan 2023 03:32:16 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :sender:from:to:cc:subject:date:message-id:reply-to; bh=R2OgUaX8axiBzFF4aBkqNO7e6AiQN2njzfm1Sr03aHI=; b=FfZxrwS6WTteCulrcmt4dm5jE8khqdd2EJ78dIwefyK0xX/GeV2LMNEEyLHlDYCByJ rborFEYbyd6TdJ5DPstlHHf4wuImix5uGYbfusWTCENAUeXBn79RyHXhqTsFpaRpmSYo Y7OZlmvUg+VqoXMLnY2nFuQ5j15X3DJ+GpGqc7i32nCd6qYHLvjTRhM4tSD/tNXLtJ/z Q1Qj8G8GNialqBhDvp7nmCVbCmltoaYSEHsISvMUH50qoaACTjw+7ttI+J+M98z9pFt2 jCSWTj6/nLLClixBmmPbuJT5lYD+Mo3ezf1VYSgJRqCnNveYK7137qtqmsZ+rlLMYDKW ysug== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :sender:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=R2OgUaX8axiBzFF4aBkqNO7e6AiQN2njzfm1Sr03aHI=; b=Yr2TPh3Msgwk+iiZQGh+o6C7bsKMBOJ2tgAOulb8h1DcjqGSQi9h+luDyrh1EJLS4y GqjynqTJwdR//Ot+vSefNLQcxNp+cbqa11OXztLEZqvrg8iL0/XwjOd2ccDKNVqjbi5W 6jcNPw/YKDPzDPyvbl580tiyn/PfB4a7jtEnDM3RYpGqg+IkJYRQHirs2RAFokYl9kW5 oDEM1APArEOTHJeixznNSU7o51iEzEqQmotiV/a6HOyFKd93trvoC560SHT//BsbBi6h Eh554zuUvOq8FmkMVTOBu31i44zz4X5rJDY5EC8wkjo+xTud1jPtJ3ohqTNOOCPaNJYh ntow== X-Gm-Message-State: AO0yUKUHSWQ7XhvbTB/fvFk5MFH4DqFo9rCMEoZWJz0vtnBzNkeSQGC9 Eb2PQ6R29Znq7tNQ/h+gHw/ZORi+63Y= X-Google-Smtp-Source: AK7set+p9RG6Vpl7ATr4HSAc359yubISlXV9kj2SdFWmwd9DZb5Xp+6U6k5AIVK7ExG4a9ITzXUbyg== X-Received: by 2002:a05:6a00:26cf:b0:580:ea08:5277 with SMTP id p15-20020a056a0026cf00b00580ea085277mr9245429pfw.16.1675164734517; Tue, 31 Jan 2023 03:32:14 -0800 (PST) Received: from [192.168.0.101] (nat-0-0.nsk.sibset.net. [5.44.169.188]) by smtp.googlemail.com with ESMTPSA id h6-20020aa79f46000000b0056bb36c047asm9224219pfr.105.2023.01.31.03.32.12 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 31 Jan 2023 03:32:14 -0800 (PST) Sender: Maxim Nikulin Message-ID: Date: Tue, 31 Jan 2023 18:32:09 +0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: timestamptz, local time in the future, and Don't do it wiki Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <20230127180346.pn3gbwaajr2cckg4@hjp.at> From: Max Nikulin In-Reply-To: <20230127180346.pn3gbwaajr2cckg4@hjp.at> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 28/01/2023 01:03, Peter J. Holzer wrote: > On 2023-01-27 19:12:08 +0700, Max Nikulin wrote: >> https://wiki.postgresql.org/wiki/Don%27t_Do_This#Date.2FTime_storage >> >> Could you, please, add a case when the timestamptz type should not be used? >> UTC timestamps of forthcoming events may change due to an updates of tzdata >> if they really scheduled at specific local time. An example: > > Yes. I could argue that this isn't really a "timestamp", though. The > time when the future event will happen isn't fixed yet - it depends on > future decisions (e.g. an update to DST rules or even a complete switch > to a different time zone). Generally I would agree that another term instead of timestamp may help to explain people that timestamps in the future are more complex than they usually expect. Do you have an idea what word should be used instead? Interestingly EcmaScript proposal for `Temporal` (intended to fix issues with `Date` objects) avoids using of "timestamp". "Instant" is chosen instead and the reason is confusion caused by discrepancy of interpretation by various databases: https://tc39.es/proposal-temporal/docs/ambiguity.html#understanding-clock-time-vs-exact-time "Understanding Clock Time vs. Exact Time" in "Time Zones and Resolving Ambiguity" By the way, approach chosen for JavaScript is even more versatile than the one implemented in Python. Instead of just `fold={0,1}` `datetime` object field, conversion of local time + TZ identifier may be controlled by `disambiguation={earlier,later,compatible,reject}`. If time string contains both offset and TZ identifier than another options are available: `offset={use,ignore,prefer,reject}`. However timestamp property in some cases may be convenient as well. https://peps.python.org/pep-0495/ "PEP 495 – Local Time Disambiguation" What I do not like with distinction of known UTC time vs. UTC time that might change is that in real applications the difference might be quite subtle. The same event, e.g. a conference with local and remote (on-line) participants may be scheduled in both ways: in respect to local time zone and with fixed UTC time. Moreover the committee may reconsider their early decision. I am in doubts if it is reasonable to use 2 tables with different column types (timestamptz for UTC or strings for local time and TZ) and to move entry between these tables just because time zone is changed from fixed UTC offset to a location-based one or vice versa.