Re: Re: Data type confusion

"Josh Berkus" <josh(at)agliodbs(dot)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