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

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.

CodePudding user response：

I do not really think it would be possible to achieve within single query.

Option 1:

implement stored procedure where you can implement some logic how to calculate these periods.

Option 2:

add new column (duration) and on insert new record calculate difference between NOW and previous timestamp and update duration for previous record

CodePudding user response：

This isn't a complete answer, but it should help you get started. From the SQL in the post, I'm assuming you're using SQL Server. I've formatted the code to match. Replace @input with your query above if you want to test on your own data. (SELECT * FROM [home_network].[dbo]...)

--generate dummy table with sample output from question
declare @input as table(
    [timestamp] [datetime] NOT NULL,
    [pointname] [nvarchar](50) NOT NULL,
    [pointvalue] [float] NOT NULL
)

insert into @input values
('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);

--Append a row number to the result
WITH A as (
SELECT *,
ROW_NUMBER() OVER(ORDER BY(SELECT 1)) as row_count
from @input)

--Self join the table using the row number as a guide
SELECT sum(datediff(MINUTE,startTimes.timestamp,endTimes.timestamp))
from A as startTimes
LEFT JOIN A as endTimes on startTimes.row_count=endTimes.row_count-1
--Only show periods of time where the heater is turned on at the start
WHERE startTimes.row_count%2=1

CodePudding user response：

Your problem can be divided into 2 steps:

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.
Filter records with ON status and summarize the duration
(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).