Re: Re: Timestamp with vs without time zone.
On 2021-09-21 13:43:46 -0400, Dave Cramer wrote: > > > On Tue, 21 Sept 2021 at 13:40, Peter J. Holzer wrote: > > On 2021-09-21 13:34:21 -0400, Dave Cramer wrote: > > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer wrote: > > On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > > Calling a type which doesn't include a timezone > > "timestamp with timezone" is - how do I put this? - more than just > > weird. > > > > I would say this is a perspective thing. It's a timestamp with a time > > zone from the client's perspective. > > I disagree. When I read back the value the original timezone is lost. So > it clearly DOESN'T store the timestamp WITH the timezone. > > > I never said it stored the timezone. I said that it has a timezone. The raison d’être of a database is to store data. If some data isn't stored, the database doesn't have it, in my opinion. As a different example, I can store a number with 15 decimal digits in a float4 and I can get 15 decimal digits out again: hjp=> create table t (f float4); CREATE TABLE hjp=> insert into t(f) values(1.23456789012345); INSERT 0 1 hjp=> select f::float8::numeric from t; ╔══╗ ║f ║ ╟──╢ ║ 1.23456788063049 ║ ╚══╝ (1 row) But those digits aren't the same I stored. So a float4 doesn't "have" 15 decimal digits of accuracy. Not even 8, although in this specific case the first 8 digits happen to be correct. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Re: Timestamp with vs without time zone.
On Tue, 21 Sept 2021 at 13:40, Peter J. Holzer wrote: > On 2021-09-21 13:34:21 -0400, Dave Cramer wrote: > > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer wrote: > > On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > > > 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. > > I disagree. When I read back the value the original timezone is lost. So > it clearly DOESN'T store the timestamp WITH the timezone. > > I never said it stored the timezone. I said that it has a timezone. Dave Cramer www.postgres.rocks > >
Re: Re: Timestamp with vs without time zone.
On 2021-09-21 13:34:21 -0400, Dave Cramer wrote: > On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer wrote: > On 2021-09-21 20:50:44 +1200, Tim Uckun wrote: > > 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. I disagree. When I read back the value the original timezone is lost. So it clearly DOESN'T store the timestamp WITH the timezone. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Re: Timestamp with vs without time zone.
On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer 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 > >
Re: Re: Timestamp with vs without time zone.
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. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Aw: Re: Re: Timestamp with vs without time zone.
> It's just that the phrase > "timestamp with time zone" would seem to indicate the time zone is > stored somewhere in there. Now, I can fully agree with _that_ :-) Karsten
Re: Re: Timestamp with vs without time zone.
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. 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 so there is no way of avoiding the insanity. It's just that the phrase "timestamp with time zone" would seem to indicate the time zone is stored somewhere in there. On Tue, Sep 21, 2021 at 8:44 PM Peter J. Holzer wrote: > > On 2021-09-21 09:39:59 +0200, Karsten Hilbert wrote: > > > It seems like it would be so much more useful if the timestamp with > > > time zone type actually stored the time zone in the record. > > > > Which one ? > > To expand on that question a bit: > > There are several formats to specify a time zone: By offset, by name > (several nomenclatures), etc. > > For example, Karsten's mail had a timestamp of "2021-09-21 09:39:59 > +0200". Thst's enough information to convert it to UTC, but not enough > for date arithmetic. For example what is > '2021-09-21 09:39:59 +0200' + '2 months'::interval? > > Should the result be '2021-11-21 09:39:59 +0200' or '2021-11-21 09:39:59 > +0100'? I'm guessing that Karsten is in Germany, so it's probably the > latter. But to compute that you need to know that the timezone is > Europe/Berlin (or at least CET). Even that is not enough for dates in > the more distant future. The EU has decided to abolish DST (that should > have happened in 2020, but of course there was that little problem that > got in the way), but we don't know when that will happen and which > timezone Germany will choose. So for a date in e.g. 2025 we simply don't > know what the timezone offset will be. > > hp > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!"
Re: Re: Re: Timestamp with vs without time zone.
Yes it would record the timestamp and then also record the time zone. That way all the conversion functions would still work. That's the way it works in the programming languages I use anyway. On Tue, Sep 21, 2021 at 8:09 PM Karsten Hilbert wrote: > > > > > It seems like it would be so much more useful if the timestamp with > > > > time zone type actually stored the time zone in the record. > > > > > > Which one ? > > > > > One specified by the user. Many date formats carry either an offset > > or the time zone information. > > What would that TZ mean, exactly, especially in relation to the timestamp > itself ? > > Would the timestamp be stored as that TZ ? > > Karsten
Re: Re: Timestamp with vs without time zone.
On 2021-09-21 09:39:59 +0200, Karsten Hilbert wrote: > > It seems like it would be so much more useful if the timestamp with > > time zone type actually stored the time zone in the record. > > Which one ? To expand on that question a bit: There are several formats to specify a time zone: By offset, by name (several nomenclatures), etc. For example, Karsten's mail had a timestamp of "2021-09-21 09:39:59 +0200". Thst's enough information to convert it to UTC, but not enough for date arithmetic. For example what is '2021-09-21 09:39:59 +0200' + '2 months'::interval? Should the result be '2021-11-21 09:39:59 +0200' or '2021-11-21 09:39:59 +0100'? I'm guessing that Karsten is in Germany, so it's probably the latter. But to compute that you need to know that the timezone is Europe/Berlin (or at least CET). Even that is not enough for dates in the more distant future. The EU has decided to abolish DST (that should have happened in 2020, but of course there was that little problem that got in the way), but we don't know when that will happen and which timezone Germany will choose. So for a date in e.g. 2025 we simply don't know what the timezone offset will be. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Aw: Re: Re: Timestamp with vs without time zone.
> > > It seems like it would be so much more useful if the timestamp with > > > time zone type actually stored the time zone in the record. > > > > Which one ? > > > One specified by the user. Many date formats carry either an offset > or the time zone information. What would that TZ mean, exactly, especially in relation to the timestamp itself ? Would the timestamp be stored as that TZ ? Karsten
Re: Re: Timestamp with vs without time zone.
One specified by the user. Many date formats carry either an offset or the time zone information. On Tue, Sep 21, 2021 at 7:39 PM Karsten Hilbert wrote: > > > It seems like it would be so much more useful if the timestamp with > > time zone type actually stored the time zone in the record. > > Which one ? > > Karsten >
