Time in State Retrieval Queries(Examples)

Description

Time in State Retrieval Queries(Examples)

• Author: Mike Viteri
• Published: 12/4/2015
• Applies to: Historian

Details

The following query finds the minimum time-in-state for the SteamValve discrete tag. Note that minimum times are returned for each state for both the five-minute phantom cycle before the query start time and for the single retrieval cycle between 10:00 and 10:05.

SELECT DateTime, TagName, vValue, StateTime, wwStateCalc FROM History

WHERE TagName IN ('SteamValve')

AND DateTime >= '2005-04-17 10:00:00'

AND DateTime <= '2005-04-17 10:05:00'

AND wwCycleCount = 1

AND wwRetrievalMode = 'ValueState'

AND wwStateCalc = 'Min'

The following query finds the maximum time-in-state for the SteamValve discrete tag in the same time period as in Query 1. Note how both the minimum and maximum values for the "1" state are very similar, while they are very different for the "0" state. This is due to the "cut-off" effect.

SELECT DateTime, TagName, vValue, StateTime, wwStateCalc FROM History

WHERE TagName IN ('SteamValve')

AND DateTime >= '2005-04-17 10:00:00'

AND DateTime <= '2005-04-17 10:05:00'

AND wwCycleCount = 1

AND wwRetrievalMode = 'ValueState'

AND wwStateCalc = 'Max'

The following query finds the minimum time-in-state for the SteamValve discrete tag for the “on” state. Note that minimum times are returned for each state for both the five-minute phantom cycle before the query start time and for the single retrieval cycle between 10:00 and 10:05.

SELECT DateTime, TagName, vValue, StateTime, wwStateCalc FROM History

WHERE TagName IN ('SteamValve')

AND DateTime >= '2005-04-17 10:00:00'

AND DateTime <= '2005-04-17 10:05:00'

AND wwCycleCount = 1

AND wwRetrievalMode = 'ValueState'

AND wwStateCalc = 'Min'

AND State = '1'

The following query returns the total of time in state for a discrete tag. In this example, the TimeStampRule system parameter is set to "End" (the default setting), so the returned values are timestamped at the end of the cycle. The returned rows represent the time-in-state behavior during the period starting at 2005-04-13 00:00:00.000 and ending at 2005-04-14 00:00:00.000.

SELECT DateTime, vValue, StateTime, wwStateCalc FROM History

WHERE DateTime > '2005-04-13 00:00:00.000'

AND DateTime <= '2005-04-14 00:00:00.000'

AND TagName IN ('PumpStatus')

AND wwRetrievalMode = 'ValueState'

AND wwStateCalc = 'Total'

AND wwCycleCount = 1

The following query returns the percentage of time in state for a discrete tag for multiple retrieval cycles. The TimeStampRule system parameter is set to "End" (the default setting), so the returned values are timestamped at the end of the cycle. Note that the first row returned represents the results for the period starting at 2003-07-03 22:00:00.000 and ending at 2003-07-04 00:00:00.000.

The "Percent" time-in-state retrieval mode is the only mode in which the StateTime column does not return time data. Instead, it returns percentage data (in the range of 0 to 100 percent) representing the percentage of time in state.

SELECT DateTime, vValue, StateTime, wwStateCalc FROM History

WHERE DateTime >= '2003-07-04 00:00:00.000'

AND DateTime <= '2003-07-05 00:00:00.000'

AND TagName IN ('PumpStatus')

AND Value = 1

AND wwRetrievalMode = 'ValueState'

AND wwStateCalc = 'Percent'

AND wwCycleCount = 13