SQL 如何比较 sqlite TIMESTAMP 值

How to compare sqlite TIMESTAMP values

提问by Roel

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 数据库，我想在其中选择 TIMESTAMP 列中的值在某个日期之前的行。我认为这很简单，但我无法完成。我试过这个：

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.htmland http://www.sqlite.org/datatypes.htmland 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 Expert Personal 中尝试这个。

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

回答by Mark Rushakoff

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

问题在于您将数据插入表的方式：+0200语法与SQLite 的任何时间格式都不匹配：

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. 时:分
9. 时:分:秒
10. 时:分:SS.SSS
11. 现在
12. 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_ATcolumn. If it indeed a datetime, it will compare correctly against a string:

您尚未描述CREATED_AT列中包含哪些类型的数据。如果它确实是一个日期时间，它将正确地与字符串进行比较：

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 DATETIMEfunction 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 alwayspost some data so that other people can reproduce your problem. You should even feel free to come up with a subsetof your original data that still reproduces the problem.

如果这些都没有解决您的问题（即使他们解决了！），您应该始终发布一些数据，以便其他人可以重现您的问题。您甚至可以随意提出仍会重现问题的原始数据子集。

回答by J. Polfer

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 定义自己的存储函数以进行比较。

回答by Sam Hartman

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.htmlHowever, 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.

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