Re: [HACKERS] operator suggest " interval / interval = numeric"
On Jan 9, 2008 10:00 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Really? I think you've confused some unimplemented decorative syntax > with what the underlying datatype will or won't do. Fair enough. The underlying type certainly will do it since it works without the opt_interval. > > This is inconsistent. I would like to ultimately not allow operations > > on interval combinations that are not allowed by the SQL standard. > > The spec's approach to datetime operations in general is almost totally > brain-dead, and so you won't find a lot of support around here for hewing > to the straight-and-narrow-spec-compliance approach. If they have not > even heard of daylight-savings time, how can anyone credit them with any > meaningful contact with the real world? We'll cite the spec where it > suits us, but in this area "the spec says you can't do that" carries > very little weight. DST in the sense of doing arithmetic on timestamps? I was not aware that the standard defined the result in such a way that precluded allowing for DST and leap seconds and whatever other time warps you wanted to allow in your database. In fact, looking over the draft of the 2003 standard looks like it takes DST into consideration just fine. It just doesn't allow the use of a non-constant timezone identifier, which admittidly would be useful. > Or were you planning to lobby for removal of our DST support, too? No. The DST support makes sense. wt ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] operator suggest " interval / interval = numeric"
On Jan 9, 2008 11:06 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Brendan Jurd" <[EMAIL PROTECTED]> writes: > > On Jan 10, 2008 5:00 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > >> The spec's approach to datetime operations in general is almost totally > >> brain-dead, ... > > > It's true that the spec fails to consider DST, in that it doesn't > > partition "day" and "second" intervals separately. > > That's only one of the ways in which they ignore DST, and not even the > most important one --- my vote for the spectacularly bad omission is > that SET TIME ZONE only allows constant offsets from UTC. I am assuming that you are advocating the use of the names for timezones that can indicate what happens over a DST change. I think that it would be useful to be able specify a timezone like PST8PDT. > > Whether the spec is braindead w.r.t intervals or not, Postgres is > > clearly giving the wrong answer. > > Sure, but it's not clear that there *is* a right answer. As noted > upthread, a useful approximate answer can be better than no answer > at all. I am not sure that I agree with that. If you need to keep track of the days, you should probably be using intervals using day to second (or narrower) resolution. > > None of these comparisons are sane. > > You can always refrain from making such comparisons, if you think they > are incapable of yielding useful answers. Maybe a way to enable strict compliance to the standard would be useful. > This whole area is pretty messy, and I don't think that there is or can > be a simple uniform solution :-(. We need to tread carefully in > introducing new behaviors that we might regret later. So I'm not in > favor of inventing an interval division operator that just duplicates > functionality that's already there in a more-cumbersome notation. > We might want that operator back someday. Who even wants to argue that > the result datatype should be numeric? Dividing a three-component > quantity by another one doesn't sound to me like an operation that > naturally yields a scalar result. I think this is reasonable. wt ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] operator suggest " interval / interval = numeric"
"Brendan Jurd" <[EMAIL PROTECTED]> writes: > On Jan 10, 2008 5:00 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> The spec's approach to datetime operations in general is almost totally >> brain-dead, ... > It's true that the spec fails to consider DST, in that it doesn't > partition "day" and "second" intervals separately. That's only one of the ways in which they ignore DST, and not even the most important one --- my vote for the spectacularly bad omission is that SET TIME ZONE only allows constant offsets from UTC. > Whether the spec is braindead w.r.t intervals or not, Postgres is > clearly giving the wrong answer. Sure, but it's not clear that there *is* a right answer. As noted upthread, a useful approximate answer can be better than no answer at all. > None of these comparisons are sane. You can always refrain from making such comparisons, if you think they are incapable of yielding useful answers. This whole area is pretty messy, and I don't think that there is or can be a simple uniform solution :-(. We need to tread carefully in introducing new behaviors that we might regret later. So I'm not in favor of inventing an interval division operator that just duplicates functionality that's already there in a more-cumbersome notation. We might want that operator back someday. Who even wants to argue that the result datatype should be numeric? Dividing a three-component quantity by another one doesn't sound to me like an operation that naturally yields a scalar result. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] operator suggest " interval / interval = numeric"
On Jan 9, 2008 10:44 PM, Brendan Jurd <[EMAIL PROTECTED]> wrote: > On Jan 10, 2008 5:00 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > The spec's approach to datetime operations in general is almost totally > > brain-dead, and so you won't find a lot of support around here for hewing > > to the straight-and-narrow-spec-compliance approach. If they have not > > even heard of daylight-savings time, how can anyone credit them with any > > meaningful contact with the real world? We'll cite the spec where it > > suits us, but in this area "the spec says you can't do that" carries > > very little weight. > > It's true that the spec fails to consider DST, in that it doesn't > partition "day" and "second" intervals separately. Should the standard really do that? I mean, a day really is defined as some number of seconds. > But is that really a reason to reject the concept of interval > partitioning altogether? It seems the spec has the right idea, it > just doesn't take it far enough to cover all the bases. I think the standard does a good job with the partitioning. > Whether the spec is braindead w.r.t intervals or not, Postgres is > clearly giving the wrong answer. A year interval is not 360 day > intervals long. A month interval is not shorter than 31 day > intervals. And, thanks to the geniuses who came up with DST, a day > interval is not the same as 24 hour intervals anymore. None of these > comparisons are sane. DST has no bearing on the fact that a day is still 86400 in the mean solar system. There really is no partition for Day down through seconds. It just means that for timestamp operations the day where we spring forward is 23 hours long, and the day where we fall back is 1 day 1 hour. Having said all this, neither a month nor a year is not a fixed number of days. The partitioning system used by the SQL standard seems to deal with this problem pretty well. wt ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] operator suggest " interval / interval = numeric"
On Jan 10, 2008 5:00 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > The spec's approach to datetime operations in general is almost totally > brain-dead, and so you won't find a lot of support around here for hewing > to the straight-and-narrow-spec-compliance approach. If they have not > even heard of daylight-savings time, how can anyone credit them with any > meaningful contact with the real world? We'll cite the spec where it > suits us, but in this area "the spec says you can't do that" carries > very little weight. It's true that the spec fails to consider DST, in that it doesn't partition "day" and "second" intervals separately. But is that really a reason to reject the concept of interval partitioning altogether? It seems the spec has the right idea, it just doesn't take it far enough to cover all the bases. Whether the spec is braindead w.r.t intervals or not, Postgres is clearly giving the wrong answer. A year interval is not 360 day intervals long. A month interval is not shorter than 31 day intervals. And, thanks to the geniuses who came up with DST, a day interval is not the same as 24 hour intervals anymore. None of these comparisons are sane. Regards, BJ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] operator suggest " interval / interval = numeric"
"Brendan Jurd" <[EMAIL PROTECTED]> writes: > On Jan 10, 2008 2:17 AM, Tom Lane <[EMAIL PROTECTED]> wrote: >> You'd have to define exactly what that means, which seems a little >> tricky for incommensurate intervals. For instance what is the >> result of '1 month' / '1 day' ? > Postgres has already made such definitions, to allow direct > interval-interval comparison. Sure. I was just twitting the OP for having not considered these issues. Given that you can get at that behavior by dividing extract(epoch) results, I tend to think we should leave well enough alone. If someone did come up with a brilliant definition of what interval division should do, it would be pretty annoying to have already locked ourselves into a not-so-brilliant definition ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] operator suggest " interval / interval = numeric"
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Tom Lane > Sent: Wednesday, January 09, 2008 10:00 PM > To: Warren Turkal > Cc: Brendan Jurd; Ilya А. Кovalenko; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] operator suggest " interval / interval = numeric" > > "Warren Turkal" <[EMAIL PROTECTED]> writes: > > YEAR TO MONTH and DAY TO {HOUR,MINUTE,SECOND} intervals should not > > combine. PostgreSQL correctly doesn't allow {YEAR,MONTH} TO > > {DAY,HOUR,MINUTE,SECOND} intervals, > > Really? I think you've confused some unimplemented decorative syntax > with what the underlying datatype will or won't do. > > > This is inconsistent. I would like to ultimately not allow operations > > on interval combinations that are not allowed by the SQL standard. > > The spec's approach to datetime operations in general is almost totally > brain-dead, and so you won't find a lot of support around here for hewing > to the straight-and-narrow-spec-compliance approach. If they have not > even heard of daylight-savings time, how can anyone credit them with any > meaningful contact with the real world? We'll cite the spec where it > suits us, but in this area "the spec says you can't do that" carries > very little weight. > > Or were you planning to lobby for removal of our DST support, too? Don't forget indexes. The standard does not breathe a word about them. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] operator suggest " interval / interval = numeric"
"Warren Turkal" <[EMAIL PROTECTED]> writes: > YEAR TO MONTH and DAY TO {HOUR,MINUTE,SECOND} intervals should not > combine. PostgreSQL correctly doesn't allow {YEAR,MONTH} TO > {DAY,HOUR,MINUTE,SECOND} intervals, Really? I think you've confused some unimplemented decorative syntax with what the underlying datatype will or won't do. > This is inconsistent. I would like to ultimately not allow operations > on interval combinations that are not allowed by the SQL standard. The spec's approach to datetime operations in general is almost totally brain-dead, and so you won't find a lot of support around here for hewing to the straight-and-narrow-spec-compliance approach. If they have not even heard of daylight-savings time, how can anyone credit them with any meaningful contact with the real world? We'll cite the spec where it suits us, but in this area "the spec says you can't do that" carries very little weight. Or were you planning to lobby for removal of our DST support, too? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] operator suggest " interval / interval = numeric"
On Jan 9, 2008 9:29 PM, Brendan Jurd <[EMAIL PROTECTED]> wrote: > Sorry, a correction. The issue of years vs. days isn't ignored. A > year is just 12 months, which yields 12 * 30 = 360 days, which is > actually a pretty significant error (1.4% on average). YEAR TO MONTH and DAY TO {HOUR,MINUTE,SECOND} intervals should not combine. PostgreSQL correctly doesn't allow {YEAR,MONTH} TO {DAY,HOUR,MINUTE,SECOND} intervals, and it shouldn't allow operating on invalid intervals combinations either. One mistake that PG does make is that it allows a "FULL RANGE" interval. This weirdness is essentially a YEAR TO SECOND interval that isn't allowed when explicitly requested. wt-time=> select INTERVAL '1 year 1 month 1 day 1:1:1'; interval - 1 year 1 mon 1 day 01:01:01 (1 row) wt-time=> select INTERVAL '1 year 1 month 1 day 1:1:1' YEAR TO SECOND; ERROR: syntax error at or near "SECOND" LINE 1: select INTERVAL '1 year 1 month 1 day 1:1:1' YEAR TO SECOND; ^ This is inconsistent. I would like to ultimately not allow operations on interval combinations that are not allowed by the SQL standard. wt ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] operator suggest " interval / interval = numeric"
On Jan 10, 2008 3:33 PM, Brendan Jurd <[EMAIL PROTECTED]> wrote: > 1 month is deemed equal to 30 days, 1 day is deemed equal to 24 hours > (although for some reason we ignore the issue of years vs. days). > Sorry, a correction. The issue of years vs. days isn't ignored. A year is just 12 months, which yields 12 * 30 = 360 days, which is actually a pretty significant error (1.4% on average). # select interval '1 year' = interval '360 days'; ?column? -- t (1 row) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] operator suggest " interval / interval = numeric"
On Jan 9, 2008 8:33 PM, Brendan Jurd <[EMAIL PROTECTED]> wrote: > I argued in a long-dead thread that we should disallow these kinds of > comparisons altogether, but I didn't manage to generate much > enthusiasm. The overall sentiment seemed to be that the slightly > bogus results were more useful than no result at all. I was wondering why PostgreSQL allowed these types of comparisons. It really shouldn't allow them. wt ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] operator suggest " interval / interval = numeric"
On Jan 10, 2008 2:17 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > You'd have to define exactly what that means, which seems a little > tricky for incommensurate intervals. For instance what is the > result of '1 month' / '1 day' ? > Postgres has already made such definitions, to allow direct interval-interval comparison. 1 month is deemed equal to 30 days, 1 day is deemed equal to 24 hours (although for some reason we ignore the issue of years vs. days). I argued in a long-dead thread that we should disallow these kinds of comparisons altogether, but I didn't manage to generate much enthusiasm. The overall sentiment seemed to be that the slightly bogus results were more useful than no result at all. That being the case, if we're comfortable making these kinds of arbitrary definitions for comparison purposes, it doesn't seem like much of a stretch to allow multiplication and division of intervals using the same rules. Regards, BJ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] operator suggest " interval / interval = numeric"
Warren Turkal escribió: > The year to month and day to second intervals should not overlap. The > standard doesn't actually allow it IIRC. They do on Postgres anyway. Otherwise the type is not all that useful, is it? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] operator suggest " interval / interval = numeric"
The year to month and day to second intervals should not overlap. The standard doesn't actually allow it IIRC. wt On Jan 9, 2008 7:17 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Ilya A. Kovalenko" <[EMAIL PROTECTED]> writes: > > I suggest one more standard date/time operator, to divide one interval > > by another with numeric (or float, for example) result. > > You'd have to define exactly what that means, which seems a little > tricky for incommensurate intervals. For instance what is the > result of '1 month' / '1 day' ? > > regards, tom lane > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] operator suggest " interval / interval = numeric"
"Ilya A. Kovalenko" <[EMAIL PROTECTED]> writes: > I suggest one more standard date/time operator, to divide one interval > by another with numeric (or float, for example) result. You'd have to define exactly what that means, which seems a little tricky for incommensurate intervals. For instance what is the result of '1 month' / '1 day' ? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] operator suggest " interval / interval = numeric"
am Wed, dem 09.01.2008, um 17:33:00 +0700 mailte Ilya A. Kovalenko folgendes: > > I suggest one more standard date/time operator, to divide one interval > by another with numeric (or float, for example) result. > I.e. something like that: > > database=# SELECT '5400 seconds'::interval / '1 hour'::interval; > > ?column? > -- > 1.5 > (1 row) test=# SELECT extract(epoch from '5400 seconds'::interval) / extract(epoch from '1 hour'::interval); ?column? -- 1.5 Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] operator suggest " interval / interval = numeric"
I suggest one more standard date/time operator, to divide one interval by another with numeric (or float, for example) result. I.e. something like that: database=# SELECT '5400 seconds'::interval / '1 hour'::interval; ?column? -- 1.5 (1 row) Ilya A. Kovalenko ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org