#51 Precision lost from mxDateTime during queries

open-accepted
nobody
PgSQL (41)
5
2007-08-27
2003-09-05
Mike C. Fletcher
No

When working with PostgreSQL timestamps, round-trips
to/from Python wind up losing data (the
least-significant digits of the timestamp, 1000ths of
seconds and less). Result is that timestamps which
include 1000ths of seconds cannot be queried from
Python using mxDateTime values.

PyPgSQL should likely be using code something like the
following to format an mxDateTime value for a timestamp:

new.Format( '%Y-%m-%d %H:%M:%%s' )%( new.second, )

or

new.Format( '%Y-%m-%d %H:%M:%%r' )%( new.second, )

(difference being use of 'r' format, which will do as
much as possible to be sure that the resulting string
is capable of exactly producing the original format).

To illustrate:

from pytable import dbspecifier, sqlquery
spec = dbspecifier.DBSpecifier(
dbdriver='PyPgSQL',
database='cinemon',
host='localhost',
user='cinemon',
password='xxxx'
)
driver, connection = spec.connect()
((new,),) = sqlquery.SQLQuery(
sql = """SELECT MAX( archive_ts ) from log_modem;"""
)( connection )
print 'new', repr(new)
print 'seconds', new.second
records = sqlquery.SQLQuery(
sql = """SELECT * from log_modem where archive_ts =
%%(new)s;"""
)( connection, new=new ).fetchall()
print records # is an empty list because of truncation...

V:\cinemon>err_date_truncation.py
new <DateTime object for '2003-09-04 19:50:16.30' at
93a820>
seconds 16.297984
[]

Discussion

    • labels: --> PgSQL
    • assigned_to: nobody --> ghaering
    • status: open --> open-accepted
     
  • Logged In: YES
    user_id=163326

    Thanks. I'll try to integrate your proposal for 2.5.2.

    Sigh. Better late than never.

     
    • assigned_to: ghaering --> nobody