The system behaves differently when doing typical delta-based queries where a start date and end date are specified using the comparison operators >=, >, <=
and <. The comparison operators can be used on the History and WideHistory
tables. The comparison operators also apply regardless of how the query is executed (for example, four-part naming, OLE DB provider views, and so on). Delta queries that use the comparison operators return all the valid changes to a set of tags over the specified time span. Using deadbands and other filters may modify the set of valid changes.
Specifying the Start Date with ">="
If the start date is specified using >= (greater than or equal to), then a row is always returned for the specified start date. If the start date/time coincides exactly with a valid value change, then the Quality is normal (0). Otherwise the value at the start date is returned, and the Quality value is 133 (because the length of time that the tag's value was at X is unknown).
Query 1
For this query, the start date will not correspond to a data change:
SELECT DateTime, Value, Quality FROM History
WHERE TagName = 'SysTimeMin' AND wwRetrievalMode = 'Delta'
AND DateTime >= '2001-01-13 12:00:30'
AND DateTime < '2001-01-13 12:10:00'
The start time (12:00:30) does not correspond with an actual change in value, and is therefore marked with the initial quality of 133:
DateTime TagName Content
2001-02-28 19:18:00.000 ReactLevel The Pump is off
DateTime Value Quality
(10 row(s) affected)
Query 2
For this query, the start date will correspond to a data change:
SELECT DateTime, Value, Quality FROM History
WHERE TagName = 'SysTimeMin' AND wwRetrievalMode = 'Delta'
AND DateTime >= '2001-01-13 12:01:00'
AND DateTime < '2001-01-13 12:10:00'
The start time (12:01:00) does correspond exactly with an actual change in value, and is therefore marked with the normal quality of 0.
(9 row(s) affected)
Query 3
For this query, the start date will return at least one row, even though the query captures no data changes:
SELECT DateTime, Value, Quality FROM History
WHERE TagName = 'SysTimeMin' AND wwRetrievalMode = 'Delta'
AND DateTime >= '2001-01-13 12:00:30'
AND DateTime < '2001-01-13 12:01:00'
The query does not capture an actual change in value, and is therefore marked with the initial value quality of 133 for the start time of the query:
(1 row(s) affected) 2001-01-13 12:02:00.000 2.0 0 2001-01-13 12:03:00.000 3.0 0 2001-01-13 12:04:00.000 4.0 0 2001-01-13 12:05:00.000 5.0 0 2001-01-13 12:06:00.000 6.0 0 2001-01-13 12:07:00.000 7.0 0 2001-01-13 12:08:00.000 8.0 0 2001-01-13 12:09:00.000 9.0 0
DateTime Value Quality
2001-01-13 12:01:00.000 1.0 0 2001-01-13 12:02:00.000 2.0 0 2001-01-13 12:03:00.000 3.0 0 2001-01-13 12:04:00.000 4.0 0 2001-01-13 12:05:00.000 5.0 0 2001-01-13 12:06:00.000 6.0 0 2001-01-13 12:07:00.000 7.0 0 2001-01-13 12:08:00.000 8.0 0 2001-01-13 12:09:00.000 9.0 0
DateTime Value Quality
Specifying the Start Date with ">"
If the start date is specified using > (greater than), then the first row returned is the first valid change after (but not including) the start date. No initial value
row is returned. A query that uses > to specify its start date may return zero rows.
Query 1
For this query, the first row that will be returned will be the first valid change after (but not including) the start time (12:00:30):
SELECT DateTime, Value, Quality FROM History
WHERE TagName = 'SysTimeMin' AND wwRetrievalMode = 'Delta'
AND DateTime > '2001-01-13 12:00:30'
AND DateTime < '2001-01-13 12:10:00'
The first row returned is the first valid change after (but not including) the start time (12:00:30):
(9 row(s) affected)
Query 2
For this query, the start date will correspond to a data change, but it will be excluded from the result set because the operator used is greater than, not
greater than or equal to.
SELECT DateTime, Value, Quality FROM History
WHERE TagName = 'SysTimeMin' AND wwRetrievalMode = 'Delta'
AND DateTime > '2001-01-13 12:01:00'
AND DateTime < '2001-01-13 12:10:00'
The start time (12:01:00) corresponds exactly with an actual change in value, but it is excluded from the result set because the operator used is greater than, not greater than or equal to.
DateTime Value Quality
2001-01-13 12:01:00.000 1.0 0 2001-01-13 12:02:00.000 2.0 0 2001-01-13 12:03:00.000 3.0 0 2001-01-13 12:04:00.000 4.0 0 2001-01-13 12:05:00.000 5.0 0 2001-01-13 12:06:00.000 6.0 0 2001-01-13 12:07:00.000 7.0 0 2001-01-13 12:08:00.000 8.0 0 2001-01-13 12:09:00.000 9.0 0
DateTime Value Quality
2001-01-13 12:02:00.000 2.0 0
2001-01-13 12:03:00.000 3.0 0
(8 row(s) affected)
Query 3
This query will return no rows, because no data changes are captured:
SELECT DateTime, Value, Quality FROM History
WHERE TagName = 'SysTimeMin' AND wwRetrievalMode = 'Delta'
AND DateTime > '2001-01-13 12:00:30'
AND DateTime < '2001-01-13 12:01:00'
The query does not capture an actual change in value; therefore, no rows are returned.
(0 row(s) affected)
Specifying the End Date with "<="
If the end date is specified using <= (less than or equal to) then the last row returned is the last valid change up to, and including, the end date. If the end date uses "<=" then the last change returned may have a date/time exactly at the end date. If there is a value exactly at the end date, it will be returned. This query uses the remote table view.
SELECT DateTime, Value, Quality FROM History
WHERE TagName = 'SysTimeMin' AND wwRetrievalMode = 'Delta'
AND DateTime > '2001-01-13 12:00:30' AND DateTime <= '2001-01-13 12:10:00'
Note that there is a valid change at exactly the end time of the query (12:10:00):
2001-01-13 12:06:00.000 6.0 0
2001-01-13 12:07:00.000 7.0 0
2001-01-13 12:08:00.000 8.0 0
2001-01-13 12:09:00.000 9.0 0
DateTime Value Quality
DateTime Value Quality
2001-01-13 12:01:00.000 1.0 0 2001-01-13 12:02:00.000 2.0 0 2001-01-13 12:03:00.000 3.0 0 2001-01-13 12:04:00.000 4.0 0 2001-01-13 12:05:00.000 5.0 0 2001-01-13 12:06:00.000 6.0 0 2001-01-13 12:07:00.000 7.0 0 2001-01-13 12:08:00.000 8.0 0 2001-01-13 12:09:00.000 9.0 0 2001-01-13 12:10:00.000 10.0 0
(10 row(s) affected)
Specifying the End Date with "<"
If the end date is specified using < (less than), then the last row returned is the last valid change up to (but not including) the end date. If the end date uses "<" then the last event returned will have a date/time less than the end date. If there is an event exactly at the end date, it will not be returned.
This query uses the remote table view.
SELECT DateTime, Value, Quality FROM History
WHERE TagName = 'SysTimeMin' AND wwRetrievalMode = 'Delta'
AND DateTime > '2001-01-13 12:00:30' AND DateTime < '2001-01-13 12:10:00'
Note that there is a valid change at exactly the end time of the query (12:10:00), but it is excluded from the result set.
(9 row(s) affected)