SQLite subtract time difference between two tables if there is a match

Question

I need some help with a SQLite Query. I have two tables, a table called 'production' and a table called 'pause':

CREATE TABLE production (
    date TEXT,
    item TEXT,
    begin TEXT,
    end TEXT
);

CREATE TABLE pause (
    date TEXT,
    begin TEXT,
    end TEXT
);

For every item which is produced, an entry in the table production with the current date, the start time and the end time (two timestamps in the format HH:MM:SS) is created. So let's assume, the production table looks like:

+------------+-------------+------------+----------+
|    date    |    item     |    begin   |   end    |
+------------+-------------+------------+----------+
| 2013-07-31 |    Item 1   |  06:18:00  | 08:03:05 |
| 2013-08-01 |    Item 2   |  06:00:03  | 10:10:10 |
| 2013-08-01 |    Item 1   |  10:30:15  | 14:20:13 |
| 2013-08-01 |    Item 1   |  15:00:10  | 16:00:00 |
| 2013-08-02 |    Item 3   |  08:50:00  | 15:00:00 |
+------------+-------------+------------+----------+

The second table also contains a date and a start and an end time. So let's assume, the 'pause' table looks like:

+------------+------------+----------+
|    date    |    begin   |   end    |
+------------+------------+----------+
| 2013-08-01 |  08:00:00  | 08:30:00 |
| 2013-08-01 |  12:00:00  | 13:30:00 |
| 2013-08-02 |  10:00:00  | 10:30:00 |
| 2013-08-02 |  13:00:00  | 14:00:00 |
+------------+------------+----------+

Now I wanna get a table, which contains the time difference between the production begin and end time for every item. If there is a matching entry in the 'pause' table, the pause time should be subtracted.

So basically, the end result should look like:

+------------+------------+-------------------------------------------------+
|    date    |    Item    | time difference (in seconds), excluding pause   |
+------------+------------+-------------------------------------------------+
| 2013-07-31 |   Item 1   |                     6305                        |
| 2013-08-01 |   Item 1   |                     12005                       |
| 2013-08-01 |   Item 2   |                     13207                       |
| 2013-08-02 |   Item 3   |                     16800                       |
+------------+------------+-------------------------------------------------+

I am not really sure, how I can accomplish it with SQLite. I know that it is possible to do this sort of calculation with Python, but in the end I think it would be better to let the database do the calculations. Maybe someone of you could give me a hint on how to solve this problem. I tried different queries, but I always ended up with different results than I expected.

Answer 1

To convert a time string to the number of seconds, use the strftime function with the %s modifier. (A time string without a date part will be assumed to have the date 2000-01-01, but this cancels out when computing the differences.)

To compute the pause times for a specific production record, use a correlated subquery; the total aggregate is needed to cope with zero/one/multiple matching pauses.

SELECT date,
       item,
       sum(strftime('%s', end) - strftime('%s', begin) -
           (SELECT total(strftime('%s', end) - strftime('%s', begin))
            FROM pause
            WHERE pause.date   = production.date
              AND pause.begin >= production.begin
              AND pause.end   <= production.end)
          ) AS seconds
FROM production
GROUP BY date,
         item