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
