4

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.

Basj
  • 41,386
  • 99
  • 383
  • 673

2 Answers2

2

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.

Community
  • 1
  • 1
Basj
  • 41,386
  • 99
  • 383
  • 673
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')
mak-273
  • 62
  • 1
  • 7
  • 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](https://stackoverflow.com/a/49282200/1422096). – Basj Mar 14 '18 at 16:25