timestamp column in sqlite return string in python

Question

i create a table with SQLite Date Browse App ...

when i want retrieve datetime value from timestamp column , SQLite return unicod type ...

this is my insert code :

def Insert(self,mode,path,vname,stime,ftime):
        con = sqlite3.connect(PATH_DataBase)  # @UndefinedVariable
        con.execute('INSERT INTO SendList VALUES(?,?,?,?,?)',(mode,path,vname,stime,ftime))
        con.commit()
        con.close()

dt1 = datetime.datetime(2013,01,01,01,01,01,0)
dt2 = datetime.datetime(2015,01,01,01,01,01,0)
c = 0
    for f in os.listdir('/home/abbas/test/'):
        c += 1
        slist.Insert(common.MODE_Bluetooth_JAVA, '/home/abbas/test/'+f,'flower'+str(c) , dt1, dt2)

and now this is my table :

but when i want compare starttime with datetime.now() python give me error :

TypeError: can't compare datetime.datetime to unicode

Answer 1

"SQLite does not have a storage class set aside for storing dates and/or times." Reference: https://www.sqlite.org/datatype3.html

Python's sqlite3 module offers "default adapters for the date and datetime types in the datetime module." Reference: https://docs.python.org/2/library/sqlite3.html#default-adapters-and-converters

The only catch is that you must be sure to define the columns appropriately. Example DDL:

import sqlite3

con = sqlite3.connect(PATH_DataBase, detect_types=sqlite3.PARSE_DECLTYPES)
con.execute('''create table if not exists SendList (
                 cid primary key, 
                 mode text, 
                 path text,
                 vname text,
                 starttime timestamp, 
                 endtime timestamp);''')
con.commit()
con.close()

Any subsequent connections to insert or select data must pass sqlite3.PARSE_DECLTYPES as the value for the keyword argument (aka kwarg) detect_types. Example:

import datetime as dt

con = sqlite3.connect(PATH_DataBase, detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute('''select 
                 *
               from 
                 SendList
               where 
                 starttime between ? and ?
               limit 10;''',
            (dt.datetime(2013,1,1,0,0,0), dt.datetime(2014,12,31,23,59,59)))
results = cur.fetchall()

Answer 2

I had an similar problem, but my column type is DATETIME instead TIMESTAMP. To resolve, I need to use the detect_types = sqlite3.PARSE_DECLTYPES and the method sqlite3.register_converter.

My code to resolve this was:

import sqlite3
sqlite3.register_converter('DATETIME', sqlite3.converters['TIMESTAMP'])
conn = sqlite3.connect("/app/tmp/my_sqlite.db", detect_types=sqlite3.PARSE_DECLTYPES)
query = "SELECT * FROM table_test_2 WHERE dag_id='dag_1'"
cursor = conn.cursor()
cursor.execute(query)
cursor.fetchall()

Execution result:

I used the sqlite3.converters['TIMESTAMP'] because I wanted the same converter of TIMESTAMP type.