# Calculating the total time a device is on per day

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?

• I am not sure what you are looking for. The decision on how to count is yours as a designer. How do you want to count it ? Commented Jan 24, 2023 at 20:49
• In the last example given ideally, the hourly sums for each day would 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. Commented Jan 24, 2023 at 20:54
• Unfortunately I don't have a SQLite instance to test with in front of me at the moment, so I'm having a hard time understanding your problem. Are you concerned about your window function (which shouldn't matter if the data spans multiple days since you're not partitioning by day) or are you concerned with the subtraction logic between the two dates? (FWIW, running a similar example on SQL Server yielded the correct answer.)
– J.D.
Commented Jan 24, 2023 at 23:08
• Welcome to the DBA.SE community. What is your actual question? Commented Jan 25, 2023 at 18:57
• Hi @JohnK.N. I've edited the post to make my question more explicit. Please let me know if there's anything else I can do to help clarify. Commented Jan 25, 2023 at 19:16

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.

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