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

I'll add another layer of complication.

You have a database server hosted in Australia, and that's also where
your web server and api server is. You have customers all over the
world though so you set up additional API servers in Europe, USA,
Japan etc.

A korean user will fetch you single page app as static HTML from S3
with cloudfront. It will hit your japanese API server,  which will
fetch the data from your japanese read only replica with the master
being in Australia.

The master DB  writes the records has to know your end user is in
Korea somehow so you have to carry that time zone all the way across
those tiers.

To me the ideal solution would be to have a compound object which has
the time zone in it. This object gets passed through the tiers and end
up at the database where it's stored.




On Wed, Sep 22, 2021 at 1:52 PM Tim Cross <theophilusx@gmail.com> wrote:
>
>
> FWS Neil <neil@fairwindsoft.com> writes:
>
> >  On Sep 21, 2021, at 12:34 PM, Dave Cramer <davecramer@postgres.rocks> wrote:
> >  On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <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.
> >
> > 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
willby 
> > default automatically change 2:30am to 3:00am.  Is that correct?  Or should it change to 3:30am?  Apple has the
optionfor the 
> > latter, but the APIs don’t work.
> >
> > In the fall it is even worse.  Using time zone America/Chicago, on November 7, 2021, 1:30 am occurs twice.  That
doesnot work 
> > as a timestamp.  Which one do you use, the early one or the late one.  Apple’s APIs give you a choice.
> >
> > The point being that people do expect to see times in local time, but the only real timestamp is UTC and I can’t
everimagine 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
differentthan 
> > time data.
> >
>
> +1. This is the key point often overlooked. To make matters even more
> complex, the daylight savings switch over dates can change, often at the
> whim of politicians. For example, the daylight savings time has been
> changed in Australia because of major events (such as the Olympics). As
> soon as you bring time zones into the mix, any calculations based on
> differences in dates must now also take into account timezone switchover
> (because you gain/loose 1 hour) AND you need to verify what the date was
> for each year in your time interval. This significantly complicates such
> calculations.
>
> Things can become even more complicated when server and clients are in
> different time zones - which do you use? The time zone of the server or
> the time zone of the client? In some cases it will be an obvious choice,
> but in others perhaps not. Will all the developers then have the same
> view/understanding? What will be the right thing to do when clients fail
> to provide tz info in submitted data? Default to server tz or client tz?
>
> On the other hand, if all timestamps are in UTC, you completely avoid
> this complication and can just map any result to local timezone if
> required. Even better, the client can decide what tz they want the
> values to be displayed in. Any calculations can be performed in UTC
> without the need to reference any DST data.
>
> Timestamps and Time Zones are a misleading concept. A timestamp is a
> point in time best represented without any ambiguity and the best
> representation for machines is UTC. Time zones are for humans and are really
> only needed when humans need to understand or map the timestamp to a
> representation relative to them and is therefore most often best left to
> the client layer.
>
>