i am trying to count (in minutes) between two records on 1 table and WHERE clause is the same condition.
_id | venue_id | act_time | status |
1 | 1 | 13:30 | 0 |
2 | 1 | 15:40 | 1 |
3 | 2 | 13:03 | 0 |
4 | 2 | 16:06 | 1 |
when i exec query like this :
SELECT _id, venue_id, status, (julianday(act_time IN (SELECT act_time FROM reports WHERE venue_id='1' AND status='1')) - julianday(act_time))*1440 AS duration FROM reports WHERE venue_id='1' AND status='0'
but, the result show the wrong calculation
Please help me what is the correct query for this problem?
so, if i count the duration between 15:40 - 13:30 (at venue_id='1') = 130 minutes.
thank you.
Aucun commentaire:
Enregistrer un commentaire