Re: Re: Data type confusion

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(at)gmx(dot)net http://funkturm.homeip.net/~peter