Advanced Time Series Data Processing with InDriver

Time series data refers to a sequence of data points collected or recorded at successive time intervals. These data points typically measure the same variable (such as temperature, sales figures, or stock prices) over some time, allowing for the analysis of patterns, and trends, and forecasting future values based on historical data.

Example of data points

By collecting data every minute from a smart energy meter, the time series can include multiple values for each timestamp, like it is shown below:

2024-02-21 09:55:00

{
"power1": 312.8,
"power2": 281.56,
"power3": 10.16,
"voltage1": 238.72,
"voltage2": 240.94,
"voltage3": 240.05,
"current1": 1.42,
"current2": 1.46,
"current3": 0.16,
"energy1": 1699355,
"energy2": 1734052.1,
"energy3": 542968.2,
"energy_total": 3976375.3,
"power_total": 604.45
}

2024-02-21 09:56:00

{
"power1": 322.8,
"power2": 241.5,
"power3": 12.16,
"voltage1": 238.12,
"voltage2": 239.76,
"voltage3": 224.67,
"current1": 1.50,
"current2": 1.46,
"current3": 0.20,
"energy1": 1699357,
"energy2": 1734058.3,
"energy3": 542969.3,
"energy_total": 3976395.3,
"power_total": 614.25
}

2024-02-21 09:57:00

{
"power1": 334.5,
"power2": 234.78,
"power3": 11.12,
"voltage1": 236.32,
"voltage2": 245.64,
"voltage3": 238,01,
"current1": 1.34,
"current2": 1.56,
"current3": 0.44,
"energy1": 1699359,
"energy2": 1734064.5,
"energy3": 542971.3,
"energy_total": 3976440.1,
"power_total": 623.45
}

2024-02-21 09:58:00

{
"power1": 362.8,
"power2": 241.56,
"power3": 15.16,
"voltage1": 234.34,
"voltage2": 241.56,
"voltage3": 242.03,
"current1": 1.99,
"current2": 1.87,
"current3": 0.14,
"energy1": 1699373,
"energy2": 1734066.3,
"energy3": 542975.2,
"energy_total": 3976495.8,
"power_total": 620.76
}

2024-02-21 09:59:00

{
"power1": 342.8,
"power2": 251.56,
"power3": 16.16,
"voltage1": 248.72,
"voltage2": 249.94,
"voltage3": 248.05,
"current1": 1.44,
"current2": 1.45,
"current3": 0.17,
"energy1": 1699389,
"energy2": 1734077.6,
"energy3": 542999.2,
"energy_total": 3976489.7,
"power_total": 601.51
}

power_total

Selected variables, such as eg. 'power_total', can be easily charted and used in further calculations.

Universal SQL table structure

By utilizing a JSON data column, each data point can comprise multiple, structured values, making this a versatile solution for collecting a wide variety of data.

Efficient Time Series Data Management with InDriver:

Key Features Explained

JSON

InDriver streamlines the collection of time series data from various sources.

It features Device Read/Write functions, as well as REST API and SQL Query execution functions, which return JSON objects. These objects can be easily processed (modified, trimmed, or expanded) and reused as input.

The InDriver onHook script example demonstrates a REST API request that returns JSON, used as input for the sqlExecute function.

This script, running at set intervals, fetches and logs data to a time series SQL table with ease.

let ts= InDriver.hookTs();

RestApi.sendRequest('shelly');

if (RestApi.isSucceeded()) {

const json = RestApi.getData('shelly');

const jsonObj = JSON.parse(json);

InDriver.sqlExecute("azureserver",

"insert into public.shelly (source, ts, data ) values ('Shelly','"+ts.toISOString()+"',$$"+json+"$$);");

}

24/7

Uninterrupted Stability

InDriver tasks guarantee continuous and stable data collection, without any disruptions or data loss. Connections to both sources and destinations are constantly monitored, and automatic reconnections are triggered in case of failures.

Clock

Time-Synchronized Data Collection

Data is collected at exact intervals, ensuring synchronization with the clock. This includes precise collection at full seconds, minutes, quarter-hours, hours, or days, all aligned with local time.

//call onHook script every full 10s, eg. 00:00:00, 00:00:10, 00:00:20...

InDriver.installHook(10000)

//call onHook script every full hour, eg. 00:00:00, 01:00:00, 02:00:00...

InDriver.installHook(3600000)

Real-Time Availability

With efficient, low-level thread implementations, InDriver tasks achieve near real-time execution. The histogram below displays the measured latency between the real-time clock and the expected hook timestamp.

Synchronization

Synchronized Sources

Data collection occurs simultaneously across all sources, eliminating delays or discrepancies.

The example below showcases a 'begin...commit' code block, within which all 'readDevice' functions are executed simultaneously, guaranteeing synchronized device data collection and logging.

ModbusApi.begin()

