A slight mod on my solution makes it work for DST changes too. Again...rowid
must be maintained.
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE log(d date);
INSERT INTO "log" VALUES('2011-03-13 01:55');
INSERT INTO "log" VALUES('2011-03-13 01:56');
INSERT INTO "log" VALUES('2011-03-13 01:58');
INSERT INTO "log" VALUES('2011-03-13 03:00');
INSERT INTO "log" VALUES('2011-03-13 03:01');
INSERT INTO "log" VALUES('2011-03-13 03:03');
INSERT INTO "log" VALUES('2011-03-13 03:04');
INSERT INTO "log" VALUES('2011-03-13 03:05');
COMMIT;
sqlite> select
l2.d,l1.d,(strftime('%s',datetime(l1.d,'utc'))-strftime('%s',datetime(l2.d,'utc'))-60)/60
from log l1,log l2 where (l1.rowid=l2.rowid+1) and
(strftime('%s',datetime(l1.d,'utc')) - strftime('%s',datetime(l2.d,'utc')) !=
60);
2011-03-13 01:56|2011-03-13 01:58|1
2011-03-13 01:58|2011-03-13 03:00|1
2011-03-13 03:01|2011-03-13 03:03|1
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
________________________________
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
