如何在 SQLite 中使用特定长度来比较时间？

Question

我以 YYYY-MM-DD HH:MM:SS 的形式存储事件时间。

我想要做的是找到在前一个触发器之后 7 小时以上发生的所有事件实例。在阅读 SQLite Date 文档后，我不确定执行此操作的最简单方法。我想我将不得不以某种方式使用 strftime，但我真的不知道如何处理它。

这是我必须构建的基本查询：

SELECT * FROM Event WHERE Event.DateTime （超过 7 小时后） (SELECT Trigger.DateTime FROM Trigger WHERE Event.DateTime > Trigger.DateTime)

我当前的测试数据设置如下：

DROP TABLE IF EXISTS "Event";
CREATE TABLE "Event" ("DateTime" TEXT, "ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE );
INSERT INTO "Event" VALUES('2000-01-01 08:00:00',1);
INSERT INTO "Event" VALUES('2000-01-01 09:00:00',2);
INSERT INTO "Event" VALUES('2000-01-01 14:00:00',3);
INSERT INTO "Event" VALUES('2000-01-01 15:00:00',4);
INSERT INTO "Event" VALUES('2000-01-01 20:00:00',5);
DROP TABLE IF EXISTS "Trigger";
CREATE TABLE "Trigger" ("DateTime" TEXT, "ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE );
INSERT INTO "Trigger" VALUES('2000-01-01 00:00:01',1);
INSERT INTO "Trigger" VALUES('2000-01-01 10:00:01',2);

因此它应该从事件中返回 1、2 和 5，因为这些是触发后 7 小时以上的事件。

Answer 1

您是否有理由要针对 Trigger.DateTime 测试 Event.DateTime 两次？我认为这应该也有效：

SELECT * FROM Event 
WHERE EXISTS(SELECT 1 FROM Trigger 
             WHERE datetime(Event.DateTime,'+7 hours') > Trigger.DateTime)

UPDATE

因为您只想与最新的触发器进行比较，所以它会更像这样：

SELECT * FROM Event 
WHERE EXISTS(SELECT MAX(Trigger.DateTime) newest FROM Trigger 
             HAVING datetime(Event.DateTime,'-7 hours') > newest)

Edit

SELECT * FROM Event 
WHERE NOT EXISTS(SELECT 1 FROM Trigger 
                 WHERE datetime(Event.DateTime,'-7 hours') <= Trigger.DateTime)

SELECT * FROM Event 
WHERE datetime(Event.DateTime,'-7 hours') > 
            (SELECT MAX(Trigger.DateTime) newest FROM Trigger)

Edit2

SELECT * FROM Event 
WHERE NOT EXISTS(SELECT 1 FROM Trigger 
                 WHERE Trigger.DateTime BETWEEN datetime(Event.DateTime,'-7 hours') AND Event.DateTime)

SELECT * FROM Event 
WHERE datetime(Event.DateTime,'-7 hours') > 
            (SELECT MAX(Trigger.DateTime) newest FROM Trigger
             WHERE Trigger.DateTime < Event.DateTime)

Answer 2

怎么样

SELECT * FROM Event 
WHERE datetime(Event.DateTime,'+7 hours') > (SELECT Trigger.DateTime FROM Trigger 
                                           WHERE Event.DateTime > Trigger.DateTime)