The purpose of this document is to outline the planned implementation of the time series read and write api so that it can be reviewed prior to writing the code. It is the intent that this documentation is the starting point of the final documentation.
When completed, the documentation will show SQL, PHP, JavaScript, C#, GraphQL, and Python implementations.
The document describes pre-requisites Time Period Method and Aggregation Method prior to the explanation of the functions. The functions also use interpolation methods (none, previous, next, and linear) and data types (bool, int, string, datetime, interval, object).
Aggregation methods
-- SQL Definition of aggregation methods as an enum
CREATE TYPE api_v1.aggregation_method_enum AS
ENUM (
'NONE', 'DOWN_SAMPLE', 'RESAMPLE', 'EVENLY_AVERAGED', 'MIN', 'MAX', 'RANGE',
'AVG', 'MEDIAN', 'SUM', 'COUNT', 'START', 'END', 'DELTA', 'DURATION_TRUE',
'DURATION_FALSE', 'TRANSITIONS_TO_TRUE', 'TRANSITIONS_TO_FALSE'
);
Aggregation can only be done for float, integer and interval values:
|
Method |
Description (with interpolation method instructions) |
Allowed Aggregation Arguments |
|
NONE |
Return data with no aggregation (raw) |
NA |
|
DOWN_SAMPLE |
Returns a maximum number of samples by picking the first sample in each time period. This method will not create new samples and will only return samples that already exists. |
agg_interval, agg_num_points, agg_timestamps |
|
RESAMPLE |
Resample the value at each of the time periods. |
agg_interval, agg_num_points, agg_timestamps |
|
EVENLY_AVERAGED |
Returns a set of evenly averaged samples that is well suited for further analytics in functions that require evenly spaced data. It is well suited for FFT. The value is for the center point. This method will only accept agg_interval (using num_points or agg_timestamps will raise an exception). It will not accept an aggregation argument of timestamps as they may not be evenly spaced. |
agg_interval, agg_num_points |
|
MIN |
Return the minimum value for each time period |
agg_interval, agg_num_points, agg_timestamps |
|
MAX |
Return the maximum value for each time period |
Same as min |
|
RANGE |
Same as min |
|
|
AVG |
Return the average(mean) value for each time period. For linear, previous, or next: the mean is calculated as time weighted. For none: the mean is the mean of all values |
Same as min |
|
MEDIAN |
Return the median for each time period. The median value can only be calculated for interpolation method "none": the median is the median of all values |
Same as min |
|
SUM |
The sum in each time period. For linear, previous, or next: The time weighted integral of the value. For none: the sum of all the values. |
Same as min |
|
COUNT |
The number of values in the time period. |
Same as min |
|
START |
The value at the beginning of the time period (same as DOWN_SAMPLE) |
Same as min |
|
END |
The value at the end of the time period |
Same as min |
|
DELTA |
End value – start value |
Same as min |
|
DURATION_TRUE |
The amount of time (in seconds) the value is true (or not 0) | Same as min |
|
DURATION_FALSE |
The amount of time (in seconds) the value is false (or 0) | Same as min |
|
TRANSITIONS_TO_TRUE |
Count of transitions to true |
Same as min |
|
TRANSITIONS_TO_FALSE |
Count of transitions to false |
Same as min |
SQL Functions
get_time_series
function api_v1.get_time_series( id bigint,
start_timestamp timestamptz, end_timestamp timestamptz, agg_method enum default 'NONE', agg_interval interval default null, agg_num_points int default null, agg_timestamps timestamptz[] default null, data_type model.scalar_type_enum default null, mu_id bigint default null, interpolation_method model.interpolation_method_enum default null ) returns api_v1.time_series
get_time_series will return time series data for a selected attribute or tag id from start timestamp to end timestamp using the specified aggregation method, data type, measurement unit id, and interpolation method.
|
Parameter |
Description |
|
id |
Attribute or tag id. Throws an exception if the id is not valid attribute or tag. |
|
start_timestamp |
The start timestamp for the history request. The function may return data before this timestamp. |
|
end_timestamp |
See above |
|
agg_method |
See Aggregation methods above |
|
Only one of agg_interval, agg_count, agg_timestamps (see below) can be set (not null). If more than one is set, then an exception is raised. |
|
|
agg_interval |
A time interval for the aggregation. See Postgres definition of interval. Examples: '00:00:01.123', '1 second', '1.5 days' |
|
agg_count |
Divide the time between start and end timestamp in count evenly sized intervals |
|
agg_timestamps |
An array of timestamps. These do not have to be evenly spaced |
|
data_type |
The data type the result shall be mapped to. The result is always a string representation of the data type. Example, if the value is 2 and the data type is bool, then result is true. If the data type is interval and the original measurement unit is seconds, then the result is '00:00:02' seconds. If data_type is not provided or set to null, then the attribute data_type will be used. |
|
mu_id |
The measurement unit that the result shall be converted to. If null, then return underlying quantity kind and measurement unit. If mu_id is not provided or set to null, then the attribute measurement unit will be used. |
| interpolation_method |
One of the valid interpolation methods 'none', 'previous', 'next', 'linear'. If the interpolation method is not provided or set to null, then the attribute interpolation method will be used. |
|
Returns |
Description |
|
api_v1.time_series |
An object type that represents time series data including interpolation method, quantity kind, measurement unit and data type. |
Example usages:
-- To select a time series object, this requires that the calling function understands the returned object typeselect api_v1.get_time_series(1234, '2021-01-02 03:04:55.123-07', '2021-01-02 04:04:55.123-07');
-- To select a decomposed time series object
select *
from api_v1.get_time_series(1234, '2021-01-02 03:04:55.123-07', '2021-01-02 04:04:55.123-07');
-- To select a decomposed time series object that is unnested into rows
select vst.*
from api_v1.get_time_series(1234, '2021-01-02 03:04:55.123-07', '2021-01-02 04:04:55.123-07') hist
cross join unnest(hist.values, hist.statuses, hist.timestamps) as vst(value, status, timestamp);
-- To select a decomposed time series object with an aggregation method
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') hist;
-- To select a decomposed time series object with an aggregation method, data type, measurement unit
-- and interpolation method
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',
data_type := 'interval', mu_id := 3452, interpolation_method := 'linear') hist;
execute_expression
1. function api_v1.execute_expression(2. root_id bigint, 3. expression text, 4. start_timestamp timestamptz,5. end_timestamp timestamptz,6. agg_method text default 'none', 7. agg_interval interval default null, 8. agg_count int default null, 9. agg_timestamps timestamptz[] default null,10. data_type model.scalar_type_enum default null,11. mu_id bigint default null12. )13. returns expression.value_stream
get_value_stream will return time series data for a selected attribute or tag id from start timestamp to end timestamp using the specified aggregation method.
|
Parameter |
Description |
|
Root_id |
The place in the model where all relative names are derived from. It is OK to set to NULL if all names are absolute |
|
Expression |
See expression functions |
|
The rest |
Same as get_value_stream |