Обсуждение: roundoff problem in time datatype
Inserting into a time field with limited precision rounds off, which
is good except for this case:
regression=# select '23:59:59.9'::time(0); time
----------24:00:00
(1 row)
This is bad because:
regression=# select '24:00:00'::time(0);
ERROR: date/time field value out of range: "24:00:00"
which means that data originally accepted will fail to dump and reload.
I see this behavior in all versions back to 7.3. 7.2 was even more
broken:
regression=# select '23:59:59.9'::time(0); time
----------00:00:00
(1 row)
I think the correct behavior has to be to check for overflow again
after rounding off. Alternatively: why are we forbidding the value
24:00:00 anyway? Is there a reason not to allow the hours field
to exceed 23?
regards, tom lane
On Sun, 25 Sep 2005, Tom Lane wrote: > Alternatively: why are we forbidding the value 24:00:00 anyway? Is > there a reason not to allow the hours field to exceed 23? One reason is because it's what the standard demand. Another is that it isn't a proper time, just like feb 31 isn't a proper date. -- /Dennis Björklund
Actually, I think there is a case where 24:00 is a proper time. Isn't it used for adding leap seconds ? Dave On 26-Sep-05, at 3:39 AM, Dennis Bjorklund wrote: > On Sun, 25 Sep 2005, Tom Lane wrote: > > >> Alternatively: why are we forbidding the value 24:00:00 anyway? Is >> there a reason not to allow the hours field to exceed 23? >> > > One reason is because it's what the standard demand. Another is > that it > isn't a proper time, just like feb 31 isn't a proper date. > > -- > /Dennis Björklund > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly > >
Dave Cramer <pg@fastcrypt.com> writes:
> Actually, I think there is a case where 24:00 is a proper time. Isn't
> it used for adding leap seconds ?
No, I think the usual notation for a leap-second is '23:59:60'.
We do allow 60 in the seconds field for this purpose.
I suppose there's another possible approach, which is to special-case
the output of this value to look like '23:59:60' instead of '24:00:00'.
Then it could be reloaded. On the whole though, most people who came
across that behavior would probably think it's a bug...
regards, tom lane
On Mon, 26 Sep 2005, Tom Lane wrote: > > Actually, I think there is a case where 24:00 is a proper time. Isn't > > it used for adding leap seconds ? > > No, I think the usual notation for a leap-second is '23:59:60'. > We do allow 60 in the seconds field for this purpose. Yes, and it can go up to 23:59:60.999999 (depending on how many fractional seconds one want). > I suppose there's another possible approach, which is to special-case > the output of this value to look like '23:59:60' instead of '24:00:00'. You would get the same problem with 23:59:60.9 which I guess you want to round up. One "solution" is to round '23:59:59.9'::time(0) up to '00:00:00'. That is normally the next following time value after all. I know why you might not want to round it "up" to 00:00:00, but it's one logical solution. By the way, here is another example of the same problem: # SELECT time '23:59:59.9' + interval '0.1';?column? ----------24:00:00 # SELECT time '23:59:59.9' + interval '0.11'; ?column? -------------00:00:00.01 (1 rad) -- /Dennis Björklund
Dennis Bjorklund <db@zigo.dhs.org> writes:
> On Mon, 26 Sep 2005, Tom Lane wrote:
>> No, I think the usual notation for a leap-second is '23:59:60'.
>> We do allow 60 in the seconds field for this purpose.
> Yes, and it can go up to 23:59:60.999999 (depending on how many fractional
> seconds one want).
That's an urban legend. There never have been, and never will be, two
leap seconds instituted in the same minute. We really should reject
anything larger than '23:59:60'.
> One "solution" is to round '23:59:59.9'::time(0) up to '00:00:00'.
7.2 did that, and we concluded it was broken.
regards, tom lane
On Mon, 26 Sep 2005, Tom Lane wrote: > > Yes, and it can go up to 23:59:60.999999 (depending on how many fractional > > seconds one want). > > That's an urban legend. There never have been, and never will be, two > leap seconds instituted in the same minute. We really should reject > anything larger than '23:59:60'. The above is still just one leap second. The time continues to tick until it wraps over to 00:00:00. So for example a time value of 23:59:60.42 exists if we allow just one leap second. > > One "solution" is to round '23:59:59.9'::time(0) up to '00:00:00'. > > 7.2 did that, and we concluded it was broken. Doesn't mean that it necissary was a correct conclusion (and I'm not stating that it was wrong, I would like to think about it for a while before I claim something like that). Do the sql standard say anything on the matter? -- /Dennis Björklund
Tom Lane <tgl@sss.pgh.pa.us> writes: > That's an urban legend. There never have been, and never will be, two > leap seconds instituted in the same minute. We really should reject > anything larger than '23:59:60'. I don't understand. The last second of a normal minute goes from 59.0 to 59.999 (etc) before the next minute begins. So surely the last second of a minute containing a leap second goes from 60.0 to 60.999? -- greg
On 9/26/05, Dennis Bjorklund wrote: > On Sun, 25 Sep 2005, Tom Lane wrote: >> >> Alternatively: why are we forbidding the value 24:00:00 anyway? Is >> there a reason not to allow the hours field to exceed 23? > > 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. > Another is that it > isn't a proper time, just like feb 31 isn't a proper date. IIRC ISO 8601 (to whcih the SQL standard points) says 2005-10-01T24:00:00 is valid (and happens to be the same as 2005-10-02T00:00:00). It does seem a bit inconsistent with the spec of an interval though. Jochem
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
Dennis Bjorklund <db@zigo.dhs.org> writes:
> Do the sql standard say anything on the matter?
It doesn't seem very helpful. AFAICS, we should interpret storing
'23:59:59.99' into a TIME(0) field as a cast from TIME(2) to TIME(0),
and the spec defines that as
15) If TD is the datetime data type TIME WITHOUT TIME ZONE, then let TSP be the <time precision> of
TD.
b) If SD is TIME WITHOUT TIME ZONE, then TV is SV, with implementation-defined rounding or
truncationif necessary.
So it's "implementation-defined" what we do.
regards, tom lane
Tom Lane wrote: > Dennis Bjorklund <db@zigo.dhs.org> writes: >> On Mon, 26 Sep 2005, Tom Lane wrote: >>> No, I think the usual notation for a leap-second is '23:59:60'. >>> We do allow 60 in the seconds field for this purpose. > >> Yes, and it can go up to 23:59:60.999999 (depending on how many fractional >> seconds one want). > > That's an urban legend. There never have been, and never will be, two > leap seconds instituted in the same minute. We really should reject > anything larger than '23:59:60'. mmm. The second "60" have is on duration of 1 second so 23:59:60.4 have is right to exist. Regards Gaetano Mendola
On Mon, 26 Sep 2005, Tom Lane wrote: > b) If SD is TIME WITHOUT TIME ZONE, then TV is SV, with > implementation-defined rounding or truncation if necessary. > > So it's "implementation-defined" what we do. Truncation would avoid the problem but of course loses some of the info. So, what are the alternatives: * Truncation. * Rounding and let it wrap when rounding up towards midnight. * Rounding and never let it wrap. The cases that would wrap goes to 23:59:59 (or 23:59:59.9 and so on for other precisions)or to 23:59:60 (or 23:59.60.9 and so on) if one start with a leap second time. Are there any more viable cases? -- /Dennis Björklund
Tom Lane wrote: > Dennis Bjorklund <db@zigo.dhs.org> writes: > >>Do the sql standard say anything on the matter? > > > It doesn't seem very helpful. AFAICS, we should interpret storing > '23:59:59.99' into a TIME(0) field as a cast from TIME(2) to TIME(0), > and the spec defines that as > > 15) If TD is the datetime data type TIME WITHOUT TIME ZONE, then let > TSP be the <time precision> of TD. > > b) If SD is TIME WITHOUT TIME ZONE, then TV is SV, with > implementation-defined rounding or truncation if necessary. > > So it's "implementation-defined" what we do. IMHO Since 23:59:59.99 probably means "the last milliseconds of this day, as far as precision allows to express it", this should be truncated to 23:59:59, not rounded to 24:00:00. Until the last microsecond has elapsed, it's not 24 hours (you wouldn't round "happy new year" at 23:59:30 from a clock with minutes only either) Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Tom Lane wrote:
>> b) If SD is TIME WITHOUT TIME ZONE, then TV is SV, with
>> implementation-defined rounding or truncation if necessary.
>>
>> So it's "implementation-defined" what we do.
> IMHO Since 23:59:59.99 probably means "the last milliseconds of this
> day, as far as precision allows to express it", this should be truncated
> to 23:59:59, not rounded to 24:00:00. Until the last microsecond has
> elapsed, it's not 24 hours (you wouldn't round "happy new year" at
> 23:59:30 from a clock with minutes only either)
Hm, so the proposal is "round unless that would produce 24:00:00, in
which case truncate"? Seems a bit ugly but it would follow the letter
of the spec, and avoid rejecting inputs that we used to accept. It's
still not very clear what to do with '23:59:60.9' though.
regards, tom lane
Tom Lane wrote: > > Hm, so the proposal is "round unless that would produce 24:00:00, in > which case truncate"? Seems a bit ugly but it would follow the letter > of the spec, and avoid rejecting inputs that we used to accept. It's > still not very clear what to do with '23:59:60.9' though. I'd handle it the same; 23.59.60.9 -> 23.59.60 since this is apparently a leap second. A normal second should never become a leap second from some conversion, but a leap second should stay one. Regards, Andreas
On Mon, Sep 26, 2005 at 06:23:06PM +0200, Andreas Pflug wrote: > Tom Lane wrote: > >Dennis Bjorklund <db@zigo.dhs.org> writes: > > > >>Do the sql standard say anything on the matter? > > > > > >It doesn't seem very helpful. AFAICS, we should interpret storing > >'23:59:59.99' into a TIME(0) field as a cast from TIME(2) to TIME(0), > >and the spec defines that as > > > > 15) If TD is the datetime data type TIME WITHOUT TIME ZONE, then > > let > > TSP be the <time precision> of TD. > > > > b) If SD is TIME WITHOUT TIME ZONE, then TV is SV, with > > implementation-defined rounding or truncation if necessary. > > > >So it's "implementation-defined" what we do. > > IMHO Since 23:59:59.99 probably means "the last milliseconds of this > day, as far as precision allows to express it", this should be truncated > to 23:59:59, not rounded to 24:00:00. Until the last microsecond has > elapsed, it's not 24 hours (you wouldn't round "happy new year" at > 23:59:30 from a clock with minutes only either) Maybe also allow for a warning to be generated? Or some way to signal an overflow? I think it could be valid to do this, or round up to 24:00:00 or 'round up' to 00:00:00, depending on what the app was trying to accomplish. Would it be possible to allow an option to the datatype that specifies the rounding behavior, or would they need to be different datatypes? -- 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
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
Where are we on this? I see current CVS behaving the same as below, except the last query now returns 24:00:00. --------------------------------------------------------------------------- Tom Lane wrote: > Inserting into a time field with limited precision rounds off, which > is good except for this case: > > regression=# select '23:59:59.9'::time(0); > time > ---------- > 24:00:00 > (1 row) > > This is bad because: > > regression=# select '24:00:00'::time(0); > ERROR: date/time field value out of range: "24:00:00" > > which means that data originally accepted will fail to dump and reload. > > I see this behavior in all versions back to 7.3. 7.2 was even more > broken: > > regression=# select '23:59:59.9'::time(0); > time > ---------- > 00:00:00 > (1 row) > > I think the correct behavior has to be to check for overflow again > after rounding off. Alternatively: why are we forbidding the value > 24:00:00 anyway? Is there a reason not to allow the hours field > to exceed 23? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Where are we on this?
We haven't decided what to do.
I think my preference is to allow '24:00:00' (but not anything larger)
as a valid input value of the time datatypes. This for two reasons:* existing dump files may contain such values* it's
consistentwith allowing, eg, '12:13:60', which we allow even though it's certainly not a valid leap second.
The alternative is to try to catch all places where 23:59:59.something
could get rounded up to 24:00:00, but that looks messy, and it would
introduce a gotcha into calculations on time values.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Where are we on this? > > We haven't decided what to do. > > I think my preference is to allow '24:00:00' (but not anything larger) > as a valid input value of the time datatypes. This for two reasons: > * existing dump files may contain such values > * it's consistent with allowing, eg, '12:13:60', which we > allow even though it's certainly not a valid leap second. > > The alternative is to try to catch all places where 23:59:59.something > could get rounded up to 24:00:00, but that looks messy, and it would > introduce a gotcha into calculations on time values. Is this a must-fix for 8.1? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> I think my preference is to allow '24:00:00' (but not anything larger)
>> as a valid input value of the time datatypes.
> Is this a must-fix for 8.1?
No, since it's a pre-existing issue, but it's the kind of thing that
should be changed during a major release not a point-release. If we
don't change it then I think we'd have to wait till 8.2 before doing
anything about it.
regards, tom lane
Tom, > I think my preference is to allow '24:00:00' (but not anything larger) > as a valid input value of the time datatypes. This for two reasons: > * existing dump files may contain such values > * it's consistent with allowing, eg, '12:13:60', which we > allow even though it's certainly not a valid leap second. It's also consistent with how several other RDBMSes do things (SQL Server, MySQL), and several programming languages. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On 10/13/05, Josh Berkus <josh@agliodbs.com> wrote: > Tom, > > > I think my preference is to allow '24:00:00' (but not anything larger) > > as a valid input value of the time datatypes. This for two reasons: > > * existing dump files may contain such values > > * it's consistent with allowing, eg, '12:13:60', which we > > allow even though it's certainly not a valid leap second. we shouldn't be allowing such timestamps! We should enforce only the canonical formats of any datatype. Imagine what chaos would have been caused if we didn't have IEEE specifications for the floating point numbers!!! > > It's also consistent with how several other RDBMSes do things (SQL Server, > MySQL), and several programming languages. Just wanted to note that this is not really consistent with other databases. For eg. SQL Server's o/p is shown below. select convert( datetime, '23:59:59.998' ) 1900-01-01 23:59:59.997 select convert( datetime, '23:59:59.999' ) 1900-01-02 00:00:00.000 /* the date changes but the time remains under 24:00:00 */ select convert( datetime, '24:00:00' ) Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Moreover, 24:00:00 not in canonical format so it should not be encoraged at all. Gujreet.