9th May, 2008

Trouble Getting a Date

Filed under: database,python — admin @ 8:04 am

I’m having trouble with dates. This can be summed up in a couple of high level issues;

1. Date support in relational databases is insane, or at the best inconsistent.

As far as I can tell the ANSI SQL-92 standard defines date, time, interval and timestamp data types. Which doesn’t help when SQL Server only implements something called ‘datetime’ – at least I think so, have you tried accessing any sort of manual for a Microsoft product online? Blimey, I thought billg had embraced this web thing years ago. Oracle has the ‘date’ data type (which is actually a time stamp) and MySQL, well they’ve gone and outdone everyone by implementing DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

2. The Python DB-API does not cope with date data type ambiguity well.

When it comes to the date question the Python DB-API states (and I quote) ” … may use mx.DateTime”, which if you ask me isn’t much of a standard. This needs to change so that all DB-API modules return consistent datetime objects, not such a big issue as datetime has been part of the standard library since, what, Python 2.3?

Sadly even if we fix this it won’t work with Sqlite as it doesn’t consistently support data typing. In my experiments regardless of what sort of date you insert into the database you get a unicode string back. Don’t believe me? Try this in Python 2.5;

>>> from sqlite3 import dbapi2 
>>> db = dbapi2.connect('test_db')
>>> cursor = db.cursor()
>>> cursor.execute('create table date_test (id integer not null primary key autoincrement, sample_date DATE NOT NULL)'
>>> stmt = "INSERT INTO date_test (sample_date) VALUES (?)"
>>> cursor.execute(stmt, (1234, ))
>>> import datetime
>>> cursor.execute(stmt, (datetime.date(2008, 3, 10), ))
>>> cursor.execute(stmt, ('My name is Earl', ))
>>> db.commit()
>>> cursor.execute("SELECT * FROM date_test")
>>> results = cursor.fetchall()
>>> for item in results:
...     print item[1], type(item[1])
1234 
2008-03-10 
My name is Earl 
>>>

But note that it is fine for integers.

3. The people writing the Python standard library modules are on crack.

Outside of the database world and within the batteries included Python standard library some modules use datetime, others time and there are even uses of calendar.

O.K. I’ll accept that maybe the module authors aren’t on full strength crack, because the time module just exposes underlying posix functions. But the people who wrote those were on something strong and hallucinogenic. I table the following function signatures from section 14.2 of the Python Library Reference 2.5 as an example;

strftime(format[, t ]) 
strptime(string[, format ])

This has bitten me twice in the last twenty four hours and frankly I’m not happy.

I appreciate that there are historical reasons for having inconsistent function signatures but can someone please fix this in Python 3.0. All we need is a single module that can access the underlying system clock and then convert between a number of different representations of that and other epoch driven dates. How hard can it be? As far as I can tell this is not part of the proposed standard library re-organisation. I think it should be.

28 Comments

  1. SQLite’s lack of strict typing is on purpose. I think the python module tries to be smart about what it returns, ints for ints, floats for floats, unicode strings for everything else.

    Also, why dont you just use a unix timestamp and store it as an integer rather than trying to deal with shotty implementations of a special “Date” type?

    Comment by Gryc Ueusp — 09/05/2008 @ 10:21 am

  2. SQLite’s lack of strict typing is on purpose. I think the python module tries to be smart about what it returns, ints for ints, floats for floats, unicode strings for everything else.

    Also, why dont you just use a unix timestamp and store it as an integer rather than trying to deal with shotty implementations of a special “Date” type?

    Comment by Gryc Ueusp — 09/05/2008 @ 10:21 am

  3. Why don’t I use a Unix timestamp? Do I look like a C programmer? The programming language of my choice has a wide variety of date data types and I want to take advantage of them. I would like a more sensible mapping between my programming language of choice and the data persistence platforms I use.

    Alternatively, I could take a least common denominator approach and stop using complex data types. I mean, why should I use strings, after all they are just lists of characters, right?

    Comment by Andy Todd — 09/05/2008 @ 10:38 am

  4. Why don’t I use a Unix timestamp? Do I look like a C programmer? The programming language of my choice has a wide variety of date data types and I want to take advantage of them. I would like a more sensible mapping between my programming language of choice and the data persistence platforms I use.

    Alternatively, I could take a least common denominator approach and stop using complex data types. I mean, why should I use strings, after all they are just lists of characters, right?

    Comment by Andy Todd — 09/05/2008 @ 10:38 am

  5. Try maintaining an astronomy library in Python. :-) Unfortunately, all of Python’s time types are more or less toys — none of them seem to make serious effort to even understand real time zones, much less things like conversion from timescales that use leapseconds (UT) to ones that do not in order to provide for uniform computation (ET).

    Comment by Brandon Rhodes — 09/05/2008 @ 12:57 pm

  6. Try maintaining an astronomy library in Python. :-) Unfortunately, all of Python’s time types are more or less toys — none of them seem to make serious effort to even understand real time zones, much less things like conversion from timescales that use leapseconds (UT) to ones that do not in order to provide for uniform computation (ET).

    Comment by Brandon Rhodes — 09/05/2008 @ 12:57 pm

  7. I must say I don’t share your opinion. My experience is limited to oracle (cx_Oracle) and sqlite (sqlite3) and both seems transparent to me. I send a datetime object and I get a datetime object.
    Regarding sqlite3, did you try the adapters/converts it has?

    Example (edited from IPython):

    import sqlite3 as sql
    import datetime as d
    con = sql.connect(‘:memory:’, detect_types=sql.PARSE_DECLTYPES)
    cur = con.cursor()
    cur.execute(‘create table date_test (sample_date timestamp)’)
    cur.execute(‘insert into date_test values (?)’, (d.datetime.now(),))
    cur.execute(‘select * from date_test’)
    r = cur.fetchone()
    print type(r[0])

    print r
    (datetime.datetime(2008, 5, 9, 0, 30, 22, 782901),)

    Comment by Mariano — 09/05/2008 @ 1:34 pm

  8. I must say I don’t share your opinion. My experience is limited to oracle (cx_Oracle) and sqlite (sqlite3) and both seems transparent to me. I send a datetime object and I get a datetime object.
    Regarding sqlite3, did you try the adapters/converts it has?

    Example (edited from IPython):

    import sqlite3 as sql
    import datetime as d
    con = sql.connect(‘:memory:’, detect_types=sql.PARSE_DECLTYPES)
    cur = con.cursor()
    cur.execute(‘create table date_test (sample_date timestamp)’)
    cur.execute(‘insert into date_test values (?)’, (d.datetime.now(),))
    cur.execute(‘select * from date_test’)
    r = cur.fetchone()
    print type(r[0])

    print r
    (datetime.datetime(2008, 5, 9, 0, 30, 22, 782901),)

    Comment by Mariano — 09/05/2008 @ 1:34 pm

  9. Sorry, I missed the output from print type(r[0]):

    type(r[0])

    Comment by Mariano — 09/05/2008 @ 1:39 pm

  10. Sorry, I missed the output from print type(r[0]):

    type(r[0])

    Comment by Mariano — 09/05/2008 @ 1:39 pm

  11. Umm it seems it doesn’t get print, anyway give it a try :)

    Comment by Mariano — 09/05/2008 @ 1:41 pm

  12. Umm it seems it doesn’t get print, anyway give it a try :)

    Comment by Mariano — 09/05/2008 @ 1:41 pm

  13. Mariano, I didn’t know about PARSE_DECLTYPES so I added it to my sample code above, and got a nice error message when I tried to retrieve my data;

    n [17]: curs.execute("SELECT * FROM date_test")
    ---------------------------------------------------------------------------
                 Traceback (most recent call last)
    
    ~/Work/Python/ in ()
    
    /System/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/sqlite3/dbapi2.py in convert_date(val)
         61
         62     def convert_date(val):
    ---> 63         return datetime.date(*map(int, val.split("-")))
         64
         65     def convert_timestamp(val):
    

    Comment by Andy Todd — 09/05/2008 @ 3:53 pm

  14. Mariano, I didn’t know about PARSE_DECLTYPES so I added it to my sample code above, and got a nice error message when I tried to retrieve my data;

    n [17]: curs.execute("SELECT * FROM date_test")
    ---------------------------------------------------------------------------
                 Traceback (most recent call last)
    
    ~/Work/Python/ in ()
    
    /System/Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/sqlite3/dbapi2.py in convert_date(val)
         61
         62     def convert_date(val):
    ---> 63         return datetime.date(*map(int, val.split("-")))
         64
         65     def convert_timestamp(val):
    

    Comment by Andy Todd — 09/05/2008 @ 3:53 pm

  15. In addition to DATE, Oracle has had TIMESTAMP and INTERVAL since 9i.
    Doesn’t make things any easier for you though.

    Comment by Dominic Brooks — 09/05/2008 @ 6:14 pm

  16. In addition to DATE, Oracle has had TIMESTAMP and INTERVAL since 9i.
    Doesn’t make things any easier for you though.

    Comment by Dominic Brooks — 09/05/2008 @ 6:14 pm

  17. Your blog is not escaping less-than and greater-than characters, so browsers interpret them as strange malformed HTML elements named “type”.

    Comment by Marius Gedminas — 09/05/2008 @ 8:11 pm

  18. Your blog is not escaping less-than and greater-than characters, so browsers interpret them as strange malformed HTML elements named “type”.

    Comment by Marius Gedminas — 09/05/2008 @ 8:11 pm

  19. Leave stftime, strptime as they are, any coming from C (or indeed php) expects them to work that way.

    The datetime library is great, none of the dbapi drivers I know use mx.DateTime anymore. Add pytz to get timezone support.

    If you are working with relational databases, then give SQLAlchemy a try.

    Comment by Laurence Rowe — 10/05/2008 @ 12:08 am

  20. Leave stftime, strptime as they are, any coming from C (or indeed php) expects them to work that way.

    The datetime library is great, none of the dbapi drivers I know use mx.DateTime anymore. Add pytz to get timezone support.

    If you are working with relational databases, then give SQLAlchemy a try.

    Comment by Laurence Rowe — 10/05/2008 @ 12:08 am

  21. Andy, you need to use “date” or “timestamp” (I think it matters if you write it lowercase or uppercase) as the sqlite’s datatype to make it work (this comes by default in sqlite3 but you can write an adapter/converter to build whatever datatype you want to store).

    See sqlite3′s doc for more on this: http://docs.python.org/lib/node348.html

    Comment by Mariano — 10/05/2008 @ 12:50 am

  22. Andy, you need to use “date” or “timestamp” (I think it matters if you write it lowercase or uppercase) as the sqlite’s datatype to make it work (this comes by default in sqlite3 but you can write an adapter/converter to build whatever datatype you want to store).

    See sqlite3′s doc for more on this: http://docs.python.org/lib/node348.html

    Comment by Mariano — 10/05/2008 @ 12:50 am

  23. Just to make things a little more confusing, SQL Server has a timestamp data type… but it doesn’t even remotely conform to ANSI/ISO standards. Instead, it’s a synonym for rowversion.

    And to confirm, SQL Server only provides datetime (and smalldatetime, half the size but less precision).

    Comment by Ira — 10/05/2008 @ 2:37 am

  24. Just to make things a little more confusing, SQL Server has a timestamp data type… but it doesn’t even remotely conform to ANSI/ISO standards. Instead, it’s a synonym for rowversion.

    And to confirm, SQL Server only provides datetime (and smalldatetime, half the size but less precision).

    Comment by Ira — 10/05/2008 @ 2:37 am

  25. PostgreSQL has nice “timestamp”, “interval”, “date” and “time” datatypes, and decent functions for date math/manipulation (up to microsecond resolution). I usually do most of my date math/formating inside my postgresql queries for this reason and just have it spit out text for python. Kind of sad in a way, but better than some of your frustration above for my uses.

    Comment by T Middleton — 10/05/2008 @ 4:28 am

  26. PostgreSQL has nice “timestamp”, “interval”, “date” and “time” datatypes, and decent functions for date math/manipulation (up to microsecond resolution). I usually do most of my date math/formating inside my postgresql queries for this reason and just have it spit out text for python. Kind of sad in a way, but better than some of your frustration above for my uses.

    Comment by T Middleton — 10/05/2008 @ 4:28 am

  27. Glad to know I am not the only one who always trips over this, but I always figured it is because dates are so rarely used in real world programming ;-) One of the few ugly warts in Python I don’t like to discuss with the unbelievers. Absolutely this mess needs to be cleaned up, preferably in a new class that consolidates the intended functionality of all of the different standard modules. “Preferably one way to do things”, no?

    Comment by David Welden — 11/05/2008 @ 6:22 am

  28. Glad to know I am not the only one who always trips over this, but I always figured it is because dates are so rarely used in real world programming ;-) One of the few ugly warts in Python I don’t like to discuss with the unbelievers. Absolutely this mess needs to be cleaned up, preferably in a new class that consolidates the intended functionality of all of the different standard modules. “Preferably one way to do things”, no?

    Comment by David Welden — 11/05/2008 @ 6:22 am

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress