Storing times in Python - Best format?

Question

When storing a time in Python (in my case in ZODB, but applies to any DB), what format (epoch, datetime etc) do you use and why?

Answer 1

The datetime module has the standard types for modern Python handling of dates and times, and I use it because I like standards (I also think it's well designed); I typically also have timezone information via pytz.

Most DBs have their own standard way of storing dates and times, of course, but modern Python adapters to/from the DBs typically support datetime (another good reason to use it;-) on the Python side of things -- for example that's what I get with Google App Engine's storage, Python's own embedded SQLite, and so on.

Answer 2

If the database has a native date-time format, I try to use that even if it involves encoding and decoding. Even if this is not 100% standard such as SQLITE, I would still use the date and time adaptors described near the bottom of the SQLITE3 help page.

In all other cases I would use ISO 8601 format unless it was a Python object database that stores some kind of binary encoding of the object.

ISO 8601 format is sortable and that is often required in databases for indexing. Also, it is unambiguous so you know that 2009-01-12 was in January, not in December. The people who change the position of month and day, always put the year last, so putting it first stops people from automatically assuming an incorrect format.

Of course, you can reformat however you want for display and input in your applications but data in databases is often viewed with other tools, not your application.

Answer 3

Seconds since epoch is the most compact and portable format for storing time data. Native DATETIME format in MySQL, for example, takes 8 bytes instead of 4 for TIMESTAMP (seconds since epoch). You'd also avoid timezone issues if you need to get the time from clients in multiple geographic locations. Logical operations (for sorting, etc.) are also fastest on integers.