Working with Time Series Data
This page describes how to write and read time series data with the PowerTSM .NET API and how to group operations into transactions. It starts with an overview of the dimensions in which a time series can store data.
Time Series Dimensions
Section titled “Time Series Dimensions”In PowerTSM a time series can store time series data on up to 3 time dimensions:
- Data dimension: persisting current values
- History dimension: persisting changed / historized values
- Quotation dimension: persisting versions
Each of these three dimensions can be used in any time series data operation, including time series data queries via formula references.
See Audit and Historization for more about the possibilities with time series dimensions.
Writing Data
Section titled “Writing Data”Time series data can be saved and updated by using the .NET API. In order to store data on a time series it has to be created first - see What Is a Time Series?.
Create a time series with advanced options
Section titled “ Create a time series with advanced options”using System;using HAKOM.TSM.Net.Api;using HAKOM.TSM.Net.Api.Compression;using HAKOM.TSM.Net.Api.Quotation;using HAKOM.TSM.Net.Api.Time.Intervals;using HAKOM.TSM.Net.Api.TimeSeries;
var tsm = ITsm.Create();var repository = await tsm.Repositories.Load("TSM_Example_Repository");var unitStore = await repository.GetUnitStore();
var interval = tsm.Intervals.Create(IntervalType.Hour(), 1);var unit = unitStore.References.Create("W");var timeZone = tsm.TimeZones.CreateFromId("W. Europe Standard Time");var startOfTime = new DateTime(2010, 10, 1, 6, 0, 0);
var timeSeries = tsm.TimeSeries.Create( name: "TSM_Example_TimeSeries", interval: interval, cyclic: true, function: FunctionType.StepRight(), unit: unit, description: "A Description which explains what this TimeSeries is", formula: null, timeZone: timeZone, intervalTransformationMethod: IntervalTransformationMethod.Sum(), startOfTime: new DateTimeOffset(startOfTime, timeZone.TimeZoneInfo.GetUtcOffset(startOfTime)), preserveDuplicatedValues: true, preserveHistoricData: true, quotation: true, quotationDefaultBehavior: QuotationBehavior.MaxExact(), compressionMode: CompressionMode.Base64BlockCompression(), storage: null, dataStorage: null);
var timeSeriesStore = await repository.GetTimeSeriesStore();await timeSeriesStore.Save(timeSeries);Write data to a time series
Section titled “ Write data to a time series”using System;using System.Collections.Generic;using HAKOM.TSM.Net.Api;using HAKOM.TSM.Net.Api.Data.TimeSeries;using HAKOM.TSM.Net.Api.Flags;using HAKOM.TSM.Net.Api.Time.Intervals;using HAKOM.TSM.Net.Api.TimeSeries;
var tsm = ITsm.Create();var repository = await tsm.Repositories.Load("TSM_Example_Repository");var unitStore = await repository.GetUnitStore();var timeSeriesStore = await repository.GetTimeSeriesStore();var timeSeries = await timeSeriesStore.Load("TSM_Example_TimeSeries");
var timeSeriesData = tsm.TimeSeriesData.CreateCollection( tsm.Intervals.Create(IntervalType.Month(), 1), false, FunctionType.StepRight(), unitStore.References.Create("W"), tsm.TimeZones.CreateFromId("W. Europe Standard Time"), new List<ITimeSeriesData<double>> { tsm.TimeSeriesData.Create<double>(new DateTimeOffset(2022, 1, 1, 0, 0, 0, 0, TimeSpan.FromHours(2)), 550, Flag.Estimated()), tsm.TimeSeriesData.Create<double>(new DateTimeOffset(2022, 7, 1, 0, 0, 0, 0, TimeSpan.FromHours(1)), 450.5, Flag.Valid()), tsm.TimeSeriesData.Create<double>(new DateTimeOffset(2022, 8, 1, 0, 0, 0, 0, TimeSpan.FromHours(1)), 475.75, Flag.Valid()) });var timeSeriesDataStore = await repository.GetTimeSeriesDataStore(timeSeries);await timeSeriesDataStore.Write(timeSeriesData);The data to be saved may have a different format, time zone, interval and even unit than the time series, since it will be converted before the final saving.
The IsWithinCutoff() extension method checks a timestamp beforehand:
Check a timestamp before writing
Section titled “ Check a timestamp before writing”using HAKOM.TSM.Net.Api.Time.Extensions;
if (!timestamp.IsWithinCutoff()){ // handle the invalid timestamp before writing}Reading Data
Section titled “Reading Data”Time series data can be read with the .NET API. There are several options available when reading time series data such as period, interval, unit and more.
using System;using System.Linq;using HAKOM.TSM.Net.Api;using HAKOM.TSM.Net.Api.Time.Periods;
var tsm = ITsm.Create();var repository = await tsm.Repositories.Load("TSM_Example_Repository");var timeSeriesStore = await repository.GetTimeSeriesStore();var timeSeries = await timeSeriesStore.Load("TSM_Example_TimeSeries")).FirstAsync();var timeSeriesDataStore = await repository.GetTimeSeriesDataStore(timeSeries);
var from = new DateTimeOffset(2022, 6, 4, 0, 0, 0, 0, TimeSpan.Zero);var to = new DateTimeOffset(2022, 12, 4, 0, 0, 0, 0, TimeSpan.Zero);
await foreach (var point in await timeSeriesDataStore.Read<double>(Period.Create(from, to))){ // use data}Reading in a Different Interval
Section titled “Reading in a Different Interval”Time series data can be read in an interval different from that of the time series. Let’s assume that there is a time series with an hourly interval whose data should be read out in a daily interval. The interval transformation is done according to the interval transformation method of the time series unit, but can also be changed within the reading options.
var readingOptions = timeSeriesDataStore.ReadingOptions.Create( new DateTimeOffset(2022, 6, 4, 0, 0, 0, 0, TimeSpan.Zero), new DateTimeOffset(2022, 12, 4, 0, 0, 0, 0, TimeSpan.Zero), interval: tsm.Intervals.Create(IntervalType.Day()));
await foreach (var point in await timeSeriesDataStore.Read<double>(readingOptions)){ // use data}Reading in a Different Format
Section titled “Reading in a Different Format”It is possible to specify the desired format of the data. A time series e.g. may store the data acyclic with function type StepRight. But if it is needed to retrieve the data e.g. cyclic with function type StepLeft, it is possible to specify that in the reading options.
var readingOptions = timeSeriesDataStore.ReadingOptions.Create( new DateTimeOffset(2022, 6, 4, 0, 0, 0, 0, TimeSpan.Zero), new DateTimeOffset(2022, 12, 4, 0, 0, 0, 0, TimeSpan.Zero), cyclic: true, function: FunctionType.StepLeft());
await foreach (var point in await timeSeriesDataStore.Read<double>(readingOptions)){ // use data}Reading in a Different Unit
Section titled “Reading in a Different Unit”Time series data can be read in a specified unit which may be provided within the given reading options. This way a time series e.g. that stores its data in Watt may be read in Kilowatt.
var readingOptions = timeSeriesDataStore.ReadingOptions.Create( new DateTimeOffset(2022, 6, 4, 0, 0, 0, 0, TimeSpan.Zero), new DateTimeOffset(2022, 12, 4, 0, 0, 0, 0, TimeSpan.Zero), unit: unitStore.References.Create("kW"));
await foreach (var point in await timeSeriesDataStore.Read<double>(readingOptions)){ // use data}Reading Historical Data
Section titled “Reading Historical Data”Time series data can be archived onto a certain date. In order to read data from a previous point of time a historic date may be provided in the reading options.
using HAKOM.TSM.Net.Api.Time.DateTimes;
var historicDateExactlyAt = DateTimeOffsetAtOrBefore.Create(new DateTimeOffset(2022, 12, 23, 0, 0, 0, 0, TimeSpan.Zero), true);var readingOptions = timeSeriesDataStore.ReadingOptions.Create( new DateTimeOffset(2022, 6, 4, 0, 0, 0, 0, TimeSpan.Zero), new DateTimeOffset(2022, 12, 4, 0, 0, 0, 0, TimeSpan.Zero), historicDate: historicDateExactlyAt);
await foreach (var point in await timeSeriesDataStore.Read<double>(readingOptions)){ // use data}Transactions and Isolation Levels
Section titled “Transactions and Isolation Levels”Several .NET API operations — for example writing data, historizing, or deleting a time series — can be grouped into a single atomic unit of work with a standard .NET System.Transactions.TransactionScope. Every operation inside the scope is committed together when scope.Complete() is called, or rolled back if the scope is disposed without completing.
Group operations in a transaction scope
Section titled “ Group operations in a transaction scope”using System.Transactions;
using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)){ await timeSeriesDataStore.Write(timeSeriesData); // ... further operations that must succeed or fail together ... scope.Complete();}Set the isolation level explicitly
Section titled “ Set the isolation level explicitly”using System.Transactions;
var transactionOptions = new TransactionOptions{ IsolationLevel = IsolationLevel.RepeatableRead};
using (var scope = new TransactionScope( TransactionScopeOption.Required, transactionOptions, TransactionScopeAsyncFlowOption.Enabled)){ await timeSeriesDataStore.Write(timeSeriesData); scope.Complete();}Retry on deadlock
Section titled “ Retry on deadlock”Even with an explicit isolation level a deadlock can still occur — and under the Serializable default it is more likely. As Microsoft itself recommends, an application should be prepared to rerun a transaction that was chosen as the deadlock victim. Wrap the unit of work in a bounded retry loop and retry only on transient failures:
const int maxAttempts = 3;
for (var attempt = 1; attempt <= maxAttempts; attempt++){ try { using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
await timeSeriesDataStore.Write(timeSeriesData); scope.Complete(); break; // success } catch (Exception) when (attempt < maxAttempts) { // Transient failure such as a SQL Server deadlock (error 1205): // wait briefly and retry the whole transaction. On the final attempt the exception is rethrown. await Task.Delay(TimeSpan.FromMilliseconds(100 * attempt)); }}