CODEWITHSUNDEEP
sqlite
Daniel
Daniel

Reputation: 2592

SQLite time duration calculation from rows

I want to calculate duration between rows with datetime data in SQLite.

Let's consider this for the base data (named intervals):

| id |       date       |  state  |
|  1 | 2020-07-04 10:11 | On      |
|  2 | 2020-07-04 10:22 | Off     |
|  3 | 2020-07-04 11:10 | On      |
|  4 | 2020-07-04 11:25 | Off     |

I'd like to calculate the duration for both On and Off state:

| Total On  | 26mins |
| Total Off | 48mins |

Then I wrote this query:

SELECT
  "Total " || interval_start.state AS state,
  (SUM(strftime('%s', interval_end.date)-strftime('%s', interval_start.date)) / 60) || "mins" AS duration
FROM
  intervals interval_start
INNER JOIN
  intervals interval_end ON interval_end.id = 
    (
      SELECT id FROM intervals WHERE
        id > interval_start.id AND
        state = CASE WHEN interval_start.state = 'On' THEN 'Off' ELSE 'On' END
      ORDER BY id
      LIMIT 1
    )
GROUP BY
  interval_start.state

However if the base data is a not in strict order:

| id |       date       |  state  |
|  1 | 2020-07-04 10:11 | On      |
|  2 | 2020-07-04 10:22 | On      | !!!
|  3 | 2020-07-04 11:10 | On      |
|  4 | 2020-07-04 11:25 | Off     |

My query will calculate wrong, as it will pair the only Off date with each On dates and sum them together.

Desired behavior should result something like this:

| Total On  | 74mins |
| Total Off | 0mins  | --this line can be omitted, or can be N/A

I have two questions:

  1. How can I rewrite the query to handle these wrong data situations?
  2. I feel my query is not the best in terms of performance, is it possible to improve it?

Upvotes: 2

Views: 364

Answers (1)

forpas
forpas

Reputation: 164089

Use a CTE where you return only the starting rows of each state and then aggregate:

with cte as (
  select *, lead(id) over (order by date) next_id
  from (
    select *, lag(state) over (order by date) prev_state
    from intervals
  )
  where state <> coalesce(prev_state, '')
) 
select c1.state,
  sum(strftime('%s', c2.date) - strftime('%s', c1.date)) / 60 || 'mins' duration
from cte c1 inner join cte c2
on c2.id = c1.next_id
group by c1.state

See the demos: 1 and 2

Upvotes: 2

Related Questions