About phantom cycles

The phantom cycle is the name given to the cycle that leads up to the query start time. It is used to calculate which initial value to return at the query start time for all retrieval modes. Some retrieval modes use the phantom cycle to simply find the last known value prior to the query start time, whereas other retrieval modes use the entire cycle to calculate aggregates. The different uses of the phantom cycle can be seen in the following table.

It’s common to expect a single aggregate row returned for a particular time interval. You can accomplish this in several ways.

The following example is querying for hourly averages. It returns a single row time stamped at the query start time. If the query included the query end point by including an equal sign for it, the query would also have returned an additional row at the query end time.

SELECT DateTime, Value, Quality, QualityDetail, OPCQuality

FROM History

WHERE TagName IN ('SysTimeSec')

AND DateTime >= '2017-12-12 08:00:00'

AND DateTime < '2017-12-12 09:00:00'

AND wwRetrievalMode = 'Avg'

AND wwResolution = 3600000

The results are:

What may be confusing in this example is the calculation of the average in the returned row for the phantom cycle leading up to the query start time. The query specifies a positive one hour time interval between the query start time and the query end time. You may therefore expect that the calculated and returned average should be for the specified interval.

However, the time difference between start and end time in the above query is not actually required because the resolution is provided explicitly (wwResolution = 36000000). If the query specified an end time equal to the specified start time and if it included the equal sign for the end time, the query would still return the same single row of data.

SELECT DateTime, Value, Quality, QualityDetail, OPCQuality

FROM History

WHERE TagName IN ('SysTimeSec')

AND DateTime = '2017-10-16 08:00:00'

AND DateTime <= '2017-10-16 09:00:00'

AND wwRetrievalMode = 'Avg'

AND wwCycleCount = 1

The results are:

This second example also asks for hourly averages and it also returns only a single row of data stamped at the query start time. This query, however, must specify a time difference between the start and end time, because the resolution is not explicitly defined in the query.

As in the preceding query, the specified interval and cycle count of 1 may look like the returned row has been calculated for the specified interval, but the returned row is once again for the phantom cycle leading up to the start time.

For some queries, you may want to be certain to include values on a cycle boundary. For example, the following query is looking for a minimum value within a cycle. In this query, the beginning DateTime statement uses ">=" to ensure that the entire cycle is queried. Even if the minimum value happens to be at the beginning of the cycle, the following query will provide an accurate result:

SELECT StartDateTime, *

FROM History

WHERE TagName = 'SysTimeSec'
AND DateTime >= '2016-03-31 15:41:10'
AND DateTime < '2016-03-31 15:41:20'
AND wwRetrievalMode = 'Min'
AND Quality <> 133
AND wwCycleCount = 1

The StartDateTime makes it easier to see which time interval was used to calculate the returned aggregate. This column returns the time stamp of the beginning of the cycle used for the aggregate calculation. The time stamp is always returned in accordance with the specified time zone and always has the same offset as the time stamp returned in the DateTime column, even when the two time stamps are on different sides of a DST change.

Assuming results are timestamped at the end of the cycle (as is done by default when wwTimeStampRule is set to END), the initial rows in the examples above would return a DateTime equal to '2009-10-16 08:00:00', and the StartDateTime column would return '2009-10-16 07:00:00' making it easy to interpret the result.

If instead the query were to ask for results time stamped at the beginning of the cycle with wwTimeStampRule set to START, the initial rows in the same examples would still return a DateTime equal to '2009-10-16 08:00:00', but the time stamp has now been shifted in accordance with the time stamp request. The result is therefore calculated for the specified time interval between 8 a.m. and 9 a.m. In this example, the new StartDateTime column would return the same time stamp as the DateTime column, '2009-10-16 08:00:00', again making it easier to interpret the result.

For retrieval modes for which cycles are defined, the StartDateTime column returns the cycle start time. These modes are:

• Cyclic retrieval

• Interpolated retrieval

• Best fit retrieval

• Average retrieval

• Minimum retrieval

• Maximum retrieval

• Integral retrieval

• Counter retrieval

• ValueState retrieval

• RoundTrip retrieval

In the remaining retrieval modes, the StartDateTime column returns the same time stamp as the DateTime column.

For an additional example, see Querying aggregate data in different ways.