Example 5: calculating total time between value changes
- Last UpdatedMar 19, 2025
- 1 minute read
You can calculate the total time the multiple discrete tags are in a certain state. For example, you want to know the total time that two pumps were on during a 24-hour period.
The following query returns a dataset that shows the time when both discrete tags had a value of 0:
SELECT * FROM OPENQUERY(INSQL,'
SELECT DateTime, Pump1, Pump2, wwResolution
FROM WideHistory
WHERE DateTime >= "2012-03-08 16:00"
AND DateTime < "2012-03-08 17:00"
AND wwRetrievalMode="DELTA"
')
WHERE Pump1+Pump2=0
The results are:
|
DateTime |
Pump1 |
Pump2 |
wwResolution |
|---|---|---|---|
|
2012-03-08 16:00:00.0000000 |
0 |
0 |
67 |
|
2012-03-08 16:00:01.5980000 |
0 |
0 |
2521 |
|
2012-03-08 16:00:04.4470000 |
0 |
0 |
18500 |
|
2012-03-08 16:00:23.6000000 |
0 |
0 |
13995 |
|
2012-03-08 16:00:37.9140000 |
0 |
0 |
2625 |
|
... |
The following query shows how to return the total time when both tags had a value of 0:
SELECT SUM(wwResolution) FROM OPENQUERY(INSQL,'
SELECT DateTime, Total=Pump1+Pump2, wwResolution
FROM WideHistory
WHERE DateTime >= "2012-03-08 16:00"
AND DateTime < "2012-03-08 17:00"
AND wwRetrievalMode="DELTA"
')
WHERE Total=0
The results are:
|
(No column name) |
|---|
|
2551289 |
If you changed the ending WHERE clause to Total>0, the returned time would be for when more than one discrete tag was true.