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 1pLNac-0006rq-GY for pgsql-general@arkaria.postgresql.org; Fri, 27 Jan 2023 12:12:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pLNab-0001qZ-Fz for pgsql-general@arkaria.postgresql.org; Fri, 27 Jan 2023 12:12:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLNab-0001qQ-5c for pgsql-general@lists.postgresql.org; Fri, 27 Jan 2023 12:12:17 +0000 Received: from mail-pj1-x1035.google.com ([2607:f8b0:4864:20::1035]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pLNaY-0005el-RN for pgsql-general@lists.postgresql.org; Fri, 27 Jan 2023 12:12:16 +0000 Received: by mail-pj1-x1035.google.com with SMTP id nm12-20020a17090b19cc00b0022c2155cc0bso4690568pjb.4 for ; Fri, 27 Jan 2023 04:12:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:to:content-language:subject:from :user-agent:mime-version:date:message-id:sender:from:to:cc:subject :date:message-id:reply-to; bh=HTnjD6VqmH+Fya/buXH8ihkie1fNXhKZd3Mby7JzknM=; b=aLCCNivuKY0OzQmuBYYqIN7yqNA1CaYrEVj/LGxMho7/0OvBrXFUksuALqJOKlrnHt TWcnp/faxOGNJXvUSfAYwnyJLjNoF6z84UcD/h/BnPI/tibN6w2msuCD2V7ty1HZDXQn CfmyX/0BpNvXZGQxgU4b5KJ4iMwHpp0lUPurvombEym5QRL1xO1vrL3JZLit4qKv+xDH +i1sQWBDhDkvTdbAPrpGDpAp5wWHQYZei4ZOc2/DTzh/ohYSUaDd5Fg9whdUsTTP+Fi+ nzZKk1jI3n00s58Dz9DnJUYuhy1EZYegjzMvi7VJDdDrgNJNsw8gWKivX040oaNtdVAA eTxQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:to:content-language:subject:from :user-agent:mime-version:date:message-id:sender:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=HTnjD6VqmH+Fya/buXH8ihkie1fNXhKZd3Mby7JzknM=; b=wGpUS1XrIxKaFd+cBYuKdGyfuxqVvGzs8fghPZNrt1SnaWHKiqBHVJlqpEN5GKnkCg nwPzIFZs3NdaI2g2ijOOl2YO4scz6es+yeIzfMRJ7ysOJfhyosAlaGVOG+1FlWlii41y dhW9tdKyOyftThW6nQ4rMX42gXV7oU2e2Jq5O9RuskWUT1MQ5QKBVYlJmW93z3STVMgp PcUupD/em9l6p4FuBuqClonC15IMC4dSzAS5t70C4lraLZexnrvlzKbnrLV1fLoxP76r EZAZx/He2D/A+Y9zaA/86TwPc/IouqBUC49X/Z1ihLDvX940b2xksqIEhg1nxWZGoZU8 q1vg== X-Gm-Message-State: AO0yUKUKjCDoqwqH6T6GiXw4JtT6NBHVcK1QvtsH8HGIeJxfiECVhufZ sRP10Org5nGqR1lu5TJL4K1l+kxGl1o= X-Google-Smtp-Source: AK7set8mqPkTKgmWU36WuNJZ5tqFEMVUhlNjsf2oa0pE+FZO6pPsVUVBS75rCYsBIzSf94AxR7EUOA== X-Received: by 2002:a05:6a20:3d2a:b0:bc:3eaf:8979 with SMTP id y42-20020a056a203d2a00b000bc3eaf8979mr2085888pzi.7.1674821533342; Fri, 27 Jan 2023 04:12:13 -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 v17-20020a170902e8d100b00196047fc25dsm2740765plg.42.2023.01.27.04.12.11 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 27 Jan 2023 04:12:13 -0800 (PST) Sender: Maxim Nikulin Message-ID: Date: Fri, 27 Jan 2023 19:12:08 +0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 From: Max Nikulin Subject: timestamptz, local time in the future, and Don't do it wiki Content-Language: en-US To: pgsql-general@lists.postgresql.org 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 Hi, I am unsure what is the proper mailing list to discuss an the issue, this one or pgsql-doc. PostgreSQL has a reputation of software with excellent support of time zones, so some people take recommendation to use "timestamp with time zone" type excessively literally. I mean the "Don't do this" page in the wiki: 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: http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html Lau Taarnskov. How to save datetimes for future events - (when UTC is not the right answer) I faced a similar issue once. One day I was asking myself looking at the time displayed by a web application - Was this event scheduled in proper time zone or in UTC? - Was the time zone database updated? I do not know particular reason but the time was wrong. It was a morning just after change of time zone offset. Various changes of time zone offset are not really rare, see https://github.com/eggert/tz/blob/main/NEWS However I am unsure what should be recommended instead of timestamptz. A column for timezone identifier in addition to local time as string is an option. Unfortunately there is an issue with ambiguous local time. I noticed a suggestion to use time zones abbreviations: https://www.postgresql.org/docs/current/datetime-invalid-input.html "B.2. Handling of Invalid or Ambiguous Timestamps" Some problems: - tzdata update may include changes of abbreviations - it is necessary to determine these abbreviations somehow Examples: - Prior to abbreviations like "+08" some letters were used for the same timezones. - Abbreviations may be changed between storing the event and scheduled time. The reason is the same. Offset of standard time changed, so timezone database is updated: Europe/Kyiv Sat Sep 23 22:59:59 1989 UT = Sun Sep 24 02:59:59 1989 MSD isdst=1 gmtoff=14400 Europe/Kyiv Sat Sep 23 23:00:00 1989 UT = Sun Sep 24 02:00:00 1989 MSK isdst=0 gmtoff=10800 Europe/Kyiv Sat Sep 28 23:59:59 1991 UT = Sun Sep 29 02:59:59 1991 EEST isdst=1 gmtoff=10800 Europe/Kyiv Sun Sep 29 00:00:00 1991 UT = Sun Sep 29 02:00:00 1991 EET isdst=0 gmtoff=7200 Let's assume that an appointment was created in 1989 or 1990 before tzdata update '1991-02-30 02:30:00' 'Europe/Kyiv' 'MSK' (or +03:00) with hope to get time after time jump. When new rules was applied attempt to convert the same local time to UTC would give time moment before time transition. In this sense Python's approach with fold=0 or fold=1 looks more straightforward, but I have not idea how to do it in Postgres: >>> from datetime import datetime >>> from zoneinfo import ZoneInfo >>> zi = ZoneInfo("Africa/Juba") >>> "{:%F %T %z %Z}".format(datetime(2021, 1, 31, 23, 30, 0, tzinfo=zi, fold=0)) '2021-01-31 23:30:00 +0300 EAT' >>> "{:%F %T %z %Z}".format(datetime(2021, 1, 31, 23, 30, 0, tzinfo=zi, fold=1)) '2021-01-31 23:30:00 +0200 CAT' Details for this thime transition: zdump -v Africa/Juba | grep 2021 Africa/Juba Sun Jan 31 20:59:59 2021 UT = Sun Jan 31 23:59:59 2021 EAT isdst=0 gmtoff=10800 Africa/Juba Sun Jan 31 21:00:00 2021 UT = Sun Jan 31 23:00:00 2021 CAT isdst=0 gmtoff=7200 It would be great to learn a better approach for appointments in local time in general and for treating of local time ambiguity in particular. I have some links, but they are more to state the problem than to present a robust solution. I do not think they are suitable for the wiki section. To summarize, please, document timestamps in the future as an exception from the "use timestamp with time zone" rule. It would be nice to recommend a better approach to ensure correct local time despite changes in tzdata.