Time series query structure

To access the time-series data, a simple sub-dialect of SQL (Structured Query Language) offers basic capabilities to filter, group, and represent the data:

An analytics query can specify the following constraints:

A time frame to extract data from with the inclusive start and end time stamps
A resolution in the time domain by which it will be reported and its data grouped
The selected measurement and how it is aggregated
Optional additional grouping to further divide the data (e.g. to present distinct devices)
Optional additional filtering to further restrict what data to report (e.g. to only show the data of the selected device)
Optional ordering clauses that sort the resulting data.

Example: Time series query structure

Retrieve the number of scans for a use case in time frames of one hour grouped by the used scanning device:

{
    "range_start": "2021-02-20T00:00:00+0000",
    "range_end": "2021-02-22T23:59:59+0000",
    "resolution": "1h",
    "select": [
      {"name": "measure_value::varchar", "aggregate": "count", "alias": "scans"},
      {"name": "gateway_l3_id"}
    ],
    "where": [
      {"name": "measure_name", "operator": "=", "value": "scan_code"},
      {"name": "gateway_l2_id", "operator": "=", "value": "b2a931"}
    ],
    "groupby": ["gateway_l3_id"]
}

The sections below describe the existing constraints:

1. Time frame

The time-series data is always selected within the bounds of a time frame. This is specified by two inclusive points in time, range_start and range_end, as the boundaries. Only data within the interval of these points in time is considered for the query and its result.

Calendar-sensitive resolutions: weeks and months

Most resolutions are constant durations expressed in seconds. They are insensitive to time zones and other calendar effects.

In contrast, the resolutions w for week and M for month are both sensitive to the calendar and time zone as they represent a full calendar week or month rather than the number of seconds a week or an average month would take.

For this reason, certain restrictions and special behavior apply to week and month resolutions:

At the moment, the resolutions of weeks or months are only possible with a single week or month respectively.
The start of a week or a month is adjusted by evaluating the time zone of the range of the timestamps.

Time zone adjustments

As outlined above both week and month resolution (w and M) are time zone sensitive. When selecting them, these rules apply:

The time zone of the range_start parameters is evaluated. The UTC offset of the time zone is used to transpose the result times to match the local time zone.

Time Range Zones	Example	Effect
different	`range_start`: “2000-01-01:00:00+0000”	warning
`range_end`: “2000-01-01:00:00+0100”
UTC	`range_start`: “2000-01-01:00:00+0000”	no adjustment
`range_end`: “2000-01-01:00:00+0000”
local time zone	`range_start`: “2000-01-01:00:00+0100”	adjustment +1h
`range_end`: “2000-01-01:00:00+0100”

This is done to allow the client to get results reflecting either the neutral UTC time zone or a local time zone of the users choice.

2.Resolution

The resulting time-series data is grouped into equidistant time slots. By picking a suitable resolution, the granularity of this grouping can be adjusted to fit the time range and observation requirements.

To group the data into time windows the following resolution values are available:

Resolution	Factor	Description	Example
`s`	any	Seconds	`30s`
`m`	any	Minutes	`10m`
`h`	any	Hours	`12h`
`d`	any	Days	`3d`
`w`	1	(One) week	`1w`
`M`	1	(One) month	`1M`

Example: Resolution

E.g. in the first query above, without any other grouping clauses, picking different resolution values yields these results: Consider we aggregate over the resolution windows by summing up the Scan Duration.

Resolution	Sum(Scan Duration)
1 min	2.03 s , 9.23 s, 0.54 s , 5.54 s
1 h	11.26 s , 0.54 s , 5.54 s
1 d	17.34 s

3.Measurement and aggregation

To select measurements:

select measure_value::double or measure_value::varchar according to the type in the table of Recorded measures
filter by the measure_name of the desired measure

E.g. this selects the scan_duration measure:

{
    "select": [
      {"name": "measure_value::double", "aggregate": "sum"},
    ],
    "where": [
      {"name": "measure_name", "aggregate": "=", "value": "scan_duration"},
    ]
}

To aggregate data, you can use one of these functions:

Aggregation	Description
`avg`	Arithmetic mean of all values
`sum`	Sum of all values
`count`	Number of all values

These aggregation functions are applied to all selected data with a single group and time resolution.

4.Grouping

The resulting data can be grouped by:

The resolution in the time domain
And any grouping clauses

Example: Grouping

Additionally, the groups can be subdivided by attributes other than time. In the example above, if we additionally group by the Device Serial Number, the time windows of the resolution parameter are reported for each device:

Resolution	Sum(Scan Duration)
1 min	M2MD010101234:2.03 s , M2MB010105678:9.23 s, M2MD010101234:0.54 s , M2MD010101234:5.54 s
1 h	M2MD010101234:2.03 s , M2MB010105678:9.23 s, M2MD010101234:0.54 s , M2MD010101234:5.54 s
1 d	M2MD010101234:8.11 s , M2MB010105678:9.23 s

5. Filtering

Dividing the data for analysis only into groups of time windows or attribute values is often not enough as it does not allow you to zoom in on specific data. Instead, before grouping and aggregating the data, a filtering pass is necessary.

E.g. consider the retrieved data should be that of a specific process or a set of devices. To achieve this, a filtering clause can be used to exclude any data not matching the criteria:

At the moment data can be matched only by comparing for equality of a string value. To match data attributes these string-valued operators can be used:

Operator	Description
`=`	Test for equality

Example: Filtering

In the example, if we filter by Device Serial to be equal to M2MB010105678, we aggregate only on this row:

Time	Attribute: Symbology	Attribute: Device Serial	Attribute: Scan Duration
2021.01.23 06:03:14	DataMatrix	M2MB010105678	9.23 s

6.Ordering

The order of the returned data can be selected with an ordering clause: One or more attributes can be selected to order the data by. In addition, the data can be either returned in an ascending (lowest values first) or descending direction.

An important use of data ordering is selecting the tail or head of some data: E.g. the top 5 of some measurement or the lowest 10 of some datum can be implemented by ordering such that the first returned rows are the wanted ones and selecting a limit to get exactly the wanted rows.

Example: Ordering

If we were now interested in the longest scan duration we would take it from the top row with ordering set to duration:

{
    "range_start": "2021-02-20T00:00:00+0000",
    "range_end": "2021-02-22T23:59:59+0000",
    "select": [
      {"name": "measure_value::double", "aggregate": "sum", "alias": "duration"},
      {"name": "gateway_l3_id"}
    ],
    "where": [
      {"name": "measure_name", "aggregate": "=", "value": "scan_duration"},
    ],
    "groupby": ["gateway_l3_id"],
    "orderby": ["duration"],
}

The results would be following:

Time	Attribute: Symbology	Attribute: Device Serial	Attribute: Scan Duration
2021.01.23 06:03:14	DataMatrix	M2MB010105678	9.23 s

In this section:

ProGlove Documentation