[Pypgsql-devel] [ pypgsql-Bugs-800801 ] Precision lost from mxDateTime during queries

[SourceForge.net <no...@so...>]

Bugs item #800801, was opened at 2003-09-04 20:17
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=800801&group_id=16528

Category: None
Group: None
Status: Open
Resolution: None
Priority: 5
Submitted By: Mike C. Fletcher (mcfletch)
Assigned to: Nobody/Anonymous (nobody)
Summary: Precision lost from mxDateTime during queries

Initial Comment:
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

[]

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=800801&group_id=16528