Re: No stddev() for interval?

>
> 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.'
>
> So, the basic question is 'why Postgres allows to combine month and day?'
> Actually, is it good idea?
>
> If we have two separate interval types - we haven't the problem of '1
> month VS 30 days' at all... And if we have no such a problem, we would
> work with intervals as with numbers (I don't see the strong reason for
> absense of stddev() and even variance() for INTERVAL YEAR TO MONTH,
> INTERVAL DAY TO SECOND, INTERVAL MINUTE TO SECOND and so on).
>
> --
> Best regards,
> Nikolay
>

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