Re: roundoff problem in time datatype

Jochem van Dieten <jochemd@gmail.com> writes:
> On 9/26/05, Dennis Bjorklund wrote:
>> One reason is because it's what the standard demand.

> Could you cite that? The only thing I can find in the SQL standard is
> that the hour field in an INTERVAL can not exceed 23, not datetimes.

SQL99 has
        _____________Table_11-Valid_values_for_datetime_fields_____________
        _Keyword____________Valid_values_of_datetime_fields________________
       | YEAR             | 0001 to 9999                                  |       |                  |
                            |       | MONTH            | 01 to 12                                      |       |
         |                                               |       | DAY              | Within the range 1 (one) to 31,
butfurther   |                            constrained by the value of MONTH and YEAR                            fields,
accordingto the rules for well-                            formed dates in the Gregorian calendar.
 
       | HOUR             | 00 to 23                                      |       |                  |
                            |       | MINUTE           | 00 to 59                                      |       |
         |                                               |       | SECOND           | 00 to 61.9(N) where "9(N)"
indicates         |                            the number of digits specified by <time
fractionalseconds precision>.
 
       | TIMEZONE_HOUR    | -12 to 13                                     |       |                  |
                            |       |_TIMEZONE_MINUTE__|_-59_to_59_____________________________________|       |
         |                                               |           NOTE 62 - Datetime data types will allow dates in
theGregorian           format to be stored in the date range 0001-01-01 CE through           9999-12-31 CE. The range
forSECOND allows for as many as two           "leap seconds". Interval arithmetic that involves leap seconds
ordiscontinuities in calendars will produce implementation-           defined results.
 

The urban legend about needing 2 leap seconds in the same minute has
infected the standard I see.  It should only allow 60.9999 as the max
value for SECOND.

Note however that we feel free to exceed the spec in other aspects of
this --- we exceed their year range for instance.  So I don't think we
necessarily have to reject '24:00:00'.

Also, the spec explicitly states that arithmetic on TIME values is done
modulo 24 hours.  So it's correct for '23:59:59'::time + '1 second'::interval
to yield '00:00:00', but this does not necessarily mean that we should
cause rounding to behave that way.  Depends whether you think that
rounding is an arithmetic operation or not ...
        regards, tom lane