Search Postgresql Archives

Re: No stddev() for interval?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux