From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp11.migadu.com ([2001:41d0:2:bcc0::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms5.migadu.com with LMTPS id oFqiFyRixGOB7gAAbAwnHQ (envelope-from ) for ; Sun, 15 Jan 2023 21:29:24 +0100 Received: from aspmx1.migadu.com ([2001:41d0:2:bcc0::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp11.migadu.com with LMTPS id OE6ZFyRixGOfPQAA9RJhRA (envelope-from ) for ; Sun, 15 Jan 2023 21:29:24 +0100 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 0F777317D4 for ; Sun, 15 Jan 2023 21:29:22 +0100 (CET) Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1pH9cU-0006wx-C5; Sun, 15 Jan 2023 15:28:46 -0500 Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1pH9cR-0006wO-0t for emacs-orgmode@gnu.org; Sun, 15 Jan 2023 15:28:43 -0500 Received: from stw1.rcdrun.com ([217.170.207.13]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1pH9cO-0001no-Jd for emacs-orgmode@gnu.org; Sun, 15 Jan 2023 15:28:42 -0500 Received: from localhost ([::ffff:197.239.8.177]) (AUTH: PLAIN admin, TLS: TLS1.3,256bits,ECDHE_RSA_AES_256_GCM_SHA384) by stw1.rcdrun.com with ESMTPSA id 0000000000055D9C.0000000063C461F8.00002AAE; Sun, 15 Jan 2023 13:28:40 -0700 Date: Sun, 15 Jan 2023 23:24:09 +0300 From: Jean Louis To: Tim Cross Cc: tomas@tuxteam.de, Ihor Radchenko , emacs-orgmode@gnu.org Subject: Re: [FEATURE REQUEST] Timezone support in org-mode datestamps and org-agenda Message-ID: Mail-Followup-To: Tim Cross , tomas@tuxteam.de, Ihor Radchenko , emacs-orgmode@gnu.org References: <63c287ca.a70a0220.4bd14.873b@mx.google.com> <87pmbh1hgx.fsf@localhost> <63c2b8e4.a70a0220.e3b6d.0051@mx.google.com> <87edrxyyeq.fsf@localhost> <87bkn1yx59.fsf@localhost> <63c320d4.170a0220.f284d.c997@mx.google.com> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline In-Reply-To: <63c320d4.170a0220.f284d.c997@mx.google.com> User-Agent: Mutt/2.2.9+54 (af2080d) (2022-11-21) Received-SPF: pass client-ip=217.170.207.13; envelope-from=bugs@gnu.support; helo=stw1.rcdrun.com X-Spam_score_int: -18 X-Spam_score: -1.9 X-Spam_bar: - X-Spam_report: (-1.9 / 5.0 requ) BAYES_00=-1.9, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-orgmode@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+larch=yhetil.org@gnu.org Sender: emacs-orgmode-bounces+larch=yhetil.org@gnu.org X-Migadu-Country: US X-Migadu-Flow: FLOW_IN ARC-Seal: i=1; s=key1; d=yhetil.org; t=1673814563; a=rsa-sha256; cv=none; b=ZARIQSB80RHcW8OtGUVWm5pYwVddg8+Za1nHdnEiNjkYREgEHZzzaBPiXn7ELF1XmF3ZUj kinYn9gAG7DbrLeQhv4+2pt4LALCocoTMHdu6ED8Cp8MFtFGfU36Oia8UdJT8JmsgbW8DP BP/ew0lFdhzxjTHwb8A7XsIiKcduPw6HlMhV3z8aQ/JzX/hXuOHTrpvQclKua2kDqR6r5S ql3TBQhNx0cpJLNK1KAUSPU75608myfkRNwB/ksS9m/0FZt8J1lSeYA/fEJAcmpxdK3wZz 21uMnTP6R9E/WeBC319bNy9NU9cl/vSo16RwHKl03mrfHpb5cD+/6kN6yNtQTQ== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=none; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org"; dmarc=none ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1673814563; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:cc:mime-version:mime-version: content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references:list-id:list-help: list-unsubscribe:list-subscribe:list-post; bh=LW4t+KtXwRlrUAyxRI64Xh9Z1um03ZYNpx/NaVc87Tk=; b=L4Zg11uRW7q23a5ecBMfVcvN/py0eLUbbumaRQetzAEPMNI+TwilpHfKqpYJB2Nq46rgPd cOtxJ1rvYcnOIuFvU16sgW2vaiFmlfjlqlSFe3jBCu2hNDvmWygCZxbSc/mPI8ulAFt4Hq W8rDgkyIO5SXqCOtbQm5ha/up4AAoQD6v3yLdQd8EOF5HwVqDN4HcX/s+nsSBKX2HF7DFH UlGlgluVOE6oBqbA3zrbiMKDgZZlCWRks3MQcyfnCqlX/FbvNM7gmF/wqxgA2AV0bYoeFT iPBqXY1XZgh0b6AkNRs/K5bZXLsxajx2iELWuLTb4zdqxmCdVZwvDN+FM3KUXg== X-Migadu-Spam-Score: -2.42 X-Spam-Score: -2.42 X-Migadu-Queue-Id: 0F777317D4 X-Migadu-Scanner: scn1.migadu.com Authentication-Results: aspmx1.migadu.com; dkim=none; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org"; dmarc=none X-TUID: T7jy9rMKd9Ox * Tim Cross [2023-01-15 00:40]: > The internal representation of timestamps used by Postgres is ALWAYS > UTC, regardless of whether the field was defined as timestamp or > timestamp with timezone. Right. > The only real difference is that if the field is defined as just > timestamp, postgres assumes the data being entered is in local time > and will adjust using the local timezone offset. I was never thinking about it because I have never observed it or maybe I was unable to see it. I don't see it is adjusting anything. Because time without time zone is just UTC time, so it is not adjusting, but adding the time. CREATE TABLE my (timestamp_without timestamp without time zone default now(), timestamp_with timestamp with time zone default now()); \d my Table "public.my" Column | Type | Collation | Nullable | Default -------------------+-----------------------------+-----------+----------+--------- timestamp_without | timestamp without time zone | | | now() timestamp_with | timestamp with time zone | | | now() [local] maddox@admin=# insert into my default values; INSERT 0 1 [local] maddox@admin=# select * from my; timestamp_without | timestamp_with ----------------------------+------------------------------- 2023-01-15 23:07:35.753599 | 2023-01-15 23:07:35.753599+03 (1 row) What if user comes to UTC time zone by Greenwich and then sets the time zone with PostgreSQL: SET TIME ZONE 'UTC'; select * from my; timestamp_without | timestamp_with ----------------------------+------------------------------- 2023-01-15 23:07:35.753599 | 2023-01-15 20:07:35.753599+00 (1 row) One can see that time stamp with time zone is being adjusted. Because it is not same time: select timestamp_with - timestamp_without from my; ?column? ----------- -03:00:00 There is 3 hours difference, though the entry in the database was in the same time and time "looks same" while it is not. The difference is not that PostgreSQL adjusts something but timestamp without time zone is treated as UTC and with time zones is treated as different to UTC, those are different times then even though they may look apparently same. > It will ignore any tz information in the input. If the field is > defined as timestamp with time zone, it will use the offset defined > by the timezone in the input to determine the UTGC value. Or no need for offset, as in the above example. > When displaying a timestamp, postgres always uses the local time > zone unless you request a different timezone using the 'at time > zone' construct. Actualy not, it does not always use local time zone, as for timestamp without time zone, it does not show any different representation regardless of local time zone: set time zone '+4'; SET [local] maddox@admin=# select * from my; timestamp_without | timestamp_with ----------------------------+------------------------------- 2023-01-15 23:07:35.753599 | 2023-01-16 00:07:35.753599+04 > >> Thinking more about this, I can see how it can be important for > >> clocking, and similar auto-recorded information. Users may be surprised > >> to record clocking on some task yesterday just to find the clocking data > >> in future upon travelling from Singapore to San Fran. That is so much right. > >> So, when implementing time zones, we may need to take care about adding > >> the time zone info when auto-inserting timestamps. That is good conclusion. It just comes so late in time. > >> In addition, we may provide some mechanism to set the time zone for: > >> 1. Individual files > >> 2. For all files, including possible time zone transitions over > >> time. One has system time, but one can work with different system file and specifying different time zone, so yes, one shall have global options of the time zone, which should allow settings different than system time. It requires in Org export to understand what is local time zone so that Org export can set global variable of local time zone, as if it was not set, so that when Org file is used in different time zone, that proper time calculation can take place. Settings shall be available: - based on operating system time zone; - which could be rewritten by Org settings for all files; - which could be rewritten by file setting; - which could be rewritten by heading property; - which could be rewritten by timestamp itself; Then the function reading timestamps should understand it, and later provide all calculations. > > Definitely. But the time stamp (with time zone) in itself doesn't > > carry enough context to actually decide that. Ha, ha, that is right, as time with time zone in Org context is not UTC time internally, so that way is very difficult to achieve accuracy, as it should involve all political changes about time zones at any time point in past and in future. -- Jean Take action in Free Software Foundation campaigns: https://www.fsf.org/campaigns In support of Richard M. Stallman https://stallmansupport.org/