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@gmx.net   http://funkturm.homeip.net/~peter