Description
This function returns time series data for an attribute or tag over a specified time period.
Parameters
| Parameter | Data Type | Description | Default |
|---|---|---|---|
| id | bigint | The attribute or tag id to get the history data for | Required |
| start_timestamp | timestamptz | The start of the time period | Required |
| end_timestamp | timestamptz | The end of the requested time period | Required |
| agg_method | api_v1.aggregation_method_enum | The way the aggregation will be performed. Default is NONE. If aggregation method is EVENLY_AVERAGED, then agg_timestamps cannot be used. | NULL |
| agg_interval | interval | Aggregate over a fixed time interval. Cannot be used together with agg_num_points or agg_timestamps | NULL |
| agg_num_points | int | Aggregate over equally spaced points between start and end. Minimum number is 2 as the aggregation must be over a period. Cannot be used together with agg_intervals or agg_timestamps | NULL |
| agg_timestamps | timestamptz[] | Aggregate between timestamps, does not have to be equally spaced. Start and end timestamp will always be used for boundaries even if they are not specified. Cannot be used together with agg_num_points or agg_timestamps | NULL |
| interval_root_timestamp | timestamptz | When aggregating with interval align all interval timestamps to this root. If NULL, then align with start_timestamp | NULL |
| data_type | api_v1.data_type_enum | The data type that the results must be cast to. | NULL |
| mu_id | bigint | The measurement unit that the results must be converted to. | NULL |
Returns
| Data Type | Description |
|---|---|
| TABLE (v TEXT, s BIT(32), t TIMESTAMP WITH TIME ZONE) | Rows of value, status, timestamp history data |
Usage examples
Select time series data for one attribute
SELECT *
FROM api_v1.get_time_series(1234, '2021-01-02 03:04:55.123-07', '2021-01-02 04:04:55.123-07');
v (text) | s (bit(32)) | t (timestamp with time zone)
-----------+----------------------------------+-----------------------------
123.456 | 00000000000000000000000000000000 | 2021-01-02 03:04:55.123-07
125.556 | 00000000000000000000000000000000 | 2021-01-02 03:05:32.234-07
11.557 | 00000000000000000000000000000000 | 2021-01-02 03:05:35.712-07
...
Select time series data for multiple attributes
SELECT attr.id, ts.*
FROM api_v1.attributes attr
CROSS JOIN LATERAL api_v1.get_time_series( attr.id, '2021-01-02 03:04:55.123-07', '2021-01-02 04:04:55.123-07') ts
WHERE attr.relative_name = 'inlet_temperature'
ORDER BY attr.id, ts.t;
id (bigint) | v (text) | s (bit(32)) | t (timestamp with time zone)
-------------+-----------+----------------------------------+-----------------------------
412321 | 123.456 | 00000000000000000000000000000000 | 2021-01-02 03:04:55.123-07
412321 | 125.556 | 00000000000000000000000000000000 | 2021-01-02 03:05:32.234-07
412321 | 11.557 | 00000000000000000000000000000000 | 2021-01-02 03:05:35.712-07
...
412325 | 1223.456 | 00000000000000000000000000000000 | 2021-01-02 03:04:55.123-07
412325 | 1235.756 | 00000000000000000000000000000000 | 2021-01-02 03:06:31.34-07
412325 | 1.557 | 00000000000000000000000000000000 | 2021-01-02 03:08:33.222-07
...
532535 | -234.456 | 00000000000000000000000000000000 | 2021-01-02 03:04:55.123-07
532535 | 55.6 | 00000000000000000000000000000000 | 2021-01-02 03:07:32.234-07
532535 | 11 | 00000000000000000000000000000000 | 2021-01-02 03:09:35.712-07
...
Select time series data and OPC-UA Status
SELECT ts.v, opc.status_name. ts.t
FROM api_v1.get_time_series(1234, '2021-01-02 03:04:55.123-07', '2021-01-02 04:04:55.123-07') ts
JOIN api_v1.opc_ua_status_codes opc ON opc.status = ts.status;
v (text) | status_name (text) | t (timestamp with time zone)
-----------+----------------------+-----------------------------
123.456 | Good | 2021-01-02 03:04:55.123-07
125.556 | Good | 2021-01-02 03:05:32.234-07
11.557 | Good | 2021-01-02 03:05:35.712-07
...
Select time series data with aggregation method RESAMPLE and interval 10 seconds
SELECT *
FROM api_v1.get_time_series(1234, '2021-01-02 03:04:55.123-07', '2021-01-02 04:04:55.123-07',
agg_method := 'RESAMPLE', agg_interval := '10 seconds') ts;
v (text) | s (bit(32)) | t (timestamp with time zone)
-----------+----------------------------------+-----------------------------
123.456 | 00000000000000000000000000000000 | 2021-01-02 03:04:55.123-07
125.556 | 00000000000000000000000000000000 | 2021-01-02 03:05:15.123-07
11.557 | 00000000000000000000000000000000 | 2021-01-02 03:05:25.123-07
...
Return time series data as JSON
SELECT to_jsonb(t) hist
FROM ( -- A sub-select must be used to preserve column names
SELECT attr.id, array_agg(ts ORDER BY ts.t) as ts
FROM api_v1.attributes attr
CROSS JOIN LATERAL api_v1.get_time_series( attr.id, '2021-01-02 03:04:55.123-07',
'2021-01-02 04:04:55.123-07') ts
WHERE attr.relative_name = 'inlet_temperature'
GROUP BY attr.id
) t;
hist (jsonb)
-------------------------------------------------------------------------------------------------------------------
{"id": 2342, "ts": [{"v": 123.456, "s":00000000000000000000000000000000, "t":"2021-01-02 03:04:55.123-07"},{...}]}
{"id": 234254233, "ts": [{"v": 125.556, "s":00000000000000000000000000000000, "t":"2021-01-02 03:04:55.123-07"},{...}]}
{"id": 3243234, "ts": [{"v": 11.557, "s":00000000000000000000000000000000, "t":"2021-01-02 03:04:55.123-07"},{...}]}
...