sqlite> select sdata ,timestamp from tbl_sensor where nid=4 and timestamp >=date('now','-4 days') order by timestamp ASC ;0|2014-08-28 04:00:320|2014-08-28 04:56:420|2014-08-28 04:57:410|2014-08-28 04:58:410|2014-08-28 04:59:410|2014-08-28 05:22:410|2014-08-28 05:23:410|2014-08-28 05:24:410|2014-08-28 05:25:41255|2014-08-28 05:26:39255|2014-08-28 05:26:41255|2014-08-28 05:27:410|2014-08-28 05:28:030|2014-08-28 05:28:410|2014-08-28 05:29:410|2014-08-28 05:30:410|2014-08-28 05:41:410|2014-08-28 05:42:410|2014-08-28 05:43:41255|2014-08-28 05:44:35255|2014-08-28 05:44:41255|2014-08-28 05:45:410|2014-08-28 06:34:580|2014-08-28 06:35:410|2014-08-28 06:36:41255|2014-08-28 06:37:10/* here sdata =0 Means ,off , and if sdata =255 means on is there *//* I want total time in ON and total time they are OFF *//* so i query to make time difference between two events ,than i plan to add */sqlite> select sdata,timestamp , strftime('%M',timestamp) - strftime('%M',ifnull((select timestamp from tbl_sensor where sdata =0 and nid =4 and timestamp >= date('now','-4 days') order by timestamp asc ),timestamp ))from tbl_sensor where sdata =255 and nid =4 and timestamp >= date('now','-4 days') order by timestamp ASC ;255|2014-08-28 05:26:39|26255|2014-08-28 05:26:41|26255|2014-08-28 05:27:41|27255|2014-08-28 05:44:35|44255|2014-08-28 05:44:41|44255|2014-08-28 05:45:41|45255|2014-08-28 06:37:10|37 /here it does not work as expected , all the time it's subtracted from this only 0|2014-08-28 04:00:32i want 1. subtraction is happen only for first event (0|2014-08-28 04:00:32) - (255|2014-08-28 05:26:39)output 255|2014-08-28 05:26:39|26and rest of the time it just add the even time255|2014-08-28 05:26:41255|2014-08-28 05:27:41Again After ,255 sdata, it goes to next one255|2014-08-28 05:26:41255|2014-08-28 05:27:410|2014-08-28 05:28:030|2014-08-28 05:28:410|2014-08-28 05:29:410|2014-08-28 05:30:410|2014-08-28 05:41:410|2014-08-28 05:42:410|2014-08-28 05:43:41255|2014-08-28 05:44:35255|2014-08-28 05:44:41255|2014-08-28 05:45:410|2014-08-28 06:34:58 then it subtract (0|2014-08-28 05:28:03) - (255|2014-08-28 05:44:35)out put should be255|2014-08-28 05:44:35|16 not athis one255|2014-08-28 05:44:35|44plz help me out ...
-- View this message in context: http://sqlite.1065341.n5.nabble.com/How-should-i-found-time-difference-between-two-events-in-sqlite3-table-like-on-off-status-tp77617.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users