Best way to store python datetime.time in sqlite3 column?

Question

I am trying to replace my use of SAS with python + sqlite3; I am trying to move my data from SAS datasets to SQLite databases. I have many time fields that are correctly represented in python as datetime.time objects. Since SQLite is "lightly typed," I'm looking for advice on which format to use for storing time in columns. (I know that I need to write python adapters, etc., to read and write objects to and from a column). These are the functions that I need to consider:

• SQLite's ability to process a column in queries. (For example, can I select the lines that occur between two times?)
• The size of the field. (My tables often contain hundreds of millions of rows.)
• Humanity. (I consider saving time as an integer: microseconds from midnight, but this complicates the data search.)

Has anyone solved this problem to their satisfaction?

Answer 1

There is a general recipe for storing any serializable Python object in an sqlite table.

• Use sqlite3.register_adapter to register a function to convert a Python object to int, long, float, str (UTF-8 encoded), unicode, or buffer.
• Use sqlite3.register_converter to register a function to convert text to a Python object. The input is always text because internally, sqlite saves everything as text.

Here's what the code for datetime.time objects looks like:

 import sqlite3 import datetime as DT def adapt_timeobj(timeobj): return ((3600*timeobj.hour + 60*timeobj.minute + timeobj.second)*10**6 + timeobj.microsecond) def convert_timeobj(val): val = int(val) hour, val = divmod(val, 3600*10**6) minute, val = divmod(val, 60*10**6) second, val = divmod(val, 10**6) microsecond = int(val) return DT.time(hour, minute, second, microsecond) # Converts DT.time to TEXT when inserting sqlite3.register_adapter(DT.time, adapt_timeobj) # Converts TEXT to DT.time when selecting sqlite3.register_converter("timeobj", convert_timeobj) con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES) cur = con.cursor() # declare timecol to be of type timeobj cur.execute("create table test (timecol timeobj)") cur.executemany("insert into test (timecol) values (?)", [(DT.time(1,2,3,4), ), (DT.time(5,6,7,8),) ]) 

You can use inequalities in SQL, but note that the compared values ​​are those returned by adapt_timeobj objects, not datetime.time . Fortunately, if the adapt_timeobj function returns integers that are ordered in the same order as the corresponding datetime.time objects (as they do above), then the inequalities in SQL will work as desired.

 cur.execute("select timecol from test where timecol < ?", [DT.time(4,5,6)]) print(cur.fetchall()) # [(datetime.time(1, 2, 3, 4),)] cur.execute("select timecol from test where timecol < ?", [DT.time(8,0,0)]) print(cur.fetchall()) # [(datetime.time(1, 2, 3, 4),), (datetime.time(5, 6, 7, 8),)] con.commit() cur.close() con.close() 

Note. If you look in the change history, you will see a simpler alternative for adapt_timeobj and convert_timeobj , which stores data as str , and not as int . It is simpler, but saving data as an int is faster and more efficient.

Answer 2

I really like @unutbu's answer , but here is an easy way to keep the timestamp.

RFC 3339 is a very unambiguous time stamp format that is easy to analyze and easy for people to read. You can store timestamps as strings.

One nice feature of RFC 3339: Simple ASCII sorting is also sorted in chronological order.

But you do not need a specification because it is so simple. Here is an example:

 2014-12-24T23:59:59.9999-08:00 

This is the last split second before Christmas in my time zone, which is 8 hours less than UTC (thus, part of -08:00 ). Year, month, date, line T , hour, minute, seconds, optional fractional second, time zone.

The time zone may also be Z , which indicates the UTC time. But it is probably more convenient to store time in your local time zone so that you can read them more easily.