Re: [FEATURE REQUEST] Timezone support in org-mode datestamps and org-agenda

[Jean Louis]

* Tim Cross <theophilusx@gmail.com> [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/