How to insert a Python datetime object in a Sqlite database, and retrieve it as datetime object when querying?

Question

Why is the datetime type lost after inserting them in a Sqlite database?

import sqlite3, datetime
dbconn = sqlite3.connect(':memory:')
c = dbconn.cursor()
c.execute('create table mytable(title text, t timestamp)')

c.execute('insert into mytable (title, t) values (?, ?)', ("hello2", datetime.datetime(2018,3,10,12,12,00)))

c.execute("select * from mytable")
for a in c.fetchall(): 
    print a[0]            # hello2
    print type(a[0])      # <type 'unicode'>
    print a[1]            # 2018-03-10 12:12:00
    print type(a[1])      # <type 'unicode'>

Shouldn't the datetime type remain after an insertion and a query?

PS: I lost nearly one hour because of this problem, so I'll post the answer now with the "Answer your own question – share your knowledge, Q&A-style" SO feature.

Note: this is not a duplicate of this neighbour question because it doesn't deal about how datetimes are stored/retrieved.

Answer 1

According to this documentation, the solution is to use a detect_types parameter:

dbconn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)

Then the output of the previous code will be:

hello2
<type 'unicode'>
2018-03-10 12:12:00
<type 'datetime.datetime'>

Also this is an important note about datetime in Sqlite:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.