Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#282 DateTimeCol throws away microseconds

closed-fixed
Oleg Broytman
None
5
2013-12-08
2013-08-12
Geoffrey Wossum
No

DateTimeCol column types throw away microseconds when saving to database.

Setting datetimeFormat on DateTimeCol objects to '%Y-%m-%d %H:%M:%S.%f' doesn't actually make SQLObject save out the microsecond field. It will then give an exception about unexpected format when trying to load the row from the database.

OS: Ubuntu Linux 12.04.2 LTS (64-bit)
SQLObject 1.4.1
PySQLite2 2.6.3-2build1
SQLite 3.7.9-2ubuntu1
Python 2.7.3-0ubuntu2

Discussion

  • Oleg Broytman
    Oleg Broytman
    2013-09-04

    • assigned_to: Oleg Broytman
     
  • Oleg Broytman
    Oleg Broytman
    2013-09-04

    Date/Time columns return date and time types, not strings, and then a converter in converters.py converts them to a string in standard format. That, perhaps, could be fixed — if the user passed non-standard format Date/Time validators could be forced to return strings in that format.

    Not sure if it is possible to get microseconds back. If the backend returns a string in a standard format there is no way to recover absent data. I will experiment with different backends (SQLite/PySQLite and PostgreSQL/psycopg).

     
  • Oleg Broytman
    Oleg Broytman
    2013-10-09

    Experiments show both PostgreSQL/psycopg and SQLite/PySQLite accept and return microseconds. The following program:

    import psycopg2
    
    con = psycopg2.connect(database="test")
    cur = con.cursor()
    cur.execute("CREATE TABLE test (id integer primary key, value timestamp)")
    cur.execute('INSERT INTO test VALUES (%s,%s)', (1, '2000-01-01 21:44:33.123'))
    cur.execute('SELECT * FROM test ORDER BY id')
    for rec in cur.fetchall():
       print rec[0], type(rec[1]), rec[1],
    

    prints

    1 <type 'datetime.datetime'> 2000-01-01 21:44:33.123000
    

    and

    from pysqlite2 import dbapi2 as sqlite
    
    con = sqlite.connect(":memory:")
    cur = con.cursor()
    cur.execute("""CREATE TABLE test (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        value DATETIME)""")
    cur.execute('INSERT INTO test VALUES (?,?,?)', (1, '', '2000-01-01 21:44:33.123'))
    cur.execute('SELECT * FROM test ORDER BY id')
    for rec in cur.fetchall():
       print rec[0], type(rec[1]), repr(rec[1]), type(rec[2]), repr(rec[2]),
    

    outputs

    1 <type 'unicode'> u'' <type 'unicode'> u'2000-01-01 21:44:33.123'
    

    I will work to extend Date/Time validators to accept and return microseconds.

     
  • Oleg Broytman
    Oleg Broytman
    2013-10-16

    I have a patch that I'd like to ask you to test.

     
    Attachments
  • Oleg Broytman
    Oleg Broytman
    2013-12-08

    • status: open --> closed-fixed
     
  • Oleg Broytman
    Oleg Broytman
    2013-12-08

    Committed to the trunk in the revision 4678.