Thread: Text to interval conversion can silently truncate data

jack=# select '1.51 years'::interval = '1.52 years'::interval;
 ?column?
----------
 t
(1 row)

This is surprising. Once I looked at the C code for Interval it makes more sense given that it cannot represent fractional years, months, or days. Wouldn't it make more sense to raise an invalid input error than to silently truncate data?

Jack

Jack Christensen <jack@jackchristensen.com> writes:
> jack=# select '1.51 years'::interval = '1.52 years'::interval;
>   ?column?
> ----------
>   t
> (1 row)

> This is surprising. Once I looked at the C code for Interval it makes
> more sense given that it cannot represent fractional years, months, or
> days. Wouldn't it make more sense to raise an invalid input error than
> to silently truncate data?

Well, "1.5 years" is perfectly valid (it means 18 months).  So I don't
think rejecting fractional years altogether would be a good idea.

Really your complaint is not very different from complaining because
1.23456789::float4 = 1.234567891::float4.  It's just a property of the
datatype that certain inputs aren't represented exactly.  Another example
that stays within the interval datatype is

regression=# select '1.000001 seconds'::interval;
    interval
-----------------
 00:00:01.000001
(1 row)

regression=# select '1.0000001 seconds'::interval;
 interval
----------
 00:00:01
(1 row)

I doubt people would thank us for rejecting that second input altogether.

            regards, tom lane