Calculating the total time a device is on per day

Question

At the moment I've got an sqlite3 database that keeps track of the state of my smart home devices. The relevant parts of the schema for the main table are

CREATE TABLE states(
    state_id INTEGER NOT NULL,
    entity_id VARCHAR(255),
    state VARCHAR(255),
    last_updated DATETIME
);

I'm trying to calculate how long each entity has been in the state "on" for each day. Currently my thought would be to use the lead window function to create a column with the next updated time:

CREATE VIEW states_with_next_update AS
  SELECT *, lead(last_updated,1) over (PARTITION BY entity_id) as next_update
  FROM states;

and then it would be possible to subtract the next updated time from the current time to get the total time each entity was in a certain state for.

CREATE VIEW states_with_durations AS
  SELECT *, julianday(next_update) - julianday(last_updated) as state_duration, date(last_updated) as day
  FROM states_with_next_update;

With the duration of each state I can now use aggregate functions to calculate the total time each was in the "on" state:

SELECT day, entity_id, sum(state_duration)
FROM state_with_durations
WHERE state = "on"
GROUP BY day, entity_id;

The only problem with this method is that next_update may not fall in the same day leading to durations that contain time from two (or more) consecutive days, which leads to over counting on the first day, and under counting on the subsequent.

For example if an entity is in the state "on" from 2022-11-10 20:00 to 2022-11-11 02:00, the total for 2022-11-10 would read 6, and the total for 2020-11-11 would read 0.

So the question is, how do I make it so that in the example above the sum for the "on" state reads 4 hours for the day 2022-11-10, and 2 hours for 2022-11-11?

Answer 1

You need to create a list of all dates in your range of interest. You can do it with a recursive CTE (using start of day modifier to rewind the time to 00:00 hours):

WITH RECURSIVE datelist AS (
   SELECT datetime(min(last_updated),'start of day') AS day FROM states
   UNION ALL
   SELECT datetime(day,'+1 day') FROM datelist 
   WHERE datetime(day,'+1 day') < (SELECT max(last_updated) FROM states)
)

This list can be joined with states_with_next_update to generate a row for every day that is covered (completely or in part) by the event

...
FROM states_with_next_update JOIN datelist 
WHERE last_updated <= datetime(datelist.day,'+1 day') AND next_update > datelist.day

And now you can calculate how much time the event intersect the current day, using the scalar version of min and max (not to be confused with the aggregate).

min(julianday(next_update),julianday(datelist.day,'+1 day')) - max(julianday(last_updated),julianday(datelist.day)) AS state_duration

Now let's put it all together in your states_with_durations VIEW:

CREATE VIEW states_with_durations AS
  WITH RECURSIVE datelist AS (
       SELECT datetime(min(last_updated),'start of day') AS day FROM states
       UNION ALL
       SELECT datetime(day,'+1 day') FROM datelist 
       WHERE datetime(day,'+1 day') < (SELECT max(last_updated) FROM states)
  )
  SELECT *, min(julianday(next_update),julianday(datelist.day,'+1 day')) - max(julianday(last_updated),julianday(datelist.day)) AS state_duration, date(datelist.day) as day
  FROM states_with_next_update JOIN datelist 
  WHERE last_updated <= datetime(datelist.day,'+1 day') AND next_update > datelist.day;

You can now use your aggregate queries to get the result you wanted.

Answer 2

Given the description of what you want to achieve:

hourly sums for each day would [ideally] be 4 for the day 2022-11-10 and 2 for the day 2022-11-11. That's what I'm looking for instead of 6 for the day 2022-11-10 and 0 for the day 2022-11-11, which is how it currently stands“,

you will need to do the following:

1. Using a CTAS or some form of min()/max() group by create a list of timestamps for start and end of that day.
2. From this table create „pseudo“ state changes, that happen at end/start of a day, using the last or first state of that day or next day
3. Union the list of status changes with the list of pseudo events
4. Apply your reporting logic to the complete list.

existing:     |new day
---- X ------—-------- X ----
      < 6h -----—----->
   

Change:
---- X ------—P------- X ----
      < 4h --> < 2h -->