How to calculate time difference in SQLite and SUM these values, grouping by day?

Question

I want to calculate total difference between two date fields in SQLite (they are declared as REAL). I.E. datefield1 and datefield2 and group by day.

Datefield1 is login date, datefield2 is logout date. Each day, user can login/logout several times. I want to sum up login time of a user (datefield2-datefield1) for each day.

Which day? The login day or logout day? – Dan Bracuk Jan 06 '15 at 15:55 — Dan Bracuk, Jan 06 '15 at 15:55
I've solved this problem, thanks – Sanyin Jan 06 '15 at 16:31 — Sanyin, Jan 06 '15 at 16:31

score 1 · Answer 1 · edited Jan 09 '15 at 05:51

1

SOLVED:

Query

select id, datetime(logindate), datetime(logoutdate), 
sum((julianday(logoutdate) - julianday(logindate)))
from MyTable GROUP BY strftime('%d', logindate);

edited Jan 09 '15 at 05:51

Ullas

11,450
4
33
50

answered Jan 06 '15 at 16:32

Sanyin

41
2
8

How to calculate time difference in SQLite and SUM these values, grouping by day?

1 Answers1

Linked