Re: Timestamp with vs without time zone. - Mailing list pgsql-general

On 9/21/21 11:17 AM, FWS Neil wrote:
> 
> 
>> On Sep 21, 2021, at 12:34 PM, Dave Cramer <davecramer@postgres.rocks 
>> <mailto:davecramer@postgres.rocks>> wrote:
>> On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <hjp-pgsql@hjp.at 
>> <mailto:hjp-pgsql@hjp.at>> wrote:
>>
>>     On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
>>     > That's all true and I won't argue about the madness that is
>>     timezones
>>     > in the world. I am simply thinking it would be some sort of a struct
>>     > like thing which would store the numerical value of the time
>>     stamp and
>>     > also the time zone that time was recorded in.  Presumably everything
>>     > else is an insane calculation from there. What was the offset on
>>     that
>>     > day? I guess it depends on the daylight savings time. What would the
>>     > conversion to another time zone be? That would depend on the DST
>>     > settings on that day in both places.
>>
>>     Yes, but HOW IS THAT TIME ZONE STORED?
>>
>>     As a user you can say "I don't care, just make it work somehow".
>>
>>     But as a developer you have to decide on a specific way. And as a
>>     database developer in particular you would have to choose a way which
>>     works for almost everybody.
>>
>>     And that's the problem because ...
>>
>>     > Mankind can't agree on what side of the road to drive on, what the
>>     > electrical voltage should be at the wall, what those plugs
>>     should be,
>>     > how you should charge your phone or anything else for that matter
>>
>>     ... people have different needs and it would be difficult to satisfy
>>     them all.
>>
>>     Simply storing an offset from UTC is simple, fast, doesn't take much
>>     space - but it would be almost as misleading as the current state. A
>>     simple offset is not a time zone.
>>
>>     Storing the IANA timezone names (e.g. 'Europe/Vienna') would store an
>>     identifier for what most people think of as a time zone - but that
>>     takes
>>     a lot of space, it needs a lookup for almost any operation and
>>     worst of
>>     all, you couldn't index such a column (at least not with a btree
>>     index)
>>     because the comparison functions aren't stable.
>>
>>     You could use a numeric indentifier instead of the name, that
>>     would take
>>     less space but wouldn't solve the other problems (and add the problem
>>     that now you have just added another mapping which you need to
>>     maintain).
>>
>>     There are other ways, but I'm sure they all have some pros and some
>>     cons. None will be perfect.
>>
>>     So I don't think there is an obvious (or even non-obvious, but clearly
>>     good) way for the PostgreSQL developers to add a real "timestamp with
>>     timezone" type.
>>
>>     As an application developer however, you can define a compound
>>     type (or
>>     just use two or three columns together) which satisfies the needs of
>>     your specific application.
>>
>>     > It's just that the phrase "timestamp with time zone" would seem to
>>     > indicate the time zone is stored somewhere in there.
>>
>>     I absolutely agree. Calling a type which doesn't include a timezone
>>     "timestamp with timezone" is - how do I put this? - more than just
>>     weird. "timestamp without timezone" should be called "local timestamp
>>     with unspecified timezone" and "timestamp with timezone" should be
>>     called "global timestamp without timezone". However, those aren't SQL
>>     names.
>>
>>
>> I would say this is a perspective thing. It's a timestamp with a time 
>> zone from the client's perspective.
> 
> A timestamp cannot have a time zone and be a valid timestamp.
> 
> Let me explain.
> 
> A timestamp is a single time that exists in the world.  For example 
> March 1, 2021, 4:15 am is a timestamp.
> 
> If you add a time zone (other than UTC) then a time stamp is not always 
> a single time that exists in the world.

You have the above backwards. A time zone locks the timestamp to a 
single point in time.

> 
> For example in the spring using time zone American/Chicago, on April 14, 
> 2021 the time zone time changes at 2am to become 3am.  The time April 
> 14, 2021, 2:30 am simply does not exists.  And therefore cannot be a 
> timestamp.  Apple’s APIs will by default automatically change 2:30am to 
> 3:00am.  Is that correct?  Or should it change to 3:30am?  Apple has the 
> option for the latter, but the APIs don’t work.

In a sense it does, it becomes 3:30 am CDT.

The DST change was actually 03/14/2021.

An example for my timezone US/Pacific:

test(5432)=# select '03/13/2021 2:30 am'::timestamptz;
       timestamptz
------------------------
  2021-03-13 02:30:00-08
(1 row)

test(5432)=# select '03/14/2021 2:30 am'::timestamptz;
       timestamptz
------------------------
  2021-03-14 03:30:00-07


> 
> In the fall it is even worse.  Using time zone America/Chicago, on 
> November 7, 2021, 1:30 am occurs twice.  That does not work as a 
> timestamp.  Which one do you use, the early one or the late one. 
>   Apple’s APIs give you a choice.

It occurs in two different time zones CDT then CST which makes it a 
different time in each case.

> 
> The point being that people do expect to see times in local time, but 
> the only real timestamp is UTC and I can’t ever imagine a need to store 
> time zone information related to a timestamp.  If you need to store the 
> location that data originated from, then store the location or the Time 
> Zone, but it should not be connected to the timestamp.  Location data is 
> completely different than time data.
> 
> Neil
> www.fairwindsoft.com <http://www.fairwindsoft.com>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com