Approach 1 (works in any version)

1. Add a sequence number to the table (or a copy of the columns of interest).
2. Do a "self join" ON a.seq = b.seq-1 to match up adjacent rows.
3. Filter down to the status changes: WHERE a.Value != b.Value

The table will contain, for example:

  3  13:44:13  off   4  13:47:27   on
 12  14:27:27   on  13  14:34:48  off

Assuming you want the timespan between pairs of rows in this table, again add a sequence number, and do the self join to extract and subtract the next to last column.

Approach 2 (plug for upgrading)

If you have MariaDB 10.2 or MySQL 8.0, then there are "Windowing functions" that make this task immensely simpler. See LAG(); it obviates the need for the self joins and the sequencing.

In the first pass, look for LAG(Value) != Value while using ORDER BY DateTime. In the second pass, diff the LAG of the time with the current time.

Approach 3 (possibly the best for 5.5 without windowing)

Since you are stuck in the nearly-antique 5.5, Consider using a Stored Procedure, not a VIEW. It will involve two extra tables. They cannot be CREATE TEMPORARY TABLE since you can't use a temp table twice for the self-join.

So, a better approach (without windowing) would be to use @variables to simulate LAG().

Details (Maybe I will give you details, but first...)

Provide sample data (not images) ready to run (CREATEs and INSERTs). Also provide expected output. (I understand ON to OFF, but what about OFF to ON; and do you want the start time of the range; etc.)

Approach 1 (works in any version)

1. Add a sequence number to the table (or a copy of the columns of interest).
2. Do a "self join" ON a.seq = b.seq-1 to match up adjacent rows.
3. Filter down to the status changes: WHERE a.Value != b.Value

The table will contain, for example:

  3  13:44:13  off   4  13:47:27   on
 12  14:27:27   on  13  14:34:48  off

Assuming you want the timespan between pairs of rows in this table, again add a sequence number, and do the self join to extract and subtract the next to last column.

Approach 2 (plug for upgrading)

If you have MariaDB 10.2 or MySQL 8.0, then there are "Windowing functions" that make this task immensely simpler. See LAG(); it obviates the need for the self joins and the sequencing.

In the first pass, look for LAG(Value) != Value while using ORDER BY DateTime. In the second pass, diff the LAG of the time with the current time.

Approach 3 (possibly the best for 5.5 without windowing)

Since you are stuck in the nearly-antique 5.5, Consider using a Stored Procedure, not a VIEW. It will involve two extra tables. They cannot be CREATE TEMPORARY TABLE since you can't use a temp table twice for the self-join.

So, a better approach (without windowing) would be to use @variables to simulate LAG().

Approach 4 (somewhere between 1 and 3)

This involves a single pass of looking at consecutive rows. If the first of a consecutive (based on datetime) pair of rows says ON, then calculate the "on time". Then SUM the "on times".

Details (Maybe I will give you details, but first...)

Provide sample data (not images) ready to run (CREATEs and INSERTs). Also provide expected output. (I understand ON to OFF, but what about OFF to ON; and do you want the start time of the range; etc.)

A hiccup

Since you want the "on time" to stop at the top of the hour, extra code is needed to stop the summation, etc. One artificial way to do this is to add 2 rows, one second apart at the top of each hour. The first is OFF, the second (1 second later) is ON. This will create a 1-second error in the results, but has no impact on the Approaches except for simplifying the "top of hour" requirement.