如何比较sqlite时间戳值

I have an Sqlite database in which I want to select rows of which the value in a TIMESTAMP column is before a certain date. I would think this to be simple but I can't get it done. I have tried this:

我有一个Sqlite数据库，在其中我要选择时间戳列中值在某个日期之前的行。我认为这很简单，但我做不到。我试过这个:

SELECT * FROM logged_event WHERE logged_event.CREATED_AT < '2010-05-28 16:20:55'

and various variations on it, like with the date functions. I've read http://sqlite.org/lang_datefunc.html and http://www.sqlite.org/datatypes.html and I would expect that the column would be a numeric type, and that the comparison would be done on the unix timestamp value. Apparantly not. Anyone who can help? If it matters, I'm trying this out in Sqlite Expert Personal.

还有各种各样的变化，比如日期函数。我已经阅读了http://sqlite.org/lang_datefunc.html和http://www.sqlite.org/datatypes.html，我希望这个列是一个数字类型，比较将在unix时间戳值上完成。很显然不是。谁能帮忙吗?如果这很重要，我将在Sqlite专家个人中尝试。

Edit:

编辑:

Here's type table description:

这是类型表描述:

CREATE TABLE [logged_event]
(
[id] INTEGER  NOT NULL PRIMARY KEY,
[created_at] TIMESTAMP,
[name] VARCHAR(64),
[data] VARCHAR(512)
);

And the test data:

测试数据:

INSERT INTO table VALUES(1,'2010-05-28T15:36:56+0200','test','test');
INSERT INTO table VALUES(2,'2010-05-28T16:20:49+0200','test','test');
INSERT INTO table VALUES(3,'2010-05-28T16:20:51+0200','test','test');
INSERT INTO table VALUES(4,'2010-05-28T16:20:52+0200','test','test');
INSERT INTO table VALUES(5,'2010-05-28T16:20:53+0200','test','test');
INSERT INTO table VALUES(6,'2010-05-28T16:20:55+0200','test','test');
INSERT INTO table VALUES(7,'2010-05-28T16:20:57+0200','test','test');

3 个解决方案

#1

The issue is with the way you've inserted data into your table: the +0200 syntax doesn't match any of SQLite's time formats:

问题在于您将数据插入到表中的方式:+0200语法与SQLite的时间格式不匹配:

YYYY-MM-DD
YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:毫米
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM
HH:MM:SS
HH:MM:SS
HH:MM:SS.SSS
HH:MM:SS.SSS
now
现在
DDDDDDDDDD
DDDDDDDDDD

Changing it to use the SS.SSS format works correctly:

更改它以使用SS.SSS格式正确工作:

sqlite> CREATE TABLE Foo (created_at TIMESTAMP);
sqlite> INSERT INTO Foo VALUES('2010-05-28T15:36:56+0200');
sqlite> SELECT * FROM Foo WHERE foo.created_at < '2010-05-28 16:20:55';
sqlite> SELECT * FROM Foo WHERE DATETIME(foo.created_at) < '2010-05-28 16:20:55';
sqlite> INSERT INTO Foo VALUES('2010-05-28T15:36:56.200');
sqlite> SELECT * FROM Foo WHERE DATETIME(foo.created_at) < '2010-05-28 16:20:55';
2010-05-28T15:36:56.200

If you absolutely can't change the format when it is inserted, you might have to fall back to doing something "clever" and modifying the actual string (i.e. to replace the + with a ., etc.).

如果在插入时你绝对不能改变格式，那么你可能需要做一些“聪明”的事情，修改实际的字符串(例如用a .等替换+)。

(original answer)

(原始的回答)

You haven't described what kind of data is contained in your CREATED_AT column. If it indeed a datetime, it will compare correctly against a string:

您还没有描述CREATED_AT列中包含什么类型的数据。如果它确实是一个datetime，它将与字符串进行正确的比较:

sqlite> SELECT DATETIME('now');
2010-05-28 16:33:10
sqlite> SELECT DATETIME('now') < '2011-01-01 00:00:00';
1

If it is stored as a unix timestamp, you need to call DATETIME function with the second argument as 'unixepoch' to compare against a string:

如果将其存储为unix时间戳，则需要调用DATETIME函数，第二个参数为“unixepoch”，以便与字符串进行比较:

sqlite> SELECT DATETIME(0, 'unixepoch');
1970-01-01 00:00:00
sqlite> SELECT DATETIME(0, 'unixepoch') < '2010-01-01 00:00:00';
1
sqlite> SELECT DATETIME(0, 'unixepoch') == DATETIME('1970-01-01 00:00:00');
1

If neither of those solve your problem (and even if they do!) you should always post some data so that other people can reproduce your problem. You should even feel free to come up with a subset of your original data that still reproduces the problem.

如果这两种方法都不能解决您的问题(即使它们解决了问题)，那么您应该始终发布一些数据，以便其他人可以重现您的问题。您甚至可以*地使用原始数据的一个子集，而这些数据仍然会重新生成问题。

#2

SQLite's support for date/time types is very limited. You may have to roll-your-own method for maintaining time information. At least, that's what I did.

SQLite对日期/时间类型的支持非常有限。您可能需要使用自己的方法来维护时间信息。至少，我是这么做的。

You can define your own stored-functions for doing comparisons using the SQLite create_function() API.

您可以使用SQLite create_function() API定义自己的存储函数来进行比较。

#3

As best I can tell, it's entirely reasonable to include a timezone specifier; see the text "formats 2 through 10 can . . ." at http://www.sqlite.org/lang_datefunc.html However, the issue is that only the date functions interpret timestamps as dates. So for actual comparison, you need to either pass the timestamp through a date function or store things such that string comparison works. One approach would be to code your application such that you called datetime on every value that you insert and on every literal value in a select statement. However, simply not including the time zone as suggested by the existing answer may be easier in many applications.

就我所知，包含时区说明符是完全合理的;在http://www.sqlite.org/lang_datefunc.html中可以看到“格式化2到10 can . . .”然而，问题是只有日期函数将时间戳解释为日期。因此，对于实际的比较，您需要通过日期函数传递时间戳，或者存储字符串比较工作的内容。一种方法是对应用程序进行编码，以便对所插入的每个值以及select语句中的每个文字值调用datetime。然而，在许多应用程序中，简单地不包括现有答案所建议的时区可能更容易。

#1