When a Sqlite column is declared as a timestamp, I understand that this works:
import sqlite3, datetime
dbconn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
c = dbconn.cursor()
c.execute('create table mytable(title text, t timestamp)')
c.execute('insert into mytable (title, t) values (?, ?)', ("hello", datetime.datetime(2018,3,10,12,12,00)))
c.execute('insert into mytable (title, t) values (?, ?)', ("hello2", datetime.datetime(2018,3,1,0,0,00)))
d1 = datetime.datetime(2018,3,10)
d2 = datetime.datetime(2018,3,11)
c.execute("select * from mytable where t >= ? and t < ?", (d1, d2))
for a in c.fetchall():
print a
# Result: (u'hello', datetime.datetime(2018, 3, 10, 12, 12))
But why does it still work when column t is defined as TEXT? It seems, according to this doc, that the datetime will then be stored as a string. Then why does such a >=, < comparison still work? Is it coincidence or good practice?
import sqlite3, datetime
dbconn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
c = dbconn.cursor()
c.execute('create table mytable(title text, t text)') # <--- here t has TEXT type
c.execute('insert into mytable (title, t) values (?, ?)', ("hello", datetime.datetime(2018,3,10,12,12,00)))
c.execute('insert into mytable (title, t) values (?, ?)', ("hello2", datetime.datetime(2018,3,1,0,0,00)))
d1 = datetime.datetime(2018,3,10)
d2 = datetime.datetime(2018,3,11)
c.execute("select * from mytable where t >= ? and t < ?", (d1, d2))
for a in c.fetchall():
print a
# Result: (u'hello', u'2018-03-10 12:12:00')
The strange thing is: if a datetime is just stored as a string, why does such a time interval comparison work? Is it just luck that the strings are compared (how with < and >?) correctly here?
Mark Benningfield :
The ISO 8601 date/time format YYYY-MM-DD HH:MM::SS is specifically designed to permit lexicographic sorting that corresponds to chronological sort order. So, comparisons between strings in this format work because that's the way it is designed to work.\n\nAccording to the SQLite docs, whenever 2 strings are compared, the applicable collation sequence is used to compare the 2 strings. The collation sequence used depends on the following rules:\n\n\n 7.1. Assigning Collating Sequences from SQL\n \n Every column of every table has an associated collating function. If no collating function is explicitly defined, then the collating function defaults to BINARY. The COLLATE clause of the column definition is used to define alternative collating functions for a column. \n \n The rules for determining which collating function to use for a binary comparison operator (=, <, >, <=, >=, !=, IS, and IS NOT) are as follows: \n \n \n If either operand has an explicit collating function assignment using the postfix COLLATE operator, then the explicit collating function is used for comparison, with precedence to the collating function of the left operand.\n If either operand is a column, then the collating function of that column is used with precedence to the left operand. For the purposes of the previous sentence, a column name preceded by one or more unary \"+\" operators is still considered a column name. \n Otherwise, the BINARY collating function is used for comparison.\n \n\n\nNote that storing date/time values as strings in any other format that does not compare the same lexicographically as chronologically results in date/time comparisons that are usually wrong.",
2018-03-15T19:11:59