wwtools/histdb/03-retrieval-modes.md

Retrieval modes (wwRetrievalMode)

Every history query has a retrieval mode. The default is Cyclic for analog tables (and analog/state summary tables) and Delta for History, discrete, and string tables. Set explicitly with AND wwRetrievalMode = '<Mode>' (case-insensitive).

At-a-glance picker

Want…	Use
Evenly spaced points across a window, fast	Cyclic
Smooth curves (analog) over a window	Interpolated
Only the changes — no duplicates	Delta
Every stored point, including duplicates	Full
A compromise: cyclic perf + delta accuracy	BestFit
Time-weighted average per cycle	Average
Min / max value per cycle	Minimum / Maximum
Area under the curve per cycle	Integral
Slope (units / time) per cycle	Slope
Throughput / accumulator (handles wraps + resets)	Counter
"How long was the tag in state X"	ValueState
"How long between consecutive entries into state X"	RoundTrip
Forecast points using simple linear regression	Predictive (a wwFilter mode, not a wwRetrievalMode)
Retrieve bracketing values just outside the window	BoundingValue

Mode catalog

Each section below covers: how the mode picks rows, which wwXxx options apply to it, the typical query shape, and the gotchas.

Cyclic — evenly spaced cycle boundaries

• One row per cycle boundary (last value at or before the boundary).
• Number of cycles set by wwCycleCount or wwResolution. Default 100 cycles when neither is set.
• Fast and cheap. May miss spikes/gaps that fall between boundaries — for that use BestFit.
• Default mode for analog and analog-summary tables.
• Applies to all tag types.
• Options: wwCycleCount, wwResolution, wwVersion, wwTimeStampRule, wwQualityRule.

SELECT DateTime, TagName, Value
FROM History
WHERE TagName = 'ReactLevel'
  AND DateTime >= '2001-03-13 13:15:00'
  AND DateTime <= '2001-03-13 14:15:00'
  AND wwRetrievalMode = 'Cyclic'
  AND wwCycleCount = 60;

NULLs and initial values are returned as-is — no special handling.

Delta — changes only

• Returns only physically stored points where the value (or quality) actually changed.
• If no point sits exactly on the start time, the last value before the start is returned with timestamp shifted to the start time and Quality = 133 (initial-value flag).
• A NULL after a non-NULL is always returned; consecutive NULLs are suppressed.
• Default for History, discrete, and string tables.
• Applies to all tag types.
• Options: wwTimeDeadband, wwValueDeadband, wwVersion, wwQualityRule.

SELECT TagName, DateTime, Value, QualityDetail
FROM History
WHERE TagName = 'A001'
  AND DateTime >= '2009-09-12 00:20'
  AND DateTime <= '2009-09-12 00:40'
  AND wwRetrievalMode = 'Delta';

>= on the start time pulls the initial value; > skips it. Same logic for < / <= on the end.

Full — every stored point

• Returns all stored rows including duplicates.
• Best fidelity to what was actually written by the I/O server.
• Heavy — long windows × many tags = giant rowsets.
• Initial-value handling matches Delta.
• Applies to all tag types.
• Options: wwVersion, wwQualityRule.

Interpolated — Cyclic, but with interpolation between actual points

• Cycle boundaries identical to Cyclic. At each boundary, the value is linearly interpolated between the surrounding stored points (or stair-stepped, if that's the tag's configured interpolation type).
• Per-tag interpolation comes from the InterpolationType column on the AnalogTag table, with system parameters InterpolationTypeInteger / InterpolationTypeReal filling in defaults. AND wwInterpolationType = 'Linear' in the query overrides everything for that query.
• A NULL preceding a cycle boundary causes a NULL at the boundary (no interpolation across NULLs).
• Useful for trend smoothness and for mixing slow + fast tags.
• Only meaningful for analog tags — others fall back to stair-step (effectively Cyclic).
• Options: wwCycleCount, wwResolution, wwVersion, wwInterpolationType, wwTimeStampRule, wwQualityRule.

BestFit — five points per cycle, picked from real data

For each cycle returns up to five points: first, last, min (with its real timestamp), max (with its real timestamp), and the first non-Good-quality "exception." A point that fills two roles (e.g. last == max) is returned once. Cycles with no data return nothing. Initial and final values at the query boundaries are interpolated.

Combines cyclic-style cost with delta-style fidelity. One week of 5-second data is ~120,960 rows in Delta, ~300 rows in BestFit. Use it for long-window trends.

• Only applied to analog and analog-summary tags. Other tags get plain Delta.
• Options: wwCycleCount, wwResolution, wwVersion, wwInterpolationType, wwQualityRule.
• Quality detail of points returned from a cycle that contains a gap or an incomplete final cycle gets the 4096 "partial cycle" bit OR'd into QualityDetail.

Average — time-weighted average per cycle

