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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

mak-273 · Accepted Answer · 2018-03-14 16:18:56Z

0

ask user to enter date time: (ex, 'Jun 1 2005 1:33PM')

date_entry = input('Enter a date in YYYY-MM-DD h:m am/pm format')

convert it to date time object:

date1 = datetime.strptime(date_entry, '%b %d %Y %I:%M%p')

To inter to sql table:

insert table1 (dateField)
   values (convert(datetime,date1,5));

and if you again need to convert after reading from sql

datetime_object = datetime.strptime(dateField, '%b %d %Y %I:%M%p')

answered Mar 14, 2018 at 16:18

mak-273

621 silver badge7 bronze badges

1 Comment

Basj Over a year ago

How do you create the table? With your code, the datetime will be stored as string, and then you need to parse the string back, after a query, is that right? I think we can avoid date to string formatting, and then parsing with this solution.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related