Обсуждение: 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
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
"Ilya A. Kovalenko" <shadow@oganer.net> 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
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 <tgl@sss.pgh.pa.us> wrote: > "Ilya A. Kovalenko" <shadow@oganer.net> 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 >
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 Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Jan 10, 2008 2:17 AM, Tom Lane <tgl@sss.pgh.pa.us> 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
On Jan 9, 2008 8:33 PM, Brendan Jurd <direvus@gmail.com> 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
On Jan 10, 2008 3:33 PM, Brendan Jurd <direvus@gmail.com> 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)
On Jan 9, 2008 9:29 PM, Brendan Jurd <direvus@gmail.com> 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
"Warren Turkal" <wturkal@gmail.com> 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
> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
> owner@postgresql.org] 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" <wturkal@gmail.com> 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.
"Brendan Jurd" <direvus@gmail.com> writes:
> On Jan 10, 2008 2:17 AM, Tom Lane <tgl@sss.pgh.pa.us> 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
On Jan 10, 2008 5:00 PM, Tom Lane <tgl@sss.pgh.pa.us> 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
On Jan 9, 2008 10:44 PM, Brendan Jurd <direvus@gmail.com> wrote: > On Jan 10, 2008 5:00 PM, Tom Lane <tgl@sss.pgh.pa.us> 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
"Brendan Jurd" <direvus@gmail.com> writes:
> On Jan 10, 2008 5:00 PM, Tom Lane <tgl@sss.pgh.pa.us> 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
On Jan 9, 2008 11:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Brendan Jurd" <direvus@gmail.com> writes: > > On Jan 10, 2008 5:00 PM, Tom Lane <tgl@sss.pgh.pa.us> 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
On Jan 9, 2008 10:00 PM, Tom Lane <tgl@sss.pgh.pa.us> 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