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' tab…

Accepted Answer

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
```

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

Solution

Problem