Re: 8.3 vs HEAD difference in Interval output? - Mailing list pgsql-hackers

>>> On Mon, Sep 15, 2008 at  4:58 PM, in message
<20219.1221515913@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote: 
> Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
>> Unless I'm compiling stuff wrong, it seems HEAD is giving me
>> slightly different output on Intervals than 8.3 in the roundoff
>> of seconds.   8.3 was rounding to the nearest fraction of a second,
>> HEAD seems to be truncating.
> 
> Yeah, that's surely because of the change to integer timestamps.
> 
>> Am I interpreting this right?  If so, shall I submit a patch
>> that rounds it to hundredths of a second (hundredths seems
>> hardcoded in the sprintf), or perhaps just silently add that
>> fix to the EncodeInterval patch I'm doing any for SQL Standard
>> and ISO intervals?
> 
> This is not the only place where the float-timestamps code has
rounding
> behavior that doesn't appear in the integer-timestamps code.  I
don't
> know if we want to buy into making them act the same ... after all,
> if they acted exactly the same, we'd not have bothered with writing
the
> integer code.  In this example, rounding to hundredths doesn't seem
like
> a particularly good idea; it seems to me it should give you the
exact
> down-to-the-microsecond value.
I find the results on 8.3.3 with integer timestamps surprising:
ccdev=# select version();                                                    version

------------------------------------------------------------------------------------------------------------------PostgreSQL
8.3.3on x86_64-unknown-linux-gnu, compiled by GCC gcc
 
(GCC) 4.1.2 20070115 (prerelease) (SUSE Linux)
(1 row)

ccdev=# select '1 year 2 mons 3 days 04:05:06.699999'::interval;              interval
--------------------------------------1 year 2 mons 3 days 04:05:06.699999
(1 row)

ccdev=# select '1 year 2 mons 3 days 04:05:06.699999'::interval(1);            interval
----------------------------------1 year 2 mons 3 days 04:05:06.70
(1 row)

ccdev=# select '1 year 2 mons 3 days 04:05:06.699999'::interval(2);            interval
----------------------------------1 year 2 mons 3 days 04:05:06.70
(1 row)

ccdev=# select '1 year 2 mons 3 days 04:05:06.699999'::interval(3);            interval
----------------------------------1 year 2 mons 3 days 04:05:06.70
(1 row)

ccdev=# select '1 year 2 mons 3 days 04:05:06.699999'::interval(4);            interval
----------------------------------1 year 2 mons 3 days 04:05:06.70
(1 row)

ccdev=# select '1 year 2 mons 3 days 04:05:06.699999'::interval(5);            interval
----------------------------------1 year 2 mons 3 days 04:05:06.70
(1 row)

ccdev=# select '1 year 2 mons 3 days 04:05:06.699999'::interval(6);              interval
--------------------------------------1 year 2 mons 3 days 04:05:06.699999
(1 row)
-Kevin