Time Series Database Cache Module by Automation Professionals
E-Mail Support

Dataset / timeSeriesCache

Query a table or view for time series data within a timestamp range, immediately returning any cached data when called, and accepting push notifications as data is delivered from the data source. Optionally insert null rows where timestamps are discontinuous. Available in all scopes.

WARNING: Datasets returned by this function can be very large. When designing with this function, avoid saving the project when large datasets are present, or you will suffer from long serialization delays.

Syntax

timeSeriesCache(datasourcename, tablename, timestampcolumn, whereclause, begints, endts, [gapTolerance,] valuecolumns...) returns Dataset

ArgumentData TypeDescription
datasourcenameStringProperly capitalized data source name to use for the background queries.
tablenameStringProperly capitalized table name or view name. May specify a schema if needed.
timestampcolumnStringColumn name that has the timestamps. This must be indexed or the cache will choke on large tables.
whereclauseString

Optional WHERE clause to single out specific rows when multiple units are stored together in one table. Provide an empty string if not needed.

Different WHERE clauses are cached separately. Consistent case, whitespace, and internal quoting are significant. Columns referenced in the WHERE clause should be indexed with the timestamp column for best performance.

begintsDateQuery start timestamp. Must not be null.
endtsDateQuery end timestamp. Must not be null.
gapToleranceLongOptional, Milliseconds. Insert a null row when consecutive timestamps are further apart than this tolerance.
valuecolumnsString/Dataset...One or more String or Dataset arguments, in any combination, defining the value columns to be delivered. When datasets are supplied, each dataset's first column must be of type String, and is expected to contain column names. (The same format as delivered by the split() function.)

Usage Notes

The background queries are broken up into chunks (using a configurable LIMIT clause) to minimize the impact of any one cache on other caches or other uses of the data source. This also provides visible progress to clients as the chunks of data are delivered. The gateway cache will retain data as long as gateway functions are requesting it, or a client requests it, plus plus a configurable timeout. The timeout is cut short if all cache registration handles are released.

Cache data transferred from the gateway to a Vision client will be tracked in detail so that overlapping requests from that client do not have to report the same data. Each Vision client will maintain its own cache with its own timeout. Thus, a client may still have data in cache after the gateway has discarded it.

Timestamps near or after now() are handled by separate tasks in a separate execution manager in the background query engine. Time spans that overlap now() are split into a bulk query, cut off at now(), which executes with the limit clauses described, and a realtime query, which is limited to now() +/- a configurable window. Splitting these queries allows real-time data to quickly show on a chart while bulk data is drawn at a lower priority.

The configurable window around now() allows the cache to tolerate modest clock skew between data collection, delivery, and display. Warning: due to this design, queries for time spans in the future will always return no rows.

The Dataset returned is actually a supporting subclass: SeriesFragment. It carries additional properties that will report the actually completed query data, the raw data creation time, and cache expiration timestamp.

When using this function to supply data to a classic chart, select "Discontinuous Lines" in the XY Renderer section of the Dataset settings. This will hide stray lines while data is arriving from the gateway. Also consider overriding the x-axis auto-range function. For example, if such a chart is driven from a Date Range component, you might use a property change event script like:

if event.propertyName in ('startDate', 'endDate'):
    chart = event.source.parent.getComponent('Chart')
    config = chart.getXAxes().get("Default X Axis")
	config.minimumDate = event.source.startDate
	config.maximumDate = event.source.endDate
    axis = chart.getChart().getXYPlot().getDomainAxis()
    axis.autoRange = False
    axis.setRange(event.source.startDate, event.source.endDate)