Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Calculate the time in minutes that a value has been greater than x

Tags:

sql

datetime

sql-server

gaps-and-islands

sql-server-2017

I want to be able to calculate the total time in minutes that the temperature column has been over a certain temperature. For example, I want to know how long in minutes, the temperature has been above 16.

If a reading at 12:28 was 16 and a reading at 12:30 is 17, we are saying that from 12:28 to 12:30, the value was 17.

Furthermore, if the first or only reading is above x (17), this will be two minutes because when the device is started it takes x minutes (2 minutes in this instance) before the first reading is taken.

  • The SerialNumber is the serial number of the device reading the temperature.
  • The CombinDateTime is the time the temperature reading was taken.
  • The Temperature is the temperature value.

  SerialNumber, CombinDateTime, Temperature
  1000649496, 2018-12-05 10:56:52,    16.6
  1000649496, 2018-12-05 10:58:52,    17.3
  1000649496, 2018-12-05 11:00:52,    16.8
  1000649496, 2018-12-05 11:02:52,    16.6
  1000649496, 2018-12-05 11:04:52,    16.4
  1000649496, 2018-12-05 11:06:52,    16.3
  1000649496, 2018-12-05 11:08:52,    16.3
  1000649496, 2018-12-05 11:10:52,    16.2
  1000649496, 2018-12-05 11:12:52,    16.2
  1000649496, 2018-12-05 11:14:52,    16.2
  1000649496, 2018-12-05 11:16:52,    16.2
  1000649496, 2018-12-05 11:18:52,    16.2
  1000649496, 2018-12-05 11:20:52,    16.1
  1000649496, 2018-12-05 11:22:52,    16.1
  1000649496, 2018-12-05 11:24:52,    16.1
  1000649496, 2018-12-05 11:26:52,    16
  1000649496, 2018-12-05 11:28:52,    16
  1000649496, 2018-12-05 11:30:52,    16
  1000649496, 2018-12-05 11:32:52,    16
  1000649496, 2018-12-05 11:34:52,    16.1
  1000649496, 2018-12-05 11:36:52,    16.1
  1000649496, 2018-12-05 11:38:52,    16.1
  1000649496, 2018-12-05 11:40:52,    16.1
  1000649496, 2018-12-05 11:42:52,    16.1
  1000649496, 2018-12-05 11:44:52,    16.1
  1000649496, 2018-12-05 11:46:52,    16.1
  1000649496, 2018-12-05 11:48:52,    16
  1000649496, 2018-12-05 11:50:52,    16
  1000649496, 2018-12-05 11:52:52,    16
  1000649496, 2018-12-05 11:54:52,    16
  1000649496, 2018-12-05 11:56:52,    16
  1000649496, 2018-12-05 11:58:52,    16
  1000649496, 2018-12-05 12:00:52,    16.1
  1000649496, 2018-12-05 12:02:52,    16.1
  1000649496, 2018-12-05 12:04:52,    16.1
  1000649496, 2018-12-05 12:06:52,    16.1
  1000649496, 2018-12-05 12:08:52,    16
  1000649496, 2018-12-05 12:10:52,    16
  1000649496, 2018-12-05 12:12:52,    16
  1000649496, 2018-12-05 12:14:52,    16
  1000649496, 2018-12-05 12:16:52,    16
  1000649496, 2018-12-05 12:18:52,    16
  1000649496, 2018-12-05 12:20:52,    16
  1000649496, 2018-12-05 12:22:52,    16
  1000649496, 2018-12-05 12:24:52,    16
  1000649496, 2018-12-05 12:26:52,    16
  1000649496, 2018-12-05 12:28:52,    16
  1000649496, 2018-12-05 12:30:52,    16
  1000649496, 2018-12-08 08:08:52,    15.1
  1000649496, 2018-12-05 12:32:52,    16
  1000649496, 2018-12-05 12:34:52,    16
  1000649496, 2018-12-05 12:36:52,    16
  1000649496, 2018-12-05 12:38:52,    16

My query so far is very basic:

    SELECT SerialNumber, CombineDateTime, Temperature 
    FROM RawData
    WHERE Temperature > 16

The principal I have in mind is that I select the data-set and order by date and move through each row until I find a value that is over 16. I then take the date and then move through the records until I find a value that is <= 16, then take that date and time and datediff() the period in minutes.

