Re: ISO 8601 "Time Intervals" of the "format with time-unit deignators"

"Ron Mayer" <ron(at)intervideo(dot)com> writes:
> For example "why is 0.001 years less than 0.001 months".

And look at this:

regression=# select '0.99 years'::interval;
interval
----------
11 mons
(1 row)

regression=# select '0.99 months'::interval;
interval
------------------
29 days 16:48:00
(1 row)

It kinda looks like fractional years are converted to integer months
(truncating) while fractional months are moved to the seconds part
of the interval. Ick. The handling ought to be consistent if you
ask me.

> If I'm breaking backward compatability anyway, I'd be happy to tweak
> things like this one too. Unless, of course someone can give me a
> reason why we want fractional years rounded to months, but fractional
> months are rounded to fractions of a second.

Actually, what I'd like to see done with interval is re-implement it as
a three-field entity, separately storing months, days, and seconds.
The separation between months and smaller units is good because a month
isn't a fixed number of any smaller unit, but the same holds true for
days and smaller units (days are not always 24 hours, consider DST
transitions). This would no doubt cause some backwards compatibility
problems, but overall it would fix many more cases than it breaks.
We see complaints about related issues regularly, every spring and fall...

I'm unsure whether fractional months or fractional days are sensible
to accept, but surely we should accept both or reject both. (This might
suggest that the underlying storage for the month and day fields should
be float not int, btw, but I am not sure about it.)

> PS: mailinglist etiquite question... for discussion, should I
> more this to hackers, or continue it here.

At this point it should move to pghackers, I think.

regards, tom lane