Re: roundoff problem in time datatype

On Mon, Sep 26, 2005 at 11:46:47AM -0400, Tom Lane wrote:
> 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, 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 ...

Does that portion of the spec also apply to plain time fields? The
entire issue here only exists because there's no method to handle the
overflow, unlike in a timestamp.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461