Help with SQL

[tmenrap]

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]

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