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
Show quoted text
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?
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
"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 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
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