python - 为什么这个 Sqlite 日期比较有效？

Question

当 Sqlite 列声明为 timestamp 时，我知道这行得通:

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))

但为什么在列 t 时它仍然有效？定义为 TEXT ？看来，根据this doc ，然后将日期时间存储为字符串。那么为什么这样一个>= , <比较仍然有效？这是巧合还是好的做法？

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')

奇怪的是:如果一个日期时间只是存储为一个字符串，为什么这样的时间间隔比较有效？字符串被比较只是运气(如何与<和> ?) 正确吗？

Answer 1

ISO 8601日期/时间格式 YYYY-MM-DD HH:MM::SS 专门设计用于允许与时间排序顺序相对应的字典排序。因此，这种格式的字符串之间的比较是有效的，因为这是它设计的工作方式。

根据SQLite docs ，无论何时比较 2 个字符串，都会使用适用的归类序列来比较 2 个字符串。使用的排序顺序取决于以下规则:

7.1. Assigning Collating Sequences from SQL

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.

The rules for determining which collating function to use for a binary comparison operator (=, <, >, <=, >=, !=, IS, and IS NOT) are as follows:

1. 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.

2. 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.

3. Otherwise, the BINARY collating function is used for comparison.

请注意，将日期/时间值存储为任何其他格式的字符串，如果不按字典顺序与按时间顺序比较相同，则会导致日期/时间比较通常是错误的。