Accessing Citadel Data
IntData Table
The ODBC driver presents Citadel data to other applications as a IntData table. The table contains a field or column for each data member logged to the Citadel database and three fields you can use to specify query criteria and to time stamp retrieved data: IntInterval, LocalTime, and UTCTime.
IntInterval specifies the query value sample rate. IntInterval can range from 10 ms to several years. IntInterval defaults to 1 (one day). WEEK is a standard seven days, but MONTH and YEAR account for different month lengths and leap years.
Because Citadel is event-driven, it only logs a value when the value changes. Using IntInterval, you can query Citadel for values evenly spaced over a period of time.
LocalTime and UTCTime are timestamps that indicate when values are logged. Citadel stores the time in UTCTime format and derives LocalTime from the stored time.
The following where clause query uses IntInterval and LocalTime to select data over a specified time at one-minute intervals. Notice that time and date formats are the same as those used in Lookout.
SELECT * FROM IntData
WHERE LocalTime>12/1 10:00 AND LocalTime<12/2 13:00 AND IntInterval=“1:00”
RawData Table
The RawData table is used to retrieve the actual values logged by Lookout for a data member and the times they were logged. As Lookout logs values for data members asynchronously, there is no correlation between the timestamps for one data member and another. For this reason, when querying the points table, you may only query one data member at a time.
The where clause using LocalTime and UTCTime is supported for the points table; however, IntInterval is not relevant to the RawData table. The data transforms are also not relevant to the RawData table and are not supported. An example of a query using the points table could be as follows:
SELECT LocalTime, Pot1 FROM RawData
WHERE LocalTime > "12/1 10:00" AND
LocalTime < "12/2 10:00"
Data Transform
Your queries can include special commands that perform data transforms to manipulate and analyze historical data. The following table lists data transform commands.
Data Transform Commands
|
Command
|
Transformation
|
|
MATH_MIN(Datapoint)
|
Returns the minimum for Datapoint across the interval.
|
|
MATH_Max(Datapoint)
|
Returns the maximum for Datapoint across the interval.
|
|
MATH_Avg(Datapoint)
|
Returns the average for Datapoint across the interval.
|
|
MATH_STDEV(Datapoint)
|
Returns the standard deviation for Datapoint across the interval.
|
|
MATH_STARTS(Datapoint)
|
Returns the number of starts (that is, the number of transitions from OFF to ON) for Datapoint across the interval. For numeric points, 0.0 is interpreted as OFF, and all other numbers are treated as ON.
|
|
MATH_STOPS(Datapoint)
|
Returns the number of stops (that is, the number of transitions from ON to OFF) for Datapoint across the interval.
|
|
MATH_ETM(Datapoint)
|
Returns the amount of time Datapoint was in the ON state across the interval.
|
|
MATH_QUAL(Datapoint)
|
There might be gaps in the historical data traces in Citadel because of machine shutdown, Lookout shutdown, or a similar occurrence. Qual returns the ratio of time for which valid data exists for Datapoint across the interval to the length of the interval itself. If valid data exists for only one-half of the interval, Qual returns 0.5.
|
Using these data transforms, you can directly calculate and retrieve complex information from the database such as averages and standard deviations, so you do not need to extract raw data and then manipulate it in another application.
For example, you need to know how many times a compressor motor started in December. You also need to know its total run time for the month. Use the following query to get your answers:
SELECT MATH_STARTS(PLC.MotorRun),MATH_ETM(PLC.MotorRun)
FROM IntData
WHERE LocalTime>=12/1/95 AND LocalTime<1/1/96 AND IntInterval=31
SQL Examples
The following examples are typical query statements; however, your queries might be much more involved, depending on your system requirements.
|
SELECT *
FROM IntData
|
Retrieves the current value of every data member logged to Citadel. Because your query does not occur at the same moment in time as a PLC poll, signals scanned from PLCs are not included in the retrieved data.
|
|
SELECT *
FROM IntData
WHERE IntInterval=0:01
|
Retrieves the value of every data member logged today in one-second increments. Notice that the interval value is enclosed in quotation marks.
|
|
SELECT LocalTime, Pot1
FROM IntData
WHERE LocalTime>8:50
AND IntInterval=0:01
|
Retrieves and time stamps the value of Pot1 in one-second increments from 8:50 this morning to now. Names are enclosed by quotes.
|
|
SELECT LocalTime, AB1.I:3, MATH_MAX(AB1.I:3)
FROM IntData
WHERE LocalTime>10/1/95
AND LocalTime<11/1/95
AND IntInterval=1:00
|
Retrieves and time stamps an Allen-Bradley PLC input in one-minute intervals for the month of October, 1995. This query also indicates the highest occurring input value of each minute.
|
|
SELECT LocalTime, OVEN1_SP, PLC.OVEN1_PV,
MATH_MAX(PLC.OVEN1_PV), MATH_MIN(PLC.OVEN1_PV),
MATH_AVG(PLC.OVEN1_PV)
FROM IntData
WHERE LocalTime>=14:00
AND LocalTime <15:00
AND IntInterval=1:00:00
|
Retrieves an oven temperature at 3:00 p.m. and shows the highest, lowest, and average temperatures between 2 p.m. and 3 p.m.
|