Upsolver
Search…
MIN_TIME_SERIES
Get the minimum value per time interval. This aggregation is useful for collecting time-series data for graphs. The size of the time intervals is configurable & dynamic; the time interval used will change depending on how many points you wish to hold per aggregation key ‌You can change the time interval and/or the number of points per interval to suit your graph.

Syntax

MIN_TIME_SERIES([MAX POINTS, INTERVALS, ]TIME, VALUE)

Arguments

MAX POINTS: The maximum amount of points to hold before reducing the resolution to the next interval size. (Default Value: 300) INTERVALS: An array of integers representing the time interval buckets to return data for. If the aggregation has more than MAX POINTS values in the given window it will use the next interval from this array to reduce the amount of points to comply with MAX POINTS. Default: [60000, 300000, 600000, 1800000, 3600000, 10800000, 21600000, 43200000, 86400000] TIME: An expression returning the time value that the VALUE is to be associated with. VALUE: An expression returning the value to aggregate in the provided TIME bucket.

Returns

An array of key value pairs, the key is the time (in epoch ms) and the value is of type VALUE and contains the average of the values within that time frame.

AVAILABLE IN

Output Type Availablity Aggregated Outputs NO Explicit Lookup Table YES Inline Joins\Lookups NO

Example 1

Collect from server metric events the max cpu over time in order to display on a graph in a web page.

Data

1
[
2
{
3
"serverIp":"10.0.0.1",
4
"time":1628894700000,
5
"cpuUsage":52.3
6
},
7
{
8
"serverIp":"10.0.0.1",
9
"time":1628894760000,
10
"cpuUsage":2.4
11
},
12
{
13
"serverIp":"10.0.0.1",
14
"time":1628894820000,
15
"cpuUsage":99.3
16
},
17
{
18
"serverIp":"10.0.0.1",
19
"time":1628894880000,
20
"cpuUsage":99.6
21
},
22
{
23
"serverIp":"10.0.0.1",
24
"time":1628894940000,
25
"cpuUsage":12.3
26
},
27
{
28
"serverIp":"10.0.0.1",
29
"time":1628895000000,
30
"cpuUsage":55
31
},
32
{
33
"serverIp":"10.0.0.2",
34
"time":1628894700000,
35
"cpuUsage":2.3
36
},
37
{
38
"serverIp":"10.0.0.2",
39
"time":1628894760000
40
},
41
{
42
"serverIp":"10.0.0.2",
43
"time":1628894820000,
44
"cpuUsage":9.3
45
},
46
{
47
"serverIp":"10.0.0.2",
48
"time":1628894880000,
49
"cpuUsage":9.6
50
},
51
{
52
"serverIp":"10.0.0.2",
53
"time":1628894940000,
54
"cpuUsage":2.3
55
}
56
]
Copied!

Query

Find the minimum values for the time series, sorted by serverIPs:
1
SET
2
Calculated_Date_Field = UNIX_EPOCH_TO_DATE(data.time);
3
SELECT
4
data.serverIp AS serverip:STRING,
5
MIN_TIME_SERIES (data.time, data.cpuUsage) AS min_cpu
6
FROM
7
"MAX_TIME_SERIES_ms_2"
8
GROUP BY
9
data.serverIp
Copied!

Results

1
{
2
"min_time_series_data_timedata_cpuusage":[
3
{
4
"key":1628894700000,
5
"value":2.3
6
},
7
{
8
"key":1628894820000,
9
"value":9.3
10
},
11
{
12
"key":1628894880000,
13
"value":9.6
14
},
15
{
16
"key":1628894940000,
17
"value":2.3
18
}
19
],
20
"serverip":"10.0.0.2"
21
}{
22
"min_time_series_data_timedata_cpuusage":[
23
{
24
"key":1628894700000,
25
"value":52.3
26
},
27
{
28
"key":1628894760000,
29
"value":2.4
30
},
31
{
32
"key":1628894820000,
33
"value":99.3
34
},
35
{
36
"key":1628894880000,
37
"value":99.6
38
},
39
{
40
"key":1628894940000,
41
"value":12.3
42
},
43
{
44
"key":1628895000000,
45
"value":55
46
}
47
],
48
"serverip":"10.0.0.1"
49
}
Copied!

Example 2

Data

1
{
2
"serverIp":"10.0.0.1",
3
"time":1628894700,
4
"cpuUsage":52.3
5
}{
6
"serverIp":"10.0.0.1",
7
"time":1628894760,
8
"cpuUsage":2.42
9
}{
10
"serverIp":"10.0.0.1",
11
"time":1628894820,
12
"cpuUsage":99.3
13
}{
14
"serverIp":"10.0.0.1",
15
"time":1628894880,
16
"cpuUsage":99.6
17
}{
18
"serverIp":"10.0.0.1",
19
"time":1628894940,
20
"cpuUsage":12.3
21
}{
22
"serverIp":"10.0.0.1",
23
"time":1628895000,
24
"cpuUsage":55.0
25
}
Copied!

Query

Find the minimum values for the time series, sorted by serverIPs, but within a specific interval:
1
SELECT
2
data.serverIp AS serverip:STRING,
3
MIN_TIME_SERIES(2, [60000, 300000], data.time, data.cpuUsage) AS min_cpu
4
FROM
5
TIME_SERIES_DATA_w_NULL
6
GROUP BY
7
data.serverIp
Copied!

Results

1
{
2
"serverip":"10.0.0.2",
3
"min_cpu":[
4
{
5
"value":2.3,
6
"key":1628894700000
7
}
8
]
9
}{
10
"serverip":"10.0.0.1",
11
"min_cpu":[
12
{
13
"value":2.4,
14
"key":1628894700000
15
},
16
{
17
"value":55,
18
"key":1628895000000
19
}
20
]
21
}
Copied!

Dialog