Adding minutes of runtime from on/off records during a time period

Question

I have a SQL database that collects temperature and sensor data from the barn.

The table definition is:

CREATE TABLE [dbo].[DataPoints]
(
    [timestamp] [datetime] NOT NULL,
    [pointname] [nvarchar](50) NOT NULL,
    [pointvalue] [float] NOT NULL
)

The sensors report outside temperature (degrees), inside temperature (degrees), and heating (as on/off).

Sensors create a record when the previous reading has changed, so temperatures are generated every few minutes, one record for heat coming ON, one for heat going OFF, and so on.

I'm interested in how many minutes of heat has been used overnight, so a 24-hour period from 6 AM yesterday to 6 AM today would work fine.

This query:

SELECT * 
FROM [home_network].[dbo].[DataPoints]
WHERE (pointname = 'Heaters')
  AND (timestamp BETWEEN '2022-12-18 06:00:00' AND '2022-12-19 06:00:00')
ORDER BY timestamp

returns this data:

2022-12-19 02:00:20   |  Heaters   |  1
2022-12-19 02:22:22   |  Heaters   |  0
2022-12-19 03:43:28   |  Heaters   |  1
2022-12-19 04:25:31   |  Heaters   |  0

The end result should be 22 minutes + 42 minutes = 64 minutes of heat, but I can't see how to get this result from a single query. It also just happens that this result set has two complete heat on/off cycles, but that will not always be the case. So, if the first heat record was = 0, that means that at 6 AM, the heat was already on, but the start time won't show in the query. The same idea applies if the last heat record is =1 at, say 05:15, which means 45 minutes have to be added to the total.

Is it possible to get this minutes-of-heat-time result with a single query? Actually, I don't know the right approach, and it doesn't matter if I have to run several queries. If needed, I could use a small app that reads the raw data, and applies logic outside of SQL to arrive at the total. But I'd prefer to be able to do this within SQL.

Answer 1

Your problem can be divided into 2 steps:

1. Filter sensor type and date range, while also getting time span of each record by calculating date difference between timestamp of current record and the next one in chronological order.
2. Filter records with ON status and summarize the duration
3. (Optional) convert to HH:MM:SS format to display

Here's the my take on the problem with comments of what I do in each step, all combined into 1 single query.

-- Step 3: Convert output to HH:MM:SS, this is just for show and can be reduced
SELECT STUFF(CONVERT(VARCHAR(8), DATEADD(SECOND, total_duration, 0), 108), 
                            1, 2, CAST(FLOOR(total_duration / 3600) AS VARCHAR(5)))
FROM (
    -- Step 2: select records with status ON (1) and aggregate total duration in seconds 
    SELECT sum(duration) as total_duration
    FROM (
        -- Step 1: Use LEAD to get next adjacent timestamp and calculate date difference (time span) between the current record and the next one in time order
        SELECT TOP 100 PERCENT
            DATEDIFF(SECOND, timestamp, LEAD(timestamp, 1, '2022-12-19 06:00:00') OVER (ORDER BY timestamp)) as duration,
            pointvalue
        FROM [dbo].[DataPoints]
        -- filtered by sensor name and time range
        WHERE pointname = 'Heaters'
            AND (timestamp BETWEEN '2022-12-18 06:00:00' AND '2022-12-19 06:00:00')
        ORDER BY timestamp ASC
    ) AS tmp
    WHERE tmp.pointvalue = 1
) as tmp2

Note: As the last record does not have next adjacent timestamp, it will be filled with the end time of inspection (In this case it's 6AM of the next day).