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@xxxxxx
<mailto:hjp-pgsql@xxxxxx>> 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@xxxxxxxxxxx
