SQLite DateTime存储的一些反思

引言

 文名用的反思，说明之前在使用的时候踩了一些坑，然后回过头来才想起总结一下，以防下次犯同样的错误。
 之前写过一个功能，将数据存储到数据库中，并且要标记每条记录（document）存储的时间，于是随手加了一个DateTime字段（field），用来标记存储时间。因为使用的是Qt，所以写的时候也是很简单，就按下面这样写了。

	sqlQuery.exec(QString("INSERT INTO TABLE VALUES('%1', '%2')").arg(QDateTime::currentDateTime().toString("yyyy/M/d h:mm:s")).arg(arg2))

当时按这种格式存储，主要是想程序不能使用的时候，可以通过数据库管理工具去查看数据，也能直观地看到记录的存储时间。这种想法本身没有什么问题。
 直到前段时间，要添加一个查询时间区间内记录的功能。

过程

 功能很简单，很容易实现，它的关键就是比较时间。
 于是就随便一搜，发现比较时间的方法很多，用>、<、==或者between…and…，又或者有用strftime等系列函数的，于是拣了个简单的一试，果然可以。
 但是，在测试过程中发现，如果是下面记录，

并不能得到想要的结果。
 于是展开分析了下，首先我用的是比较运算符的方法。c++用>、<、==这类操作符操作的时候，因为重载了运算符，本质上是对字符串进行了字典序比较，所以"2019/12/10 9:07"中的’9’大于"2019/12/10 17:06"中的’1’，它认为第一个时间反而大于第二个时间，所以在这种情况下用比较运算符操作得不到想要的结果。
 于是，我换作between…and…比较两个时间，也没有得到期待的结果。因为between…and…是sql里提供的，我用的又是Sqlite数据库，于是查询了关于它的介绍，介绍页面，

The BETWEEN operator
The BETWEEN operator is logically equivalent to a pair of comparisons. "x BETWEEN y AND z" is equivalent to "x>=y AND x<=z" except that with BETWEEN, the x expression is only evaluated once. The precedence of the BETWEEN operator is the same as the precedence as operators == and != and LIKE and groups left to right.

 可知，between…and…并没有明显的异于比较运算符。
 而且， Sqlite中日期和时间说明中说到，Sqlite中的日期和时间使用的是timeString，是字符串。所以我不管使用比较运算符还是between…and…操作符在上述情况中，都是返回了字典序比较的结果。
 但是，好在Sqlite还支持strftime函数，它功能之一是可以将指定格式的时间字符串转换成unix时间戳（unix stamp，unix时间戳是从1970年1月1日（UTC/GMT的午夜）开始所经过的秒数）。于是，我便将数据表中每条记录的Datetime通过

strftime('%s', Datetime)

转成unix时间戳，然后将我要查询的时间区间的边界也转成unix时间戳做比较，筛选我得到的记录，经过了一番调试，成功了。但是这种方法，有一点操作上比较麻烦的地方，就是格式要严格对应，比如，下面语句可以输出正确的时间戳，

SELECT strftime('%s', '2019-11-12 08:33:04')
输出：1573547584

而下面格式就不行，

SELECT strftime('%s', '2019-11-12 8:33:04')
输出：NULL

 想要得到正确的结果就要格式严格对应（yyyy-MM-dd hh:mm:ss）。然而很多时候，就拿我用的Qt来说，如上面图片中控件上直接取下的时间字符串，默认格式是"yyyy/M/d h:mm:s"，就不能与strftime中的格式完全对应，需要修改格式，一旦涉及的格式多了，容易出错。而且，这种方法需要对数据表每条记录都做格式化转换处理，一旦数据量大了，额外的消耗也会不少。 事实上，如果你格式严格按照(yyyy-MM-dd hh:mm:ss)来的话，可以直接用比较运算符和between…and…进行比较（因为即使是字典序比较也没什么问题）。
 如果你没有用数据库可视化工具打开后可以直观看到记录时间的需求，你可以在存储的时候就将DateTime转成时间戳，这样进行查询比较的时候就是简单的运算符比较，而且应用层应该提供了许多转换的方法，不容易出错。

后记

 虽然这个问题很常见也很基础，但是实际编程的时候确实给我带来过一定的困扰。并且，我也不觉得我上面采用的方法是最合理的。希望有人看到后，如果觉得有更合理的方法，可以提出来讨论。