CODEWITHSUNDEEP
sqlsqlitedatetimedurationstrftime
Damon
Damon

Reputation: 10809

How to compare times using specific lengths in SQLite?

I have event times stored in the form YYYY-MM-DD HH:MM:SS.

What I am trying to do is find all instances of Event where it occurs more than 7 hours after the preceding Trigger. I am not sure the easiest way to do this after reading the SQLite Date documentation. I guess I will have to use strftime somehow, but I'm really not sure how to approach it.

This is the basic query I'd have to build off of:

SELECT * FROM Event WHERE Event.DateTime (is more than 7 hours after) (SELECT Trigger.DateTime FROM Trigger WHERE Event.DateTime > Trigger.DateTime)

I have the test data currently set up like so:

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

so it should return 1, 2 and 5 from the events since those are the ones that are over 7 hours after a trigger.

Upvotes: 0

Views: 318

Answers (2)

jswolf19
jswolf19

Reputation: 2303

Is there a reason you want to test the Event.DateTime against the Trigger.DateTime twice? I think this should work just as well:

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

UPDATE

Since you only want to compare to the latest trigger, it would be something more like this:

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)

Upvotes: 1

Doug Currie
Doug Currie

Reputation: 41170

How about 

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

Upvotes: 0

Related Questions