How to insert a datetime properly in a Sqlite database? (UTC vs with-timezone time)

Question

I need to insert a datetime into a Sqlite database with Python. (I already read this question, it's not a duplicate here because I'm dealing with timezones questions).

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

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

#2
c.execute('insert into mytable (title, t) values (?, ?)', ("hello", "Sat Mar 10 2018 12:12:00 GMT+0100 (Paris, Madrid)"))

c.execute("select * from mytable")
for a in c.fetchall(): 
    print a

# (u'hello', u'Sat Mar 10 2018 12:12:00 GMT+0100 (Paris, Madrid)')
# (u'hello2', u'2018-03-10 12:12:00')

• The method #1 seems to be the natural way to insert a datetime object inside a Sqlite database, but then it doesn't save the timezone.

• I receive user input in the form Sat Mar 10 2018 12:12:00 GMT+0100 (Paris, Madrid). The method #2 seems to save it into the DB as a string only, and this is not very good: then we can't easily query all rows with date between day1 and day2 for example.

• Question: Should I use another method, and convert Sat Mar 10 2018 12:12:00 GMT+0100 (Paris, Madrid) , and insert this UTC in the Sqlite DB?
  Or even should I only insert the UNIX timestamp as an integer in the DB?

Note: I'm ok to discard the original timezone, if it's properly converted to UTC: when I'll query from DB and display for output on a web page, I'll format with the user browser timezone.

Note2: According to https://www.sqlite.org/datatype3.html#date_and_time_datatype, it seems that Sqlite doesn't have a datetime type, it's stored as TEXT, REAL, or INT. But how does it internally know what to use among these choices?

Answer 1

The column t is not "really" a datetime one. According to: https://docs.python.org/2/library/sqlite3.html#default-adapters-and-converters , python adds the "timestamp" type to allow easy insertion of datetime objects into sqlite. These objects are converted into strings when they are stored in sqlite. sqlite only supports these datatypes: https://www.sqlite.org/datatype3.html

According to https://www.sqlite.org/lang_datefunc.html , sqlite uses UTC time internally. I think the easiest way for you to store your times is to either convert them into UTC or Unix time before inserting them.

If your times are in local time, you can convert them this way: How do I convert local time to UTC in Python?

If your times are not in local time, converting them is a bit more complicated, as Python does not define any timezones in the standard library. Your best bet here is probably to use a library such as the one described here: https://stackoverflow.com/a/4771733/6180687

I would recommend using Unix time if you already have it, to make your life easier.