Re: No stddev() for interval?

On 5/21/06, Brendan Jurd <direvus(at)gmail(dot)com> wrote:
>
> Ah!
> > Maybe the reason for such thoughts lies in nature of
> > postgres intervals.
> > SQL:2003 standard paper says:
> > 'There are two classes of intervals. One class, called year-month
> > intervals, has an express or implied datetime
> > precision that includes no fields other than YEAR and MONTH, though
> > not both are required. The other class,
> > called day-time intervals, has an express or implied interval
> > precision that can include any fields other than
> > YEAR or MONTH.'
> >
> >
> >
> It certainly would make life easier for interval operations if the month
> <-> day conversion was no longer a factor. Keeping the years and months
> separate from the other fields makes sense to me. It would be nice from a
> standards-compliance point of view too.
>
> AFAICT, the only useful reason for the postgres interval to include the
> 'month' and 'year' units is so that users can perform arithmetic like "now()
> - '3 months'::interval". With two separate types, you could still support
> this kind of operation with the year-month interval, and support all other
> operations with the day-time interval.
>
> Clearly it would be major effort to build the two new interval types, but
> I'm thinking it would be worth the trouble (read: I'm willing to sink my own
> time into it).
>
> Regards
> BJ
>

I've been mulling this idea over for the last couple of days, and I still
think it would be a big improvement to postgres' interval support. The
whole day <-> month problem is a serious fly in the ointment. Nobody's shot
me down yet ... is there any significant downside to having the proposed two
separate interval types?

Here's how I see it working:

* The existing 'interval' type is left in place with existing
functionality, so we don't break existing apps.

* Two new types are added, let's call them 'month interval' and 'second
interval' for the moment. The internal representation of the month type can
be a signed integer, for the second type a signed value that supports a
maximum resolution of 1 microsecond.

* Textual input syntax for 'second interval' is the same general form as
for 'interval', with valid fields being second, minute, hour, day and week.
For 'month interval', only month, year, decade, century and millenium are
considered valid.

* Add functions for specifying the new interval types with numeric input,
e.g., "months(int) returns month interval", "years(int) returns month
interval", "days(int) returns second interval", "seconds(double) returns
second interval".

* Allow (implicit?) cast from month interval to interval, and from second
interval to interval, but not the reverse.

* Eventually, the return type of date and timestamp subtraction becomes
second interval.

Regards,
BJ