如何比较 sqlite TIMESTAMP 值 [英] How to compare sqlite TIMESTAMP values

问题描述

我有一个 Sqlite 数据库，我想在其中选择 TIMESTAMP 列中的值在某个日期之前的行.我认为这很简单，但我无法完成.我试过这个:

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:

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

以及它的各种变体，例如日期函数.我读过 http://sqlite.org/lang_datefunc.html 和 http://www.sqlite.org/datatypes.html 我希望该列是数字类型，并且比较将在 unix 时间戳值上完成.显然不是.任何人都可以提供帮助?如果重要的话，我正在 Sqlite Expert Personal 中尝试这个.

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.

这里是类型表描述:

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

和测试数据:

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

推荐答案

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

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:

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. 时:分:秒
10. 时:分:SS.SSS
11. 现在
12. DDDDDDDDDD

将其更改为使用 SS.SSS 格式可以正常工作:

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

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

(原答案)

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

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:

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

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

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:

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.

这篇关于如何比较 sqlite TIMESTAMP 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！