On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <hjp-pg...@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. > Dave Cramer > www.postgres.rocks > >
