home > en > development > dql.md

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}] [GET] {data range definition} [WHERE] {definition of selection}] [AS] {definition of format}]
  1. Retrieve the data stored by the virtual device. Used in specific cases, will be discussed in a separate document.
[GET] virtual

Whereby:

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

Definition of the data range

  • last 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 an additional value v
  • minimum n [new v] - get 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

The sback parameter is taken into account when retrieving the last measurements of the group. 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

Report specification

  • report className - where className is the name of the Java class implementing the report logic (see Report Server)

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 - retrieve 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 a complete value (see channel definition)

Examples:

last 10 project test last 10 status 1 last 10 project test status 1 last 3 eui 010203040506 channel * last 3 eui 010203040506 channel temperature,humidity

Format of data returned by reports

All reports return data as a JSON object.

Format definition (deprecated)

Note: The format definition is only applicable when retrieving device data using the /api/iot/device/{eui}?{query} API. It is not taken into account by desktop controls.

  • timeseries - use of simplified JSON format
  • csv.timeseries - data in CSV format

Example of data returned by a query without format definition:

last 2 [ [ { "deviceEUI": "020305", "name": "temperature", "value":30.0, "timestamp":1679526300000, "stringValue":null }, { "deviceEUI": "020305", "name": "temperature", "value":30.0, "timestamp":1679526411476, "stringValue":null } ] ]

Example of simplified JSON format:

last 2 timeseries get last 2 as timeseries [ [ [ [ "timestamp", "temperature", "humidity" ], [ 1679526411476, 30.0, 46.0 ], [ 1679526300000, 30.0, 46.0 ] ] ] ]

CSV format example:

last 2 csv.timeseries timestamp,temperature,humidity 1679526411476,30.0,46.0 1679526300000,30.0,46.0
© 2023-2025 Grzegorz Skorupa