Re: roundoff problem in time datatype

Jochem van Dieten <jochemd(at)gmail(dot)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, but further |
constrained by the value of MONTH and YEAR
fields, according to 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
fractional seconds precision>.

| TIMEZONE_HOUR | -12 to 13 |
| | |
|_TIMEZONE_MINUTE__|_-59_to_59_____________________________________|
| | |
NOTE 62 - Datetime data types will allow dates in the Gregorian
format to be stored in the date range 0001-01-01 CE through
9999-12-31 CE. The range for SECOND allows for as many as two
"leap seconds". Interval arithmetic that involves leap seconds
or discontinuities 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