Re: roundoff problem in time datatype

["Jim C. Nasby" <jnasby(at)pervasive(dot)com>]

On Mon, Sep 26, 2005 at 06:23:06PM +0200, Andreas Pflug wrote:
> Tom Lane wrote:
> >Dennis Bjorklund <db(at)zigo(dot)dhs(dot)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(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461