From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: A proposal for Interval Math
Date: Sun, 19 May 2002 13:36:10 -0700
Message-ID: <200205191336.10383.josh@agliodbs.com> (raw)
Developers:
What follows is a proposal for a spec for PostgreSQL handling of Interval
multiplication and divsion. I freely admit that I can't do much to help
implement this spec (my C skills are infantile). However, I think it would
be helpful to at least have a roadmap for eventual Interval map support.
In theory, all of the following calculations should be possible using Postgres
data types:
INTERVAL * NUMBER
INTERVAL / NUMBER
INTERVAL / INTERVAL
However, this has yet to be implemented due partly to the difficult definition
of the above in the SQL92 standard, in which intervals should be divisible by
floats, and the data type should be "sub-typed", with multiplication and
division being possible only for like interval units.
As far as I know, no RDBMS has been able to implement the full SQL92 standard
for Intervals. Were I on the committee, I would argue that it is
conceptually improbable, and not particularly useful. Are they accepting
applicants? :-)
Therefore, I am going to argue for a slightly altered implementation, with
adjustments intended to overcome the essential atomic nature of date units
(i.e. the fact that "0.4 Months" cannot be accurately measured in smaller
interval units).
My proposal is this: that Intervals should be treated in the same way that
Integer math is handled: with automatic rounding and remainders. Further,
Intervals would be multiplied only by Integers, not by floats or numeric
values. This solves a lot of the problems of date unit division by simply
refusing to perform it.
Thus:
INTERVAL / INTEGER = INTERVAL + ( INTERVAL % INTEGER )
INTERVAL * INTEGER = INTERVAL
INTERVAL / INTERVAL = INTEGER + ( INTERVAL % INTERVAL )
In other words:
'7 months' / 2 = '3 months'
'7 months' % 2 = '1 month'
'7 months' * 2 = '14 months'
'7 months' / '3 months' = 2
'7 months' % '3 months' = 1
Ah, you ask, but what about '7 months' / '3 days'? This requires the addtion
of "Interval Rounding" to the Postgresql system. Any division of unlike
Interval increments would require Interval Rounding to the smallest interval
unit. It would be understood that such rounding carries inherent
inaccuracies, in the the same way that Float rounding does. In a "rounded"
Interval value, all of the following would be true:
1 minute = 60 seconds
1 hour = 60 minutes
1 day = 24 hours
1 week = 7 days
1 month = 30 days
1 year = 365 days = 12 months = 52 weeks
Thus:
'7 months' / '3 days' = 70
... even though this is frequently off by +/- 1 on real calendars.
Of course, this would require a builtin function interval_round(INTERVAL,
Interval Unit). Also, the Interval data type would have to be able to hold
counts of interval units higher than the threshold for the next unit
increment, i.e. '345 hours' would have to be a valid INTERVAL on its own
without being automatically rounded into days + hours by the system.
Comments, please?
--
-Josh Berkus
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: A proposal for Interval Math
In-Reply-To: <200205191336.10383.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox