Data Query Language (DQL)

Signomix provides a Data Query Language (DQL) that allows users to define the type and scope of data displayed by dashboard controls and retrieved via the API.

A DQL expression consists of conditions and their parameters.

Generally, a DQL expression can take two forms:

1. Retrieving data from a device

[USING] {report specification} [WHERE] {selection definition} [GET] {data range definition} [{operation definition}] [[AS] {format definition}]

2. Retrieving data recorded by a virtual device. Used in specific cases; this will be discussed in a separate document.

[GET] virtual

Note that:

• the keywords USING, GET, WHERE, and AS are for informational purposes only and may be omitted;
• case is not significant.

Report Specification

• report className — where className is the name of the Java class that implements the report logic (see: Report Server)
• class className – same as report className

Selection definition

• project name - retrieves data tagged with a project name equal to name
• status n - retrieve data for which the device had a status equal to n
• eui identifier - retrieve data for the device with identifier identifier
• group identifier - retrieve data for devices belonging to the group with identifier identifier
• channel definition - retrieve measurements with names contained in the definition field - names separated by commas or the * character, which denotes all measurement names for a given source
• notnull - discard records (measurements with the same timestamp) that do not have a complete set of values (see: channel definition)
• deltas - retrieve the increments of measurement values

Examples:

project test
status 1
project test status 1
eui 010203040506 channel *
eui 010203040506 channel temperature,humidity

Data range definition

• limit n – retrieve the last n values of a specific data point
• average n [new v] - retrieve the average value calculated from the last n measurements, optionally including an additional value v
• minimum n [new v] – retrieves the minimum value from the last n measurements, optionally including an additional value v
• maximum n [new v] – retrieves the maximum value from the last n measurements, optionally including an additional value v
• from {d1} [to {d2}] - from the time point defined by {d1} to the time point defined by {d2}.
• sback n — include data recorded up to n seconds earlier
• ascending - sort the result by ascending measurement date
• descending - sort the result by descending measurement date
• deltas - retrieve the increments in measurement values
• interval n intervalName - retrieve data for the last n time intervals named intervalName
• zone timeZone - take the timeZone into account when determining timestamps (e.g., Europe/Warsaw, UTC). By default, the UTC time zone is used.
• gapfill - fill in gaps in measurement data that lack values for a given time interval. If a measurement value is missing, the value recorded in the previous time interval will be returned.

The names of the time intervals ( intervalName ) are defined in the Signomix server configuration. The following are available by default:

• second – a time interval with a duration of 1 second
• minute - a time interval of 1 minute
• hour – a time interval lasting 1 hour
• day – a time interval lasting 1 day
• week – a time interval lasting 1 week
• month - a time interval of 1 month
• quarter - a time interval lasting 3 months
• year - a time interval lasting 1 year

The sback parameter is taken into account when retrieving the latest measurements for a group. If it is not specified, data recorded up to one hour earlier is used. This parameter allows you to exclude data from sources that have not sent data recently (e.g., sources that have stopped working but have historical data that should not be included in the report).

Definition of time points {d1, d2}:

• a date saved in the format yyyy-MM-dd'T'HH:mm:ss.SSSXoryyyy-MM-dd'T'HH:mm:ssXor as Unix time (https://en.wikipedia.org/wiki/Unix_time) (may also be specified as a number of milliseconds)
• -Xd - X days back from the current time - THIS DOES NOT WORK
• -0d-TimeZone - from the start of the current day in the specified time zone
• -0d-UTC from the start of the day in the UTC time zone
• -0M-TimeZone from the start of the current month in the specified time zone
• -0M-TimeZone from the start of the current month in the specified time zone
• -Xh - X hours back from the current time
• -Xm - X minutes back from the current time

Examples:

get last 10
last 10
from -2h to -1h
from 2023-03-10T00:00:00Z to 2023-03-10T12:00:00Z
from 2023-03-23T00:05:00~01:00 to 2023-03-23T00:07:00~01:00
from 1679363129 to 1679395529
from -0M-Europe_Warsaw
from -0M-UTC
from -0d-Europe/Warsaw to -0m

Operation Definition

Selected reports may support additional operations that are performed on the data before it is returned. These operations are defined in the report specification and may vary from report to report. For the IntervalReport, an operation is available to multiply measurement values by a value retrieved from another source.

• mpy - name of the multiplier (measurement) by which the measurement values returned by the report are to be multiplied
• mpyeui – the identifier of the device from which the multiplier value is to be retrieved

Example: Retrieving the value increments (every 30 days) of the "counter" measurement from the "WATERMETTER " device and multiplying them by the "water" measurement value (price per 1 m³ of water) stored in the "PRICES" data source:

report IntervalReport eui WATERMETTER channel counter interval 30 day mpy water mpyEui PRICES

Format Definition

The default data format is JSON. For selected reports, it is also possible to retrieve data in CSV or HTML format. In this case, you can select the data format by adding the keyword `format ` and the format name to the DQL expression.

• format json - data in JSON format (default)
• format csv - data in CSV format
• format html - data in HTML format