Time Series Data

It is possible to run queries that analyse time series of data obtained from TSD streams that are linked to objects in the network. This is achieved using time series expressions as described in detail in Theory of time series expressions.

Differences between time series data and time series results

The time series expressions approach to analysing time series data is very different from the approach taken for analysing time series results. In particular:

  • In time series expressions, each value is an entire (finite) time series and operators and functions act on whole series. For time series results, expressions are evaluated at each time step using operators and functions that act on individual result values for that time step.
  • In time series expressions, the different time series can have different time steps. For time series results, all results share the same time steps.
  • Time series expressions always work with absolute times in local clock time (i.e. including daylight saving). Time series results may use relative times or absolute times in local standard time (i.e. excluding daylight saving).
  • Time series expressions can construct new time series as intermediate values stored in variables, spreading the calculation over multiple semi-colon-separated clauses. For time series results, all calculations occur in the context of an aggregating function and must be carried using a single (possibly large) expression that is the argument of the aggregating function.
  • Time series expressions aggregate time series using the TSAGGREGATE function, passing in the name of the desired statistic (MIN, MAX, etc.). Time series results are aggregated using separate functions for each statistic (MIN, MAX, etc.)
  • Time series expressions load all the required time-series data for all network objects into memory at the same time. This is not a problem for typical analysis but could be a limitation for very dense and/or long-period data. Time series results are loaded for all objects for a single time step at a time.

Loading and aggregating time series data

The two basic operations that are required for use of time series data in InfoAsset Manager are loading of a time series (from a TSD data stream) and aggregation of a time series (to obtain a single number that can then be displayed or used in other calculations). In between, there can be any number of time series expressions to combine and transform the loaded data.

Data loading is done by the TSLOAD function, which loads data from one of the TSD streams listed in the time series links associated with an object. The link to use is identified by its description and the start and end times to load are specified explicitly. The units of measurement must also be specified explicitly. For example, if the object has a time series link named FFT that points to a TSD stream containing flow data, then data for the past week can be loaded into a variable $flow by the statement:

SET $now = NOW(); get the current time

SET $flow = TSLOAD("FFT", "m/s",$now-7,$now);

Aggregation is done by the TSAGGREGATE function, which calculates a specified statistic over a specified time period. For example, to find the maximum flow during the past day for the data loaded above:

SET $maxflow = TSAGGREGATE($flow, $now-1, $now, "MAX", 1);

A more complex and complete example would be:

Choose a period to analyse :

LET $end = NOW();

SET $start = $end - 7;

Load H2S data for this period :

SET $h2s = TSLOAD("H2S", "mg/l", $start, $end);

Get maximum H2S and time of maximum

SET $maxh2s = TSAGGREGATE($h2s, $start, $end, "max", 1.0);

SET $maxtimeh2s = TSAGGREGATE($h2s, $start, $end, "maxtime", 1.0);

We want to look at what was happening in the 3 hours before the peak H2S

SET $maxtimeh2sminus3 = $maxtimeh2s - 3.0/24.0;

Load flow data for this period and calculate the mean:

SET $prevfft = TSLOAD("FFT", "l/s", $maxtimeh2sminus3 , $maxtimeh2s);

SET $prevfftmean = TSAGGREGATE($prevfft, $maxtimeh2sminus3 , $maxtimeh2s, "mean", 1.0);

Present the results for all objects that have links to H2S data:

SELECT asset_id, DATETIMEFORMAT($maxtimeh2s ,"dd/MM/yyyy", "HH:mm") AS 'Time of Max H2S', $maxh2s AS 'Max H2S', $prevfftmean AS 'Mean FFT in previous 3 hours' WHERE $maxh2s IS NOT NULL;

Time series expressions can be used to combine and transform the loaded time series, as shown in the following (slightly contrived) example using time series links called A, B and C to streams that have no units:

LET $end = NOW();

SET $start = $end - 7;

SET $a = TSLOAD("A", "", $start, $end);

SET $b = TSLOAD("B", "", $start, $end);

SET $c = TSLOAD("C", "", $start, $end);

SET $d = 5.4 + $a + 2*$b*$b + POW($c, 3);

SET $maxd = TSAGGREGATE($d, $start, $end, "max", 1.0);

As can be seen, the time series expression looks identical to an equivalent expression that might be used to combine individual values from a single time step of each of the series. Because this expression includes whole time series, the calculations are instead carried out using the entire set of values within each time series, yielding a new time series. See Theory of time series expressions for more details.

Parent topic: SQL in InfoAsset Manager