[egenix-users] DateTime error rounding seconds

[Steve Holden]

----- Original Message -----
From: "Edwin Grubbs" <egrubbs at rackspace.com>
To: <egenix-users at lists.egenix.com>
Sent: Tuesday, April 02, 2002 3:26 PM
Subject: Re: [egenix-users] DateTime error rounding seconds


> On Tue, 2 Apr 2002, M.-A. Lemburg wrote:
>
> > Edwin Grubbs wrote:
> > >
> > > I am having a problem with DateTime incorrectly rounding the floating
> > > point second value down 1/100 of second.
> > >
> > > Python 2.2 (#1, Mar 13 2002, 12:34:11)
> > > [GCC 2.96 20000731 (Red Hat Linux 7.1 2.96-98)] on linux2
> > > Type "help", "copyright", "credits" or "license" for more information.
> > > >>> from mx.DateTime import *
> > > >>> DateTime(2002, 1, 2, 3, 4, 5.00)
> > > <DateTime object for '2002-01-02 03:04:05.00' at 8126658>
> > > >>> DateTime(2002, 1, 2, 3, 4, 5.01)
> > > <DateTime object for '2002-01-02 03:04:05.00' at 8152098>
> > > >>> DateTime(2002, 1, 2, 3, 4, 5.02)
> > > <DateTime object for '2002-01-02 03:04:05.01' at 8126658>
> > > >>> DateTime(2002, 1, 2, 3, 4, 5.03)
> > > <DateTime object for '2002-01-02 03:04:05.03' at 8152098>
> > > >>> DateTime(2002, 1, 2, 3, 4, 5.04)
> > > <DateTime object for '2002-01-02 03:04:05.04' at 8126658>
> > > >>> DateTime(2002, 1, 2, 3, 4, 5.05)
> > > <DateTime object for '2002-01-02 03:04:05.04' at 8152098>
> > > >>> DateTime(2002, 1, 2, 3, 4, 5.06)
> > > <DateTime object for '2002-01-02 03:04:05.05' at 8126658>
> >
> > That's because 5.05 is really 5.0499999999999998. mxDateTime's
> > repr() function truncates the seconds value to avoid accidental
> > cases of a 60 reading when you really only have 59.6 seconds.
> >
> > Use now().second to access the true seconds value with
> > full accuracy.
> >
> > HTH,
> >
>
> The real problem with it, is that str() returns the truncated date just
> like repr(), and we are using str() to insert datetime fields. If you
> create a DateTime object for 03:04:05.02 and insert it into a Postgres 7
> database using its str() value, it is stored as 03:04:05.01. Then when you
> retrieve 03:04:05.01 from the database, the DateTime object shows it as
> 03:04:05.00. Then you cannot match the row that you just retrieved with
> its datetime column. How is the DateTime object supposed to be inserted
> and retrieved from the database?
>
Isn't the *real* real problem (;-) i that you are using character fields to
store date values? Normally one is supposed to use a timestamp or datetime
field to store these values (if you store them as strings then equality is
the only sensible test you can make. As datetime values you can also use <,
>, BETWEEN, etc.). However, even this solution sometimes has pitfalls -
stroing datetimes in either Access of SQL Server (I think the latter, but
it's a long time ago) raies warnings abour field truncation, which you have
to cure by removing any fractional seconds before storage.

> I understand that a float cannot represent most decimal values exactly,
> but truncating just increases the inaccuracy even though it might seem
> more coherent to have "2002-03-28 10:59:59.9999" truncated to "2002-03-28
> 10:59:59.99" instead of rounded to "2002-03-28 11:00:00.00".
>
Just as one asks for trouble by treating floats as exact, it's problematic
to store date/time values as strings. If you DBMS has no date/time type,
consider converting the dates to an appropriate numeric format.

regards
 Steve