Re: Re: Data type confusion

"Josh Berkus" <josh@agliodbs.com> writes:
> Peter is absolutely correct here.  '1 year'::INTERVAL - '1
> day'::INTERVAL is '364 days'::INTERVAL most of the time.  However, on
> leap years it is '365 days'.

Au contraire, it is always '1 year - 1 day'::INTERVAL.  That is a
two-part interval value and is not reduced further.  When you add it
to a date or timestamp, *then* you find out how many days are meant.

> One way to simplify this would be not to allow any division operations
> on INTERVALS that result in a modulo of a smaller increment than the
> INTERVAL value expressed.  Thus, one could "'3 months'::INTERVAL / 3"
> but would not be allowed to "'2 months::INTERVAL / 3".   However, this
> seems kind of unfair to hour, minute, and second values whose fractions
> are well-defined and easily manipulated.

I was toying with the notion of allowing scalings whose results didn't
introduce any fractional part to the "months" field.  For example

'2 months + 1 day' / 2.0  =  '1 month + 12hrs'

'3 months + 1 day' / 2.0  =  error (can't have a half month)

'61 days' / 2.0          =  '30 days 12hrs'

However, I fear that this would make no sense to anyone who hadn't
thought about the issues as carefully as we have in this thread.

> 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).

Good point.  Ugly as the "30 day" convention is, it is probably close
enough for that sort of thing.
        regards, tom lane