Title: Get sum of paired time differences
Tags: sql;sqlite
Question: I have an sqlite table of card controlled entry door usages.

I need the sum of the even-odd time intervals, so for example the expected sum for card 02 is (12:03 - 08:07) + (16:03 - 14:52) = 03:56 + 01:21 = 05:07

People can move freely in daytime, so there could be many entries for a card.

Test table

The table looks like this:

```card_id | time
----------------------------
   03   | 07:55
   01   | 08:02
   02   | 08:07
   03   | 11:56
   02   | 12:03
   03   | 12:23
   02   | 14:52
   03   | 15:56
   01   | 15:58
   02   | 16:03
```

Comment: Well if the first one is missing you are a bit stuffed because there's no way to tell! The second one , default to now, to the end of the day, skip?

Comment: Yeah just thinking, normally I'd do something like this with a CTE but SQLLite doesn't have that.

Comment: I can't do it without. :(. Would be significantly easier if Date and Time were one DateTime Column though

Comment: Thanks @Tony, null is good when there is no matching pair for an entry, but I don't know how to write a query to solve these problems. I can get the time difference between two entries, but this only works when there is only two entries for a card, and there could be any of them, because people can freely move in the daytime.

Comment: Then it seems that I have to solve it in Java, but i am eager to know if it could be somehow done in sqlite (actually I can't do it with a CTE either).

Comment: It could be datetime, or even just time! Actually this is just a memory table I am creating from the data imported from csv files, and they contain only one day per file. Editing the question :)

Here is the accepted answer: Query is not hard but time interval handling is!

```SELECT card_id,
    TIME(1721059.5+SUM(JULIANDAY(time)*((SELECT COUNT() FROM t AS _ WHERE card_id=t.card_id AND time<t.time)%2*2-1)))
FROM t
GROUP BY card_id;
```

How it works:

```((SELECT COUNT() FROM t AS _ WHERE card_id=t.card_id AND time<t.time)%2*2-1)``` counts all records before current one and returns ```-1``` or ```1```.

```JULIANDAY(time)``` convert time string in a numeric quantity. Product of former two results will return desired calculation (in days).

```TIME(1721059.5+...)``` will return a properly formatted time string. ```1721059.5``` is required because of ```JULIANDAY``` definition and SQLite date/time functions being only valid for dates after ```0000-01-01 00:00:00```.

EDIT: Looking at @CL answer, I see that using ```JULIANDAY('00:00')``` is more elegant then ```1721059.5```. However, I keep constant as it should perform better then using function call.

Comment for this answer: Yes. I didn't care about it as it was not a requirement.
Comment for this answer: If I understand well this will always result a negative interval when there is no matching pair for an entry (which is good because it is very easy to filter out these cards from the results). Am I right?
Comment for this answer: It's good for me, because I must filter out them. Nice answer, thank you!

Here is another answer: I don't have an SQLite available to try this, but something like this ought to work:

```CREATE TEMP TABLE card_times AS
SELECT time FROM entries
WHERE card_id = 2;

SELECT SUM(strftime("%s", '2000-01-01 ' || x.time || ':00') 
         - strftime("%s", '2000-01-01 ' || y.time || ':00')) 
FROM card_times AS x INNER JOIN card_times AS y
ON x.ROWID = y.ROWID + 1  
WHERE (x.ROWID % 2) = 0
```

Get a table of times for the desired card. Join it with itself using row id's to match the right pairs.  Filter out the odd pairs.  Sum the results.

Note this will fail if the clock wraps to the next day.  Taking care of that case in SQLite would be ugly.  You really need full date-times in the original data.

This gives the answer in seconds.  I let you work out how to get it in the units you need.

Comment for this answer: Empty result set, and I can't figure out.
I have uploaded the test table: http://cdn.virtuosoft.eu/stackoverflow/19393071/entries.sqlite
Comment for this answer: OK, you are right, the problem was that the temporary table was already existed (and it needs `card_id = '02'` instead of `2` so it was empty). It returns 18420, which equals to 5:07. The downside is that it only calculates one card. Thanks, vote went.

Here is another answer: It would be possible to compute which times are even and odd dynamically, but it's easier if we put this information into the table itself:

```ALTER TABLE MyTable ADD odd;

UPDATE MyTable
SET odd = (SELECT COUNT(*) % 2
           FROM MyTable AS c
           WHERE c.card_id = MyTable.card_id
             AND c.time    < MyTable.time);
```

Then it is possible to simply sum the times for each card, multiplying the value by ```-1``` for even times.
(Because the result is a time difference, adding ```julianday('00:00')``` is required to get back to an absolute time value.)

```SELECT card_id,
       (SELECT TIME(SUM(julianday(time) * CASE odd WHEN 0 THEN -1 ELSE 1 END)
                    + julianday('00:00'))
        FROM MyTable
        WHERE card_id = cards.card_id
       ) AS total_time
FROM (SELECT DISTINCT card_id
      FROM MyTable) AS cards;

03|07:34:00
01|07:56:00
02|05:07:00
```

Comment for this answer: Thank you very much for your answer, but I have accepted LS_dev's because it was simpler.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 6, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:10/15/13', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><sqlite>', 'Title': 'Get sum of paired time differences', 'ViewCount': 114, 'id': 19393071, 'user_AboutMe': '<p><a href="https://github.com/istvan-ujjmeszaros" rel="nofollow noreferrer">https://github.com/istvan-ujjmeszaros</a><br />\n<a href="https://www.virtuosoft.eu/" rel="nofollow noreferrer">https://www.virtuosoft.eu/</a><br />\n<a href="https://www.bootstrap-live-customizer.com/" rel="nofollow noreferrer">https://www.bootstrap-live-customizer.com/</a></p>\n', 'user_AccountId': 236645.0, 'user_DisplayName': 'István Ujj-Mészáros', 'user_Reputation': 3163.0, 'user_Views': 756.0, 'user_id': 504270.0}}