I know you are not supposed to loop through SQL records, so I am thinking of using a CTE, but I am not too sure how to do this.

My expected results would be for example:

    SerialNumber, MinutesOver 
    1000649496, 1186

TIA

like image 785
The OrangeGoblin Avatar asked Dec 19 '25 17:12

The OrangeGoblin

2 Answers

This looks like a gaps and islands problem (consecutive > 16 temperatures and <= 16 temperatures need to be grouped together) and one solution is as follows:

DECLARE @threshold DECIMAL(18, 2) = 16;
WITH cte1 AS (
    SELECT *, CASE 
           -- first row itself is greater than threshold
           WHEN Temperature  >  @threshold  AND  LAG(Temperature)  OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) IS NULL      THEN 1
           -- next row is greater than threshold
           WHEN Temperature <=  @threshold  AND LEAD(Temperature)  OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) > @threshold THEN 1
           -- prev row is greater than threshold
           WHEN Temperature <=  @threshold  AND  LAG(Temperature)  OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) > @threshold THEN 1
    END AS chg
    FROM @t
), cte2 AS (
    SELECT *, SUM(chg) OVER (PARTITION BY SerialNumber ORDER BY CombinDateTime) AS grp
    FROM cte1
)
SELECT SerialNumber
     , MIN(CombinDateTime) AS StartDateTime
     , MAX(CombinDateTime) AS EndDateTime
     , DATEDIFF(SECOND, MIN(CombinDateTime), MAX(CombinDateTime)) / 60.0 AS Total
FROM cte2
GROUP BY SerialNumber, grp
HAVING MAX(Temperature) > @threshold

Result:

SerialNumber  StartDateTime        EndDateTime          Total
1000649496    2018-12-05 10:56:52  2018-12-05 11:24:52  28.000000
1000649496    2018-12-05 11:32:52  2018-12-05 11:46:52  14.000000
1000649496    2018-12-05 11:58:52  2018-12-05 12:06:52  8.000000
like image 109
Salman A Avatar answered Dec 21 '25 08:12

Salman A

A solution with LAG and rolling SUM window functions:

DECLARE @ThresholdTemperature DECIMAL(3, 1) = 16

;WITH BreakMarker AS
(
    -- Determine if the temperature is above or below the threshold
    SELECT
        M.*,
        LimitMarker = CASE WHEN M.Temperature > @ThresholdTemperature THEN 0 ELSE 1 END
    FROM
        #Measures AS M
),
LaggedChange AS
(
    -- Determine at which point in time the temperature moves between the threshold
    SELECT
        B.*,
        TempChange = CASE WHEN B.LimitMarker = LAG(B.LimitMarker, 1, 0) OVER (
            PARTITION BY 
                B.SerialNumber 
            ORDER BY 
                B.CombinDateTime ASC) THEN 0 ELSE 1 END
    FROM
        BreakMarker AS B
),
BreakGroups AS
(
    -- Generate a group ID value to calculate MAX and MIN
    SELECT
        L.*,
        BreakGroup = SUM(TempChange) OVER (PARTITION BY L.SerialNumber ORDER BY L.CombinDateTime ASC)
    FROM
        LaggedChange AS L
)
SELECT
    B.SerialNumber,
    MinCombinDateTime = MIN(B.CombinDateTime),
    MaxCombinDateTime = MAX(B.CombinDateTime),
    MinutesOver = DATEDIFF(MINUTE, MIN(B.CombinDateTime), MAX(B.CombinDateTime))
FROM
    BreakGroups AS B
GROUP BY
    B.SerialNumber,
    B.BreakGroup
HAVING
    MIN(B.Temperature) > @ThresholdTemperature

Result:

SerialNumber    MinCombinDateTime           MaxCombinDateTime           MinutesOver
1000649496      2018-12-05 10:56:52.000     2018-12-05 11:24:52.000     28
1000649496      2018-12-05 11:34:52.000     2018-12-05 11:46:52.000     12
1000649496      2018-12-05 12:00:52.000     2018-12-05 12:06:52.000     6

You can check the temporary results from the CTE here, so it's easier to understand the step by step logic:

