Tom Lane writes:
> I don't agree --- five years and three minutes is perfectly meaningful.
> There are only certain things you can validly do with it, however, and
> scaling by a floating-point number isn't one of them, because fractional
> months aren't well-defined. But you can, for example, add it to or
> subtract it from a timestamp to produce a well-defined result timestamp.
Maybe. Or maybe not. Take 1 year and 3 seconds. E.g.,
'2001-08-06 03:03:03' - '1 year 3 seconds' = '2000-08-06 03:03:00'
'2000-08-06 03:03:03' - '1 year 3 seconds' = '1999-08-06 03:03:00'
but
'2001-08-06 03:03:03' - '2000-08-06 03:03:00' = '365 days 3 seconds'
'2000-08-06 03:03:03' - '1999-08-06 03:03:00' = '366 days 3 seconds'
This means either
a) A value such as '1 year 3 seconds' varies depending on context, which
is not how our system is intended to work, or
b) The normal rules of arithmetic do not hold. I doubt the following is
was good idea:
select timestamp '2000-08-06 03:03:03' - ( timestamp '2000-08-06 03:03:03' - interval '1 year 3 seconds' );
?column?
-------------------366 days 00:00:03
select timestamp '2000-08-06 03:03:03' - timestamp '2000-08-06 03:03:03' + interval '1 year 3 seconds' ; ?column?
-----------------1 year 00:00:03
On the other hand, in certain applications even fractional months may be
useful. Banks sometimes organize a year as 360 days and months as 30
days, so talking about 0.5 months might make sense. However, in this case
again, years/months and days/seconds must not be mixed.
Another interesting tidbit here:
select interval '1 year 00:00:03' = interval '360 days 00:00:03' ;?column?
----------t
> The real bogosity in the interval type is that months and seconds are
> not sufficient: it should be months, days, and seconds. As we get
> reminded twice a year by the regression tests, "1 day" and "24 hours"
> are not the same thing.
Agreed.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter