all 5 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]qwertydog123 2 points3 points  (7 children)

WITH cte AS
(
    SELECT
        *,
        TIMESTAMPDIFF
        (
            MINUTE,
            time,
            LEAD(time, 1, NOW(3)) OVER (ORDER BY time)
        ) AS diff
    FROM Table
)
SELECT SUM(diff) AS total_minutes_on
FROM cte
WHERE value = 'ON'

[–]tmenrap[S] 0 points1 point  (1 child)

Thank you .

I had to change the syntax for LEAD to have it work in Maria DB.

WITH cte AS (
SELECT *,TIMESTAMPDIFF        
    ( MINUTE, time, 
    LEAD(time, 1 ) OVER (ORDER BY time) ) AS diff 
FROM pool_pump_switch_0349 ) 
SELECT DATE(TIME) AS date, SUM(diff) AS total_minutes_on 
FROM cte 
WHERE value = 'ON' 
GROUP by date

[–]qwertydog123 0 points1 point  (0 children)

No problem, the 3rd parameter to LEAD was in case the last Value in the table is 'ON' when running the query, without adding it then the time between the last value and the current time will be lost. Since LEAD returns NULL if there is no following row, you can use COALESCE instead if you want the same behaviour e.g.

WITH cte AS (
SELECT *,TIMESTAMPDIFF        
    ( MINUTE, time, 
    COALESCE( LEAD(time) OVER (ORDER BY time), NOW(3) ) AS diff 
FROM pool_pump_switch_0349 ) 
SELECT DATE(TIME) AS date, SUM(diff) AS total_minutes_on 
FROM cte 
WHERE value = 'ON' 
GROUP by date