How to write date and time from string to Sqlite table?

Question

I have date and time in one-string format "2016-03-28T20:23:46+0800". How to convert it to date-time format Sqlite3 supports in "datetime" domain? I'm using python to read/write data in database.

I know that it's possible to achive using python:

>>> str = "2016-03-28T20:23:46+0800"
>>> temp = str.split('T')
>>> temp[1] = temp[1].rstrip('+')
>>> temp[1]
'20:23:46+0800'
>>> temp[1] = temp[1].split('+')[0]
>>> result = " ".join(temp)
>>> result
'2016-03-28 20:23:46'

but maybe the given string has a convertible format?

cheng chen · Accepted Answer · 2016-05-13T12:48:48.463

date time format does not exist in sqlite, one work around is to use number sequence as date time for example:

def Strtime2Num(result):
    date_object = datetime.strptime(Strtime,"%Y-%m-%dT%H:%M%S+0800")
    Strtime = date_object.strftime('%Y%m%d%H%M')
    return int(result)

when select in sql command, you can use

con.execute(UPDATE YearUpdateCapa SET %s = %d WHERE DateTime >= %d AND DateTime <= %d' % (name, avail,start,end))

score 1 · Answer 2 · edited May 23 '17 at 11:51

sqlite3 won't produce an error until you try to select the value back as datetime object if you try to insert a timestamp in the wrong format. To validate the input date/time string, convert it to datetime.datetime first. sqlite3 module can convert datetime.datetime automatically:

#!/usr/bin/env python3
import sqlite3
from datetime import datetime

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE dates (timestamp TIMESTAMP)')
db.executemany('INSERT INTO dates VALUES(?)', 
               [[datetime.utcnow()], ['2016-03-28 20:23:46.000000+0800']])
print(*db.execute('SELECT timestamp FROM dates'), sep='\n')

Output

('2016-05-14 05:08:52.487113',)
('2016-03-28 20:23:46.000000+0800',)

TIMESTAMP field accepts a string in ISO 8601 format 'YYYY-MM-DD HH:MM:SS.mmmmmm' or datetime.datetime object.

To return datetime.datetime() back, you could use detect_types=sqlite3.PARSE_DECLTYPES (if you need the timezone support; define your own converter that returns timezone-aware datetime objects).

How to write date and time from string to Sqlite table?

2 Answers2

Output