Re: Data type confusion

Josh Berkus wrote:

> Or, to put it another way, 95% of the time users just want to do simple
> things. Like we want to know how many weeks an employee has been with
> us for: '2 years 3 months'::INTERVAL / '1 week'::INTERVAL (and we
> don't care about the fractional week left over).
> Thus we don't want to hold up simple and obvious date multiplication and
> division just to deal with the wierdo cases.

I see now what you are trying to do. It sort of makes sense, but I'm still really reluctant to give (semantic or otherwise) meaning to "yesterday divided by tomorrow" .....

Would it be a better solution if there was a conversion function a la

convert(text, interval) RETURNS double precision

where text in ('day','week','hour', ....). The function would convert the interval to the specified unit, with some considerable degree of fuzziness as discussed in this thread.

Then your query would be simply

convert('week', '2 years 3 months') / 1

and would return something in the vicinity of 117.0 :-)

It seems to me that such a function would be more generally useful than the division of intervals. What you really want to do is not to divide intervals, but to express them in different time units. Or am I missing something (again)?

Comments?

Allan.