• One row per tag per cycle.
• For tags with linear interpolation, midpoints between stored points are weighted by elapsed time.
• For tags with stair-step interpolation, the earlier of two values carries the full interval.
• Differs from SQL AVG(), which is unweighted; the Historian's time-weighted average is generally more accurate for sparse data.
• Gaps (NULL ranges) are excluded from the calculation; the output column TimeGood reports total good time per cycle.
• Initial / final values: with wwTimeStampRule='END' the initial value is the average over the cycle leading up to the query start; with START, the final value covers the cycle after the query end.
• Only applies to analog and analog-summary tags.
• Options: wwCycleCount, wwResolution, wwVersion, wwInterpolationType, wwTimeStampRule, wwQualityRule.

Minimum / Maximum — per-cycle extremes

• Returns the minimum (or maximum) stored point inside each cycle, at its real timestamp.
• Cycles with no data return nothing. Cycles containing a NULL return NULL.
• If multiple points tie, the earliest is returned.
• QualityDetail of returned cycle-extreme points has the 4096 "partial cycle" bit set when the cycle is incomplete.
• Only applied to analog tags; others fall back to Delta.
• Options: wwCycleCount, wwResolution, wwVersion, wwTimeStampRule, wwQualityRule.

Integral — area under the curve

• Per-cycle integral of the tag value × time. Useful for things like "how many gallons" given a flow-rate tag.
• The integration uses linear or stair-step depending on the tag's configured / overridden interpolation type.
• Only applied to analog tags.
• Options: wwCycleCount, wwResolution, wwVersion, wwInterpolationType, wwTimeStampRule, wwQualityRule.

Slope — units per time per cycle

• Per-cycle slope (rate of change). Linear regression across the cycle's points.
• Only analog tags.
• Options: wwCycleCount, wwResolution, wwVersion, wwTimeStampRule, wwQualityRule.

Counter — accumulators with wrap and reset support

• For tags that monotonically increase but periodically reset (totalizers, throughput counters, manual resets).
• Returns the accumulated count per cycle, accounting for resets and configurable deadbands so brief drops aren't read as resets.
• Supports manually-reset counters and counter deadbands.
• Options: wwCycleCount, wwResolution, wwVersion, wwTimeStampRule, wwQualityRule.

ValueState — time-in-state

• Calculates how long the tag spent in each value-state during each cycle.
• The aggregation type comes from wwStateCalc: Minimum, Maximum, Average, Total, Percent (and *Contained variants).
• If you specify a single value in the query (AND Value = 'Open'), one row per cycle. If you don't, one row per (state, cycle) — table-friendly but trend-unfriendly.
• Works for integer, discrete, string, and state-summary tags. Other types return nothing.
• NULLs are themselves a state.
• Options: wwCycleCount, wwResolution, wwVersion, wwStateCalc, wwTimeStampRule, wwQualityRule.

RoundTrip — time between consecutive entries to a state

• Like ValueState but measures the time between successive leading edges of the same state — useful for cycle-time analysis ("how long between successive 'Start' events").
• wwStateCalc accepts MinContained, MaxContained, AvgContained, TotalContained, PercentContained.
• Options: wwCycleCount, wwResolution, wwVersion, wwStateCalc, wwTimeStampRule, wwQualityRule, wwEdgeDetection.
• Edge detection is configured via wwEdgeDetection (Leading, Trailing, Both) — see 04-retrieval-options.md.

Predictive — simple linear regression forecast

• Available from Historian 2014 R2 P01 onward.
• Implemented as a wwFilter mode rather than a distinct wwRetrievalMode. Future Historian releases are expected to add other prediction algorithms.
• Set AND wwFilter = '<predictive expression>' against an analog tag.

BoundingValue — bracket the window

• Available from Historian 17.3.100 onward.
• Returns the two stored points immediately surrounding the query window, in addition to whatever falls inside it.
• Use when a chart renderer or trend client needs a value to anchor the line at the query edges without interpolation.

Mixing modes per query

You can't. One wwRetrievalMode per query. To compare modes, run them as separate queries and UNION ALL.

Initial-value semantics, in one place

Mode	Initial value
Cyclic, Full, Delta	Last value before the start, timestamped at the query start. Quality=133 if shifted; if no prior value exists, NULL with Quality=1, QualityDetail=65536.
Interpolated, BestFit	Linearly interpolated between the surrounding points at the start time.
Average, Min, Max, Integral, Slope	Calculated over the cycle adjacent to the boundary defined by wwTimeStampRule.
Counter, ValueState, RoundTrip	Result of applying the algorithm to the cycle preceding the query start.

>= on the start pulls an initial value; > does not. Same for <= / < on the end.

Next

• 04-retrieval-options.md — wwCycleCount, wwResolution, wwInterpolationType, deadbands, quality, value selector, and more.
• 05-query-recipes.md — worked examples that combine modes and options.