Re: Timestamp with vs without time zone.

On 9/21/21 11:17 AM, FWS Neil wrote:
>
>
>> On Sep 21, 2021, at 12:34 PM, Dave Cramer <davecramer(at)postgres(dot)rocks
>> <mailto:davecramer(at)postgres(dot)rocks>> wrote:
>> On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at
>> <mailto:hjp-pgsql(at)hjp(dot)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(dot)klaver(at)aklaver(dot)com