Data Query Language (DQL)

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

A DQL expression consists of conditions and their parameters.

In general, a DQL expression has 2 possible forms:

1. Retrieving data received from the device

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

2. Retrieve data written by the virtual device. Used in specific cases, will be discussed in a separate document.

[GET] virtual

Whereby:

• uSING, GET, WHERE, AS keywords are informational only and may be omitted,
• case insensitive.

Report specification

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

Definition of selection

• project name - retrieve data tagged with project name equal to name
• status n - retrieve data for which the device had a status equal to n
• eui ident ifier - retrieving data for a device with identifier identifier
• group identifier - getting data concerning devices belonging to the group with identifier identifier
• channel definition - retrieval of measurements with names contained in the definition field - names separated by commas or * sign meaning all names of measurements for the given source
• notnull - discard records (measurements with the same timestamp) that do not have complete values (see channel definition)
• deltas - retrieve 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 the specified data
• average n [new v] - retrieve the average value calculated from the last n measurements, optionally including the additional value v
• minimum n [new v] - download the minimum value from the last n measurements, optionally taking into account the additional value v
• maximum n [new v] - get the maximum value from the last n measurements, optionally taking into account the additional value v
• from {d1} [to {d2}] - from the time point defined by {d1}, to the time point defined by {d1}.
• sback n - include data recorded up to n seconds earlier
• ascending - sort the result according toascending measurement date
• descending - sort the result according to decreasing measurement date
• deltas - get increments of measurement values
• interval n intervalName - download data for the n most recent time intervals with intervalName
• zoneTime zone - take into account the time zoneTime zone when specifying time points (e.g. Europe/Warsaw, UTC). By default, the UTC time zone is used.
• gapfill - fill in gaps in the measurement data that have no value for the time interval. If the measurement value is missing, the value recorded in the previous time interval will be returned.

IntervalName interval names are defined in the Signomix server configuration. By default, the available ones are:

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

The sback parameter is taken into account when retrieving the last group measurements. If it is not specified, data recorded up to one hour earlier are taken into account. This parameter allows you to discard data from sources that have not sent data recently (e.g., they stopped working, but there is historical data that should not be presented in the report).

Definition of time points {d1, d2}:

• date written in the format yyyy-MM-dd'T'HH:mm:ss.SSSX or yyyy-MM-dd'T'HH:mm:ssX or as Unix time (https://en.wikipedia.org/wiki/Unix_time) (can also be given as number of milliseconds)
• -Xd - X days backward from the current time - THIS DOES NOT WORK
• -0d-TimeZone - from the beginning of the current day in the specified time zone
• -0d-UTC from the beginning of the day in the UTC zone
• -0M-Time Zone-from the beginning of the current month in the specified time zone
• -0M-UTC from the beginning of the current month in the UTC zone
• -Xh - X hours backward from the current moment
• -Xm - X minutes backward from the current moment

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 can be different for different reports. In the case of the IntervalReport, an operation is available to multiply a measurement value by a value taken from another source.

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

Example: retrieve the incremental values (every 30 days) of the counter measurement from the WATERMETTER device and multiply them by the value of the water measurement (price of 1m3 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 download data in CSV or HTML format. In this case, you can select the data format by adding the format keyword and format name to the DQL expression.

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