// Execute the following read function simultaneously after commitWait()

ModbusApi.readDevice( 'MoxaOne','{"name": "inputs", "type": "DISCRETEINPUTS", "address":1, "size":8}')

ModbusApi.readDevice( 'MoxaTwo','{"name": "inputs", "type": "DISCRETEINPUTS", "address":1, "size":8}')

ModbusApi.readDevice( 'MoxaThree','{"name": "inputs", "type": "DISCRETEINPUTS", "address":1, "size":8}')

ModbusApi.commitWait()

if (ModbusApi.isSucceeded()) {

let data = ModbusApi.getAllData()

let ts = InDriver.hookTs()

InDriver.sqlExecute("AzurePGSQL",["select tsapiinsert('public','modbus','3xIOLogicE1212', '", ts.toUTCString(), "','", data, "' );"])

InDriver.sendMessage(ts, '["device data","arch"]', data)

}

Completion

Aggregation

Built-In Aggregation Algorithm

InDriver's built-in aggregation algorithm ensures that any missing data, potentially lost due to network disruptions, server failures, or device unavailability, is seamlessly interpolated at precise, clock-synchronized timestamps. This high-availability feature guarantees that data remains continuous, time-synchronized, and neatly organized into intervals. The algorithm facilitates aggregation at common intervals—such as 1 minute, 15 minutes, 1 hour, and 1 day—generating separate tables that not only include interpolated values but also provide aggregated statistics like value, minimum, maximum, average, delta, and delta over one hour.

This entire process is efficiently executed with just a single call to the InDriver TSApi.

onStartup

InDriver.import("TsApi");

//set aggregation interval as 10 minutes

InDriver.installHook(600000)

// Define aggregator for 'shelly' table on 'azureserver' SQL server

// Time zone: 'Europe/Warsaw'

// Source: 'Shelly' time series data

TsApi.defineAggregator("AGG","azureserver","shelly","Europe/Warsaw",'["Shelly"]');

onHook

// onHook called every 10 minutes to aggregate new values

TsApi.aggregate("AGG");

JavaScript

Custom Algorithms

Leveraging your understanding of JavaScript basics, you can easily develop bespoke algorithms for data acquisition and processing. Our comprehensive library of pre-built functions simplifies your workflow, enabling you to achieve your objectives with minimal code.

Below is a concise code snippet demonstrating how to extract values from JSON data collected from a smart meter:

let ts= InDriver.hookTs();

RestApi.sendRequest('shelly');

if (RestApi.isSucceeded()) {

const json = RestApi.getData('shelly');

const jsonObj = JSON.parse(text);

//Extract energy and power values for each phase

let energy1 = Math.abs(jsonObj .data.device_status.emeters[0].total_returned);

let energy2 = Math.abs(jsonObj .data.device_status.emeters[1].total_returned);

let energy3 = Math.abs(jsonObj .data.device_status.emeters[2].total_returned);

let power1 = Math.abs(jsonObj .data.device_status.emeters[0].power);

let power2 = Math.abs(jsonObj .data.device_status.emeters[1].power);

let power3 = Math.abs(jsonObj .data.device_status.emeters[2].power);

//Build smart meter time series data point

let data = {

power1 : power1,

power2 : power2,

power3 : power3,

voltage1 : Math.abs(jsonObj .data.device_status.emeters[0].voltage),

voltage2 : Math.abs(jsonObj .data.device_status.emeters[1].voltage),

voltage3 : Math.abs(jsonObj .data.device_status.emeters[2].voltage),

current1 : Math.abs(jsonObj .data.device_status.emeters[0].current),

current2 : Math.abs(jsonObj .data.device_status.emeters[1].current),

current3 : Math.abs(jsonObj .data.device_status.emeters[2].current),

energy1 : energy1,

energy2 : energy2,

energy3 : energy3,

energy_total: energy1 + energy2 + energy3,

power_total: power1 + power2 + power3

}

let shelly = {Shelly:data};

let list = [];

list.push(shelly);

// Ensure data changes are detected. This smart meter sends updates irregularly, often repeating values in subsequent requests.

let currentJSON = JSON.stringify(list);

if (lastJSON !=currentJSON) {

// Log JSON object with extracted values to database

InDriver.sqlExecute("azureserver", "insert into public.shelly (source, ts, data ) values ('Shelly','"+ts.toISOString()+"',$$"+currentJSON+"$$);");

lastJSON = currentJSON;

InDriver.debug(currentJSON)

}

Real Example - Smart Meter Data Acquisition

This example illustrates the comprehensive use case of InDriver for time series data acquisition, processing, interpolation, aggregation, and logging. It demonstrates how InDriver facilitates seamless integration and manipulation of data streams, ensuring efficient handling of time series data from initial collection to final storage, all while maintaining data integrity and providing valuable insights through statistical analysis.

Learn more