SerialNumber    CombinDateTime              Temperature LimitMarker TempChange  BreakGroup
1000649496      2018-12-05 10:56:52.000     16.6        0           0           0
1000649496      2018-12-05 10:58:52.000     17.3        0           0           0
1000649496      2018-12-05 11:00:52.000     16.8        0           0           0
1000649496      2018-12-05 11:02:52.000     16.6        0           0           0
1000649496      2018-12-05 11:04:52.000     16.4        0           0           0
1000649496      2018-12-05 11:06:52.000     16.3        0           0           0
1000649496      2018-12-05 11:08:52.000     16.3        0           0           0
1000649496      2018-12-05 11:10:52.000     16.2        0           0           0
1000649496      2018-12-05 11:12:52.000     16.2        0           0           0
1000649496      2018-12-05 11:14:52.000     16.2        0           0           0
1000649496      2018-12-05 11:16:52.000     16.2        0           0           0
1000649496      2018-12-05 11:18:52.000     16.2        0           0           0
1000649496      2018-12-05 11:20:52.000     16.1        0           0           0
1000649496      2018-12-05 11:22:52.000     16.1        0           0           0
1000649496      2018-12-05 11:24:52.000     16.1        0           0           0
1000649496      2018-12-05 11:26:52.000     16.0        1           1           1
1000649496      2018-12-05 11:28:52.000     16.0        1           0           1
1000649496      2018-12-05 11:30:52.000     16.0        1           0           1
1000649496      2018-12-05 11:32:52.000     16.0        1           0           1
1000649496      2018-12-05 11:34:52.000     16.1        0           1           2
1000649496      2018-12-05 11:36:52.000     16.1        0           0           2
1000649496      2018-12-05 11:38:52.000     16.1        0           0           2
1000649496      2018-12-05 11:40:52.000     16.1        0           0           2
1000649496      2018-12-05 11:42:52.000     16.1        0           0           2
1000649496      2018-12-05 11:44:52.000     16.1        0           0           2
1000649496      2018-12-05 11:46:52.000     16.1        0           0           2
1000649496      2018-12-05 11:48:52.000     16.0        1           1           3
1000649496      2018-12-05 11:50:52.000     16.0        1           0           3
1000649496      2018-12-05 11:52:52.000     16.0        1           0           3
1000649496      2018-12-05 11:54:52.000     16.0        1           0           3
1000649496      2018-12-05 11:56:52.000     16.0        1           0           3
1000649496      2018-12-05 11:58:52.000     16.0        1           0           3
1000649496      2018-12-05 12:00:52.000     16.1        0           1           4
1000649496      2018-12-05 12:02:52.000     16.1        0           0           4
1000649496      2018-12-05 12:04:52.000     16.1        0           0           4
1000649496      2018-12-05 12:06:52.000     16.1        0           0           4
1000649496      2018-12-05 12:08:52.000     16.0        1           1           5
1000649496      2018-12-05 12:10:52.000     16.0        1           0           5
1000649496      2018-12-05 12:12:52.000     16.0        1           0           5
1000649496      2018-12-05 12:14:52.000     16.0        1           0           5
1000649496      2018-12-05 12:16:52.000     16.0        1           0           5
1000649496      2018-12-05 12:18:52.000     16.0        1           0           5
1000649496      2018-12-05 12:20:52.000     16.0        1           0           5
1000649496      2018-12-05 12:22:52.000     16.0        1           0           5
1000649496      2018-12-05 12:24:52.000     16.0        1           0           5
1000649496      2018-12-05 12:26:52.000     16.0        1           0           5
1000649496      2018-12-05 12:28:52.000     16.0        1           0           5
1000649496      2018-12-05 12:30:52.000     16.0        1           0           5
1000649496      2018-12-05 12:32:52.000     16.0        1           0           5
1000649496      2018-12-05 12:34:52.000     16.0        1           0           5
1000649496      2018-12-05 12:36:52.000     16.0        1           0           5
1000649496      2018-12-05 12:38:52.000     16.0        1           0           5
1000649496      2018-12-08 08:08:52.000     15.1        1           0           5
like image 25
EzLo Avatar answered Dec 21 '25 06:12

EzLo
Sign in to Comment

Related questions

Postgres find duplicates using multiple columns and delete one row based on column value
Minus 1 unit if the condition is met in mysql
ORA-00604: error occurred at recursive SQL level 1
SQL question conjunction
SQL Error with "Turkish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!