InfluxQL transformation functions
InfluxQL transformation functions modify and return values in each row of queried data.
Missing InfluxQL functions
Some InfluxQL functions are in the process of being rearchitected to work with
the InfluxDB 3.0 storage engine. If a function you need is not here, check the
InfluxQL feature support page
for more information.
Must use aggregate or selector functions when grouping by time
Most transformation functions support GROUP BY
clauses that group by tags,
but do not directly support GROUP BY
clauses that group by time.
To use transformation functions with with a GROUP BY time()
clause, apply
an aggregate
or selector
function to the field_expression argument.
The transformation operates on the result of the aggregate or selector operation.
ABS()
Returns the absolute value of the field value.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply ABS()
to a field
SELECT
a,
ABS(a)
FROM numbers
LIMIT 6
time |
a |
abs |
2023-01-01T00:00:00Z |
0.33909108671076 |
0.33909108671076 |
2023-01-01T00:01:00Z |
-0.774984088561186 |
0.774984088561186 |
2023-01-01T00:02:00Z |
-0.921037167720451 |
0.921037167720451 |
2023-01-01T00:03:00Z |
-0.73880754843378 |
0.73880754843378 |
2023-01-01T00:04:00Z |
-0.905980032168252 |
0.905980032168252 |
2023-01-01T00:05:00Z |
-0.891164752631417 |
0.891164752631417 |
Apply ABS()
to each field
SELECT ABS(*) FROM numbers LIMIT 6
time |
abs_a |
abs_b |
2023-01-01T00:00:00Z |
0.33909108671076 |
0.163643058925645 |
2023-01-01T00:01:00Z |
0.774984088561186 |
0.137034364053949 |
2023-01-01T00:02:00Z |
0.921037167720451 |
0.482943221384294 |
2023-01-01T00:03:00Z |
0.73880754843378 |
0.0729732928756677 |
2023-01-01T00:04:00Z |
0.905980032168252 |
1.77857552719844 |
2023-01-01T00:05:00Z |
0.891164752631417 |
0.741147445214238 |
Apply ABS()
to time windows (grouped by time)
SELECT
ABS(MEAN(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
abs |
2023-01-01T00:00:00Z |
0.4345725888930678 |
2023-01-01T00:10:00Z |
0.12861008519618367 |
2023-01-01T00:20:00Z |
0.030168160597251192 |
2023-01-01T00:30:00Z |
0.02928699660831855 |
2023-01-01T00:40:00Z |
0.02211434600834538 |
2023-01-01T00:50:00Z |
0.15530468657783394 |
ACOS()
Returns the arccosine (in radians) of the field value.
Field values must be between -1 and 1.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply ACOS()
to a field
SELECT
a,
ACOS(a)
FROM numbers
LIMIT 6
time |
a |
acos |
2023-01-01T00:00:00Z |
0.33909108671076 |
1.2248457522250173 |
2023-01-01T00:01:00Z |
-0.774984088561186 |
2.4574862443115 |
2023-01-01T00:02:00Z |
-0.921037167720451 |
2.741531473732281 |
2023-01-01T00:03:00Z |
-0.73880754843378 |
2.4020955294179256 |
2023-01-01T00:04:00Z |
-0.905980032168252 |
2.7044854502651114 |
2023-01-01T00:05:00Z |
-0.891164752631417 |
2.6707024029338 |
Apply ACOS()
to each field
SELECT ACOS(*) FROM numbers LIMIT 6
time |
acos_a |
acos_b |
2023-01-01T00:00:00Z |
1.2248457522250173 |
1.7351786975993897 |
2023-01-01T00:01:00Z |
2.4574862443115 |
1.433329416131427 |
2023-01-01T00:02:00Z |
2.741531473732281 |
2.074809114132046 |
2023-01-01T00:03:00Z |
2.4020955294179256 |
1.6438345403920092 |
2023-01-01T00:04:00Z |
2.7044854502651114 |
|
2023-01-01T00:05:00Z |
2.6707024029338 |
0.7360183965088304 |
Apply ACOS()
to time windows (grouped by time)
SELECT
ACOS(MEAN(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
acos |
2023-01-01T00:00:00Z |
2.0203599837582877 |
2023-01-01T00:10:00Z |
1.441829029328407 |
2023-01-01T00:20:00Z |
1.5406235882252437 |
2023-01-01T00:30:00Z |
1.5415051418561052 |
2023-01-01T00:40:00Z |
1.5486801779072885 |
2023-01-01T00:50:00Z |
1.41486045205998 |
ASIN()
Returns the arcsine (in radians) of the field value.
Field values must be between -1 and 1.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply ASIN()
to a field
SELECT
a,
ASIN(a)
FROM numbers
LIMIT 6
time |
a |
asin |
2023-01-01T00:00:00Z |
0.33909108671076 |
0.34595057456987915 |
2023-01-01T00:01:00Z |
-0.774984088561186 |
-0.8866899175166036 |
2023-01-01T00:02:00Z |
-0.921037167720451 |
-1.1707351469373848 |
2023-01-01T00:03:00Z |
-0.73880754843378 |
-0.8312992026230288 |
2023-01-01T00:04:00Z |
-0.905980032168252 |
-1.133689123470215 |
2023-01-01T00:05:00Z |
-0.891164752631417 |
-1.0999060761389035 |
Apply ASIN()
to each field
SELECT ASIN(*) FROM numbers LIMIT 6
time |
asin_a |
asin_b |
2023-01-01T00:00:00Z |
0.34595057456987915 |
-0.1643823708044932 |
2023-01-01T00:01:00Z |
-0.8866899175166036 |
0.1374669106634696 |
2023-01-01T00:02:00Z |
-1.1707351469373848 |
-0.5040127873371497 |
2023-01-01T00:03:00Z |
-0.8312992026230288 |
-0.07303821359711259 |
2023-01-01T00:04:00Z |
-1.133689123470215 |
|
2023-01-01T00:05:00Z |
-1.0999060761389035 |
0.8347779302860662 |
Apply ASIN()
to time windows (grouped by time)
SELECT
ASIN(MEAN(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
asin |
2023-01-01T00:00:00Z |
-0.44956365696339134 |
2023-01-01T00:10:00Z |
0.1289672974664895 |
2023-01-01T00:20:00Z |
0.030172738569652847 |
2023-01-01T00:30:00Z |
0.029291184938791334 |
2023-01-01T00:40:00Z |
0.022116148887608062 |
2023-01-01T00:50:00Z |
0.15593587473491674 |
ATAN()
Returns the arctangent (in radians) of the field value.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply ATAN()
to a field
SELECT
a,
ATAN(a)
FROM numbers
LIMIT 6
time |
a |
atan |
2023-01-01T00:00:00Z |
0.33909108671076 |
0.32692355076199897 |
2023-01-01T00:01:00Z |
-0.774984088561186 |
-0.659300127490126 |
2023-01-01T00:02:00Z |
-0.921037167720451 |
-0.7443170183837121 |
2023-01-01T00:03:00Z |
-0.73880754843378 |
-0.6362993731936669 |
2023-01-01T00:04:00Z |
-0.905980032168252 |
-0.7361091800814261 |
2023-01-01T00:05:00Z |
-0.891164752631417 |
-0.727912249468035 |
Apply ATAN()
to each field
SELECT ATAN(*) FROM numbers LIMIT 6
time |
atan_a |
atan_b |
2023-01-01T00:00:00Z |
0.32692355076199897 |
-0.1622053541422186 |
2023-01-01T00:01:00Z |
-0.659300127490126 |
0.13618613793696105 |
2023-01-01T00:02:00Z |
-0.7443170183837121 |
-0.4499093121666581 |
2023-01-01T00:03:00Z |
-0.6362993731936669 |
-0.07284417510130452 |
2023-01-01T00:04:00Z |
-0.7361091800814261 |
1.0585985450688151 |
2023-01-01T00:05:00Z |
-0.727912249468035 |
0.6378113578294793 |
Apply ATAN()
to time windows (grouped by time)
SELECT
ATAN(MEAN(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
atan |
2023-01-01T00:00:00Z |
-0.4099506966510045 |
2023-01-01T00:10:00Z |
0.1279079463727065 |
2023-01-01T00:20:00Z |
0.030159013397288013 |
2023-01-01T00:30:00Z |
0.02927862748761639 |
2023-01-01T00:40:00Z |
0.022110742100818606 |
2023-01-01T00:50:00Z |
0.15407382461141705 |
ATAN2()
Returns the the arctangent of y/x
in radians.
ATAN2(expression_y, expression_x)
Arguments
- expression_y: Expression to identify the
y
numeric value or one or more
fields to operate on.
Can be a number literal, field key,
constant, or wildcard (*
).
Supports numeric field types.
- expression_x: Expression to identify the
x
numeric value or one or more
fields to operate on.
Can be a number literal, field key,
constant, or wildcard (*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply ATAN2()
to a field divided by another field
SELECT ATAN2(a, b) FROM numbers LIMIT 6
time |
atan2 |
2023-01-01T00:00:00Z |
2.0204217911794937 |
2023-01-01T00:01:00Z |
-1.395783190047229 |
2023-01-01T00:02:00Z |
-2.053731408859953 |
2023-01-01T00:03:00Z |
-1.669248713922653 |
2023-01-01T00:04:00Z |
-0.47112754043763505 |
2023-01-01T00:05:00Z |
-0.8770454978291377 |
Apply ATAN2()
to each field divided by a numeric value
SELECT ATAN2(*, 2) FROM numbers LIMIT 6
time |
atan2_a |
atan2_b |
2023-01-01T00:00:00Z |
0.16794843225523703 |
-0.0816396675119722 |
2023-01-01T00:01:00Z |
-0.36967737169970566 |
0.06841026268126137 |
2023-01-01T00:02:00Z |
-0.4315666721698651 |
-0.2369359777533473 |
2023-01-01T00:03:00Z |
-0.35385538623378937 |
-0.036470468100670846 |
2023-01-01T00:04:00Z |
-0.4253376417906667 |
0.7268651162204586 |
2023-01-01T00:05:00Z |
-0.41917415992493756 |
0.35488446257957357 |
Apply ATAN2()
to time windows (grouped by time)
SELECT
ATAN2(MEAN(a), MEAN(b))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
atan2 |
2023-01-01T00:00:00Z |
-1.278967897411707 |
2023-01-01T00:10:00Z |
2.3520553840586773 |
2023-01-01T00:20:00Z |
2.226497789888965 |
2023-01-01T00:30:00Z |
3.0977773783018656 |
2023-01-01T00:40:00Z |
2.9285769547942677 |
2023-01-01T00:50:00Z |
0.9505419744107901 |
CEIL()
Returns the subsequent value rounded up to the nearest integer.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply CEIL()
to a field
SELECT
b,
CEIL(b)
FROM numbers
LIMIT 6
time |
b |
ceil |
2023-01-01T00:00:00Z |
-0.163643058925645 |
-0 |
2023-01-01T00:01:00Z |
0.137034364053949 |
1 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
-0 |
2023-01-01T00:03:00Z |
-0.0729732928756677 |
-0 |
2023-01-01T00:04:00Z |
1.77857552719844 |
2 |
2023-01-01T00:05:00Z |
0.741147445214238 |
1 |
Apply CEIL()
to each field
SELECT CEIL(*) FROM numbers LIMIT 6
time |
ceil_a |
ceil_b |
2023-01-01T00:00:00Z |
1 |
-0 |
2023-01-01T00:01:00Z |
-0 |
1 |
2023-01-01T00:02:00Z |
-0 |
-0 |
2023-01-01T00:03:00Z |
-0 |
-0 |
2023-01-01T00:04:00Z |
-0 |
2 |
2023-01-01T00:05:00Z |
-0 |
1 |
Apply CEIL()
to time windows (grouped by time)
SELECT
CEIL(MEAN(b))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
ceil |
2023-01-01T00:00:00Z |
1 |
2023-01-01T00:10:00Z |
-0 |
2023-01-01T00:20:00Z |
-0 |
2023-01-01T00:30:00Z |
-0 |
2023-01-01T00:40:00Z |
-0 |
2023-01-01T00:50:00Z |
1 |
COS()
Returns the cosine of the field value.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply COS()
to a field
SELECT
b,
COS(b)
FROM numbers
LIMIT 6
time |
b |
cos |
2023-01-01T00:00:00Z |
-0.163643058925645 |
0.9866403278718959 |
2023-01-01T00:01:00Z |
0.137034364053949 |
0.9906254752128878 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
0.8856319645801471 |
2023-01-01T00:03:00Z |
-0.0729732928756677 |
0.9973386305831397 |
2023-01-01T00:04:00Z |
1.77857552719844 |
-0.20628737691395405 |
2023-01-01T00:05:00Z |
0.741147445214238 |
0.7376943643170851 |
Apply COS()
to each field
SELECT COS(*) FROM numbers LIMIT 6
time |
cos_a |
cos_b |
2023-01-01T00:00:00Z |
0.9430573869206459 |
0.9866403278718959 |
2023-01-01T00:01:00Z |
0.7144321674550146 |
0.9906254752128878 |
2023-01-01T00:02:00Z |
0.6049946586273094 |
0.8856319645801471 |
2023-01-01T00:03:00Z |
0.7392720891861374 |
0.9973386305831397 |
2023-01-01T00:04:00Z |
0.616914561474936 |
-0.20628737691395405 |
2023-01-01T00:05:00Z |
0.6285065034701617 |
0.7376943643170851 |
Apply COS()
to time windows (grouped by time)
SELECT
COS(MEAN(b))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
cos |
2023-01-01T00:00:00Z |
0.9914907269510592 |
2023-01-01T00:10:00Z |
0.9918765457796455 |
2023-01-01T00:20:00Z |
0.9997307399250498 |
2023-01-01T00:30:00Z |
0.7850670342365872 |
2023-01-01T00:40:00Z |
0.9947779847618986 |
2023-01-01T00:50:00Z |
0.9938532355205111 |
CUMULATIVE_SUM()
Returns the running total of subsequent field values.
CUMULATIVE_SUM(field_expression)
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply CUMULATIVE_SUM()
to a field
SELECT CUMULATIVE_SUM(b) FROM numbers LIMIT 6
time |
cumulative_sum |
2023-01-01T00:00:00Z |
-0.163643058925645 |
2023-01-01T00:01:00Z |
-0.02660869487169601 |
2023-01-01T00:02:00Z |
-0.5095519162559901 |
2023-01-01T00:03:00Z |
-0.5825252091316577 |
2023-01-01T00:04:00Z |
1.1960503180667823 |
2023-01-01T00:05:00Z |
1.9371977632810204 |
Apply CUMULATIVE_SUM()
to each field
SELECT CUMULATIVE_SUM(*) FROM numbers LIMIT 6
time |
cumulative_sum_a |
cumulative_sum_b |
2023-01-01T00:00:00Z |
0.33909108671076 |
-0.163643058925645 |
2023-01-01T00:01:00Z |
-0.43589300185042595 |
-0.02660869487169601 |
2023-01-01T00:02:00Z |
-1.3569301695708769 |
-0.5095519162559901 |
2023-01-01T00:03:00Z |
-2.095737718004657 |
-0.5825252091316577 |
2023-01-01T00:04:00Z |
-3.001717750172909 |
1.1960503180667823 |
2023-01-01T00:05:00Z |
-3.892882502804326 |
1.9371977632810204 |
Apply CUMULATIVE_SUM()
to field keys that match a regular expression
SELECT CUMULATIVE_SUM(/[ab]/) FROM numbers LIMIT 6
time |
cumulative_sum_a |
cumulative_sum_b |
2023-01-01T00:00:00Z |
0.33909108671076 |
-0.163643058925645 |
2023-01-01T00:01:00Z |
-0.43589300185042595 |
-0.02660869487169601 |
2023-01-01T00:02:00Z |
-1.3569301695708769 |
-0.5095519162559901 |
2023-01-01T00:03:00Z |
-2.095737718004657 |
-0.5825252091316577 |
2023-01-01T00:04:00Z |
-3.001717750172909 |
1.1960503180667823 |
2023-01-01T00:05:00Z |
-3.892882502804326 |
1.9371977632810204 |
Apply CUMULATIVE_SUM()
to time windows (grouped by time)
SELECT
CUMULATIVE_SUM(SUM(b))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
cumulative_sum |
2023-01-01T00:00:00Z |
1.3054783385851743 |
2023-01-01T00:10:00Z |
0.029980276948385454 |
2023-01-01T00:20:00Z |
-0.20208529969578404 |
2023-01-01T00:30:00Z |
-6.882005145666267 |
2023-01-01T00:40:00Z |
-7.904410787756402 |
2023-01-01T00:50:00Z |
-6.795080184131271 |
DERIVATIVE()
Returns the rate of change between subsequent field values
per unit
.
SELECT DERIVATIVE(field_expression[, unit])
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric field types.
- unit: Unit of time to use to calculate the rate of change.
Supports duration literals.
Default is
1s
(per second).
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply DERIVATIVE()
to a field to calculate the per second change
SELECT DERIVATIVE(b) FROM numbers LIMIT 6
time |
derivative |
2023-01-01T00:01:00Z |
0.005011290382993233 |
2023-01-01T00:02:00Z |
-0.01033295975730405 |
2023-01-01T00:03:00Z |
0.006832832141810439 |
2023-01-01T00:04:00Z |
0.03085914700123513 |
2023-01-01T00:05:00Z |
-0.017290468033070033 |
2023-01-01T00:06:00Z |
-0.007557890705063634 |
Apply DERIVATIVE()
to a field to calculate the per 5 minute change
SELECT DERIVATIVE(b, 5m) FROM numbers LIMIT 6
time |
derivative |
2023-01-01T00:01:00Z |
1.5033871148979698 |
2023-01-01T00:02:00Z |
-3.0998879271912148 |
2023-01-01T00:03:00Z |
2.0498496425431316 |
2023-01-01T00:04:00Z |
9.257744100370537 |
2023-01-01T00:05:00Z |
-5.187140409921009 |
2023-01-01T00:06:00Z |
-2.26736721151909 |
Apply DERIVATIVE()
to each field
SELECT DERIVATIVE(*) FROM numbers LIMIT 6
time |
derivative_a |
derivative_b |
2023-01-01T00:01:00Z |
-0.018567919587865765 |
0.005011290382993233 |
2023-01-01T00:02:00Z |
-0.0024342179859877505 |
-0.01033295975730405 |
2023-01-01T00:03:00Z |
0.0030371603214445152 |
0.006832832141810439 |
2023-01-01T00:04:00Z |
-0.0027862080622411984 |
0.03085914700123513 |
2023-01-01T00:05:00Z |
0.00024692132561391543 |
-0.017290468033070033 |
2023-01-01T00:06:00Z |
0.016704951104985283 |
-0.007557890705063634 |
Apply DERIVATIVE()
to field keys that match a regular expression
SELECT DERIVATIVE(/[ab]/) FROM numbers LIMIT 6
time |
derivative_a |
derivative_b |
2023-01-01T00:01:00Z |
-0.018567919587865765 |
0.005011290382993233 |
2023-01-01T00:02:00Z |
-0.0024342179859877505 |
-0.01033295975730405 |
2023-01-01T00:03:00Z |
0.0030371603214445152 |
0.006832832141810439 |
2023-01-01T00:04:00Z |
-0.0027862080622411984 |
0.03085914700123513 |
2023-01-01T00:05:00Z |
0.00024692132561391543 |
-0.017290468033070033 |
2023-01-01T00:06:00Z |
0.016704951104985283 |
-0.007557890705063634 |
Apply DERIVATIVE()
to time windows (grouped by time)
SELECT
DERIVATIVE(MEAN(b), 1m)
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
derivative |
2023-01-01T00:10:00Z |
-0.025809764002219633 |
2023-01-01T00:20:00Z |
0.010434324849926194 |
2023-01-01T00:30:00Z |
-0.06447854269326314 |
2023-01-01T00:40:00Z |
0.05657514203880348 |
2023-01-01T00:50:00Z |
0.021317362457152655 |
DIFFERENCE()
Returns the result of subtraction between subsequent field values.
DIFFERENCE(field_expression)
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply DIFFERENCE()
to a field
SELECT DIFFERENCE(b) FROM numbers LIMIT 6
time |
difference |
2023-01-01T00:01:00Z |
0.300677422979594 |
2023-01-01T00:02:00Z |
-0.619977585438243 |
2023-01-01T00:03:00Z |
0.40996992850862635 |
2023-01-01T00:04:00Z |
1.8515488200741077 |
2023-01-01T00:05:00Z |
-1.0374280819842019 |
2023-01-01T00:06:00Z |
-0.45347344230381803 |
Apply DIFFERENCE()
to each field
SELECT DIFFERENCE(*) FROM numbers LIMIT 6
time |
difference_a |
difference_b |
2023-01-01T00:01:00Z |
-1.114075175271946 |
0.300677422979594 |
2023-01-01T00:02:00Z |
-0.14605307915926502 |
-0.619977585438243 |
2023-01-01T00:03:00Z |
0.18222961928667092 |
0.40996992850862635 |
2023-01-01T00:04:00Z |
-0.1671724837344719 |
1.8515488200741077 |
2023-01-01T00:05:00Z |
0.014815279536834924 |
-1.0374280819842019 |
2023-01-01T00:06:00Z |
1.002297066299117 |
-0.45347344230381803 |
Apply DIFFERENCE()
to field keys that match a regular expression
SELECT DIFFERENCE(/[ab]/) FROM numbers LIMIT 6
time |
difference_a |
difference_b |
2023-01-01T00:01:00Z |
-1.114075175271946 |
0.300677422979594 |
2023-01-01T00:02:00Z |
-0.14605307915926502 |
-0.619977585438243 |
2023-01-01T00:03:00Z |
0.18222961928667092 |
0.40996992850862635 |
2023-01-01T00:04:00Z |
-0.1671724837344719 |
1.8515488200741077 |
2023-01-01T00:05:00Z |
0.014815279536834924 |
-1.0374280819842019 |
2023-01-01T00:06:00Z |
1.002297066299117 |
-0.45347344230381803 |
Apply DIFFERENCE()
to time windows (grouped by time)
SELECT
DIFFERENCE(MEAN(b))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
difference |
2023-01-01T00:10:00Z |
-0.2580976400221963 |
2023-01-01T00:20:00Z |
0.10434324849926194 |
2023-01-01T00:30:00Z |
-0.6447854269326314 |
2023-01-01T00:40:00Z |
0.5657514203880348 |
2023-01-01T00:50:00Z |
0.21317362457152655 |
ELAPSED()
Returns the difference between subsequent field value’s
timestamps in a specified unit
of time.
ELAPSED(field_expression[, unit ])
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports all field types.
- unit: Unit of time to return the elapsed time in.
Supports duration literals.
Default is
1ns
(nanoseconds).
Notable behaviors
- If the
unit
is greater than the elapsed time between points, ELAPSED()
returns 0
.
ELAPSED()
supports the GROUP BY time()
clause but the query results aren’t very useful.
An ELAPSED()
query with a nested function and a GROUP BY time()
clause
returns the interval specified in the GROUP BY time()
clause.
Examples
The following examples use the
Random numbers sample data.
Apply ELAPSED()
to a field and return elapsed time in nanoseconds
SELECT ELAPSED(b) FROM numbers LIMIT 6
time |
elapsed |
2023-01-01T00:01:00Z |
60000000000 |
2023-01-01T00:02:00Z |
60000000000 |
2023-01-01T00:03:00Z |
60000000000 |
2023-01-01T00:04:00Z |
60000000000 |
2023-01-01T00:05:00Z |
60000000000 |
2023-01-01T00:06:00Z |
60000000000 |
Apply ELAPSED()
to a field and return elapsed time in seconds
SELECT ELAPSED(b, 1s) FROM numbers LIMIT 6
time |
elapsed |
2023-01-01T00:01:00Z |
60 |
2023-01-01T00:02:00Z |
60 |
2023-01-01T00:03:00Z |
60 |
2023-01-01T00:04:00Z |
60 |
2023-01-01T00:05:00Z |
60 |
2023-01-01T00:06:00Z |
60 |
Apply ELAPSED()
to each field
SELECT ELAPSED(*) FROM numbers LIMIT 6
time |
elapsed_a |
elapsed_b |
2023-01-01T00:01:00Z |
60000000000 |
60000000000 |
2023-01-01T00:02:00Z |
60000000000 |
60000000000 |
2023-01-01T00:03:00Z |
60000000000 |
60000000000 |
2023-01-01T00:04:00Z |
60000000000 |
60000000000 |
2023-01-01T00:05:00Z |
60000000000 |
60000000000 |
2023-01-01T00:06:00Z |
60000000000 |
60000000000 |
Apply ELAPSED()
to field keys that match a regular expression
SELECT ELAPSED(/[ab]/, 1s) FROM numbers LIMIT 6
time |
elapsed_a |
elapsed_b |
2023-01-01T00:01:00Z |
60 |
60 |
2023-01-01T00:02:00Z |
60 |
60 |
2023-01-01T00:03:00Z |
60 |
60 |
2023-01-01T00:04:00Z |
60 |
60 |
2023-01-01T00:05:00Z |
60 |
60 |
2023-01-01T00:06:00Z |
60 |
60 |
EXP()
Returns the exponential of the field value.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply EXP()
to a field
SELECT
a,
EXP(a)
FROM numbers
LIMIT 6
time |
a |
exp |
2023-01-01T00:00:00Z |
0.33909108671076 |
1.4036711951820788 |
2023-01-01T00:01:00Z |
-0.774984088561186 |
0.460711111517308 |
2023-01-01T00:02:00Z |
-0.921037167720451 |
0.39810592427186076 |
2023-01-01T00:03:00Z |
-0.73880754843378 |
0.4776831901055915 |
2023-01-01T00:04:00Z |
-0.905980032168252 |
0.40414561525252984 |
2023-01-01T00:05:00Z |
-0.891164752631417 |
0.4101777188333968 |
Apply EXP()
to each field
SELECT EXP(*) FROM numbers LIMIT 6
time |
exp_a |
exp_b |
2023-01-01T00:00:00Z |
1.4036711951820788 |
0.8490450268435884 |
2023-01-01T00:01:00Z |
0.460711111517308 |
1.14686755886191 |
2023-01-01T00:02:00Z |
0.39810592427186076 |
0.6169648527893578 |
2023-01-01T00:03:00Z |
0.4776831901055915 |
0.929625657322271 |
2023-01-01T00:04:00Z |
0.40414561525252984 |
5.921415512753404 |
2023-01-01T00:05:00Z |
0.4101777188333968 |
2.09834186598405 |
Apply EXP()
to time windows (grouped by time)
SELECT
EXP(MEAN(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
exp |
2023-01-01T00:00:00Z |
0.6475413743155294 |
2023-01-01T00:10:00Z |
1.137246608416461 |
2023-01-01T00:20:00Z |
1.030627830373793 |
2023-01-01T00:30:00Z |
1.029720078241656 |
2023-01-01T00:40:00Z |
1.0223606806499268 |
2023-01-01T00:50:00Z |
1.1680137850180072 |
FLOOR()
Returns the subsequent value rounded down to the nearest integer.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply FLOOR()
to a field
SELECT
b,
FLOOR(b)
FROM numbers
LIMIT 6
time |
b |
floor |
2023-01-01T00:00:00Z |
-0.163643058925645 |
-1 |
2023-01-01T00:01:00Z |
0.137034364053949 |
0 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
-1 |
2023-01-01T00:03:00Z |
-0.0729732928756677 |
-1 |
2023-01-01T00:04:00Z |
1.77857552719844 |
1 |
2023-01-01T00:05:00Z |
0.741147445214238 |
0 |
Apply FLOOR()
to each field
SELECT FLOOR(*) FROM numbers LIMIT 6
time |
floor_a |
floor_b |
2023-01-01T00:00:00Z |
0 |
-1 |
2023-01-01T00:01:00Z |
-1 |
0 |
2023-01-01T00:02:00Z |
-1 |
-1 |
2023-01-01T00:03:00Z |
-1 |
-1 |
2023-01-01T00:04:00Z |
-1 |
1 |
2023-01-01T00:05:00Z |
-1 |
0 |
Apply FLOOR()
to time windows (grouped by time)
SELECT
FLOOR(SUM(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
floor |
2023-01-01T00:00:00Z |
-5 |
2023-01-01T00:10:00Z |
1 |
2023-01-01T00:20:00Z |
0 |
2023-01-01T00:30:00Z |
0 |
2023-01-01T00:40:00Z |
0 |
2023-01-01T00:50:00Z |
1 |
LN()
Returns the natural logarithm of the field value.
Field values must be greater than or equal to 0.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply LN()
to a field
SELECT
b,
LN(b)
FROM numbers
LIMIT 6
time |
b |
ln |
2023-01-01T00:00:00Z |
-0.163643058925645 |
|
2023-01-01T00:01:00Z |
0.137034364053949 |
-1.98752355209665 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
|
2023-01-01T00:03:00Z |
-0.0729732928756677 |
|
2023-01-01T00:04:00Z |
1.77857552719844 |
0.5758127783016702 |
2023-01-01T00:05:00Z |
0.741147445214238 |
-0.2995556920844895 |
Apply LN()
to each field
SELECT LN(*) FROM numbers LIMIT 6
time |
ln_a |
ln_b |
2023-01-01T00:00:00Z |
-1.0814865153308908 |
|
2023-01-01T00:01:00Z |
|
-1.98752355209665 |
2023-01-01T00:02:00Z |
|
|
2023-01-01T00:03:00Z |
|
|
2023-01-01T00:04:00Z |
|
0.5758127783016702 |
2023-01-01T00:05:00Z |
|
-0.2995556920844895 |
Apply LN()
to time windows (grouped by time)
SELECT
LN(SUM(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
ln |
2023-01-01T00:00:00Z |
|
2023-01-01T00:10:00Z |
0.25161504572793725 |
2023-01-01T00:20:00Z |
-1.1983831026157092 |
2023-01-01T00:30:00Z |
-1.2280265702380913 |
2023-01-01T00:40:00Z |
-1.5089436474159283 |
2023-01-01T00:50:00Z |
0.4402187212890264 |
LOG()
Returns the logarithm of the field value with base b
.
Field values must be greater than or equal to 0.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
- b: Logarithm base to use in the operation.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply LOG()
to a field with a base of 3
SELECT
b,
LOG(b, 3)
FROM numbers
LIMIT 6
time |
b |
log |
2023-01-01T00:00:00Z |
-0.163643058925645 |
|
2023-01-01T00:01:00Z |
0.137034364053949 |
-1.8091219009630797 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
|
2023-01-01T00:03:00Z |
-0.0729732928756677 |
|
2023-01-01T00:04:00Z |
1.77857552719844 |
0.5241273780031629 |
2023-01-01T00:05:00Z |
0.741147445214238 |
-0.2726673414946528 |
Apply LOG()
to each field with a base of 5
SELECT LOG(*, 5) FROM numbers LIMIT 6
time |
log_a |
log_b |
2023-01-01T00:00:00Z |
-0.6719653532302217 |
|
2023-01-01T00:01:00Z |
|
-1.2349178161776593 |
2023-01-01T00:02:00Z |
|
|
2023-01-01T00:03:00Z |
|
|
2023-01-01T00:04:00Z |
|
0.3577725949246566 |
2023-01-01T00:05:00Z |
|
-0.18612441633827553 |
Apply LOG()
to time windows (grouped by time)
SELECT
LOG(SUM(a), 10)
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
log |
2023-01-01T00:00:00Z |
|
2023-01-01T00:10:00Z |
0.10927502592347751 |
2023-01-01T00:20:00Z |
-0.5204511686721008 |
2023-01-01T00:30:00Z |
-0.5333251630849791 |
2023-01-01T00:40:00Z |
-0.6553258995757036 |
2023-01-01T00:50:00Z |
0.1911845614863297 |
LOG2()
Returns the logarithm of the field value to the base 2.
Field values must be greater than or equal to 0.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply LOG2()
to a field
SELECT
b,
LOG2(b)
FROM numbers
LIMIT 6
time |
b |
log2 |
2023-01-01T00:00:00Z |
-0.163643058925645 |
|
2023-01-01T00:01:00Z |
0.137034364053949 |
-2.8673903722598544 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
|
2023-01-01T00:03:00Z |
-0.0729732928756677 |
|
2023-01-01T00:04:00Z |
1.77857552719844 |
0.8307222397363156 |
2023-01-01T00:05:00Z |
0.741147445214238 |
-0.4321675114403543 |
Apply LOG2()
to each field
SELECT LOG2(*) FROM numbers LIMIT 6
time |
log2_a |
log2_b |
2023-01-01T00:00:00Z |
-1.560255232456162 |
|
2023-01-01T00:01:00Z |
|
-2.8673903722598544 |
2023-01-01T00:02:00Z |
|
|
2023-01-01T00:03:00Z |
|
|
2023-01-01T00:04:00Z |
|
0.8307222397363156 |
2023-01-01T00:05:00Z |
|
-0.4321675114403543 |
Apply LOG2()
to time windows (grouped by time)
SELECT
LOG2(SUM(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
log2 |
2023-01-01T00:00:00Z |
|
2023-01-01T00:10:00Z |
0.36300377868474476 |
2023-01-01T00:20:00Z |
-1.7289013592288134 |
2023-01-01T00:30:00Z |
-1.7716678429623767 |
2023-01-01T00:40:00Z |
-2.1769455171078644 |
2023-01-01T00:50:00Z |
0.6351013661101591 |
LOG10()
Returns the logarithm of the field value to the base 10.
Field values must be greater than or equal to 0.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply LOG10()
to a field
SELECT
b,
LOG10(b)
FROM numbers
LIMIT 6
time |
b |
log10 |
2023-01-01T00:00:00Z |
-0.163643058925645 |
|
2023-01-01T00:01:00Z |
0.137034364053949 |
-0.8631705113283253 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
|
2023-01-01T00:03:00Z |
-0.0729732928756677 |
|
2023-01-01T00:04:00Z |
1.77857552719844 |
0.25007231222579585 |
2023-01-01T00:05:00Z |
0.741147445214238 |
-0.1300953840950034 |
Apply LOG10()
to each field
SELECT LOG10(*) FROM numbers LIMIT 6
time |
log10_a |
log10_b |
2023-01-01T00:00:00Z |
-0.46968362586098245 |
|
2023-01-01T00:01:00Z |
|
-0.8631705113283253 |
2023-01-01T00:02:00Z |
|
|
2023-01-01T00:03:00Z |
|
|
2023-01-01T00:04:00Z |
|
0.25007231222579585 |
2023-01-01T00:05:00Z |
|
-0.1300953840950034 |
Apply LOG10()
to time windows (grouped by time)
SELECT
LOG10(SUM(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
log10 |
2023-01-01T00:00:00Z |
|
2023-01-01T00:10:00Z |
0.10927502592347751 |
2023-01-01T00:20:00Z |
-0.520451168672101 |
2023-01-01T00:30:00Z |
-0.5333251630849791 |
2023-01-01T00:40:00Z |
-0.6553258995757036 |
2023-01-01T00:50:00Z |
0.19118456148632973 |
MOVING_AVERAGE()
Returns the rolling average across a window of subsequent field values.
MOVING_AVERAGE(field_expression, N)
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports all field types.
- N: Number of field values to use when calculating the moving average.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply MOVING_AVERAGE()
to a field
SELECT MOVING_AVERAGE(a, 3) FROM numbers LIMIT 6
time |
moving_average |
2023-01-01T00:02:00Z |
-0.4523100565236256 |
2023-01-01T00:03:00Z |
-0.8116096015718056 |
2023-01-01T00:04:00Z |
-0.8552749161074944 |
2023-01-01T00:05:00Z |
-0.8453174444111498 |
2023-01-01T00:06:00Z |
-0.5620041570439896 |
2023-01-01T00:07:00Z |
-0.3569778402485757 |
Apply MOVING_AVERAGE()
to each field
SELECT MOVING_AVERAGE(*, 3) FROM numbers LIMIT 6
time |
moving_average_a |
moving_average_b |
2023-01-01T00:02:00Z |
-0.4523100565236256 |
-0.16985063875199669 |
2023-01-01T00:03:00Z |
-0.8116096015718056 |
-0.13962738340200423 |
2023-01-01T00:04:00Z |
-0.8552749161074944 |
0.40755300431282615 |
2023-01-01T00:05:00Z |
-0.8453174444111498 |
0.815583226512337 |
2023-01-01T00:06:00Z |
-0.5620041570439896 |
0.9357989917743662 |
2023-01-01T00:07:00Z |
-0.3569778402485757 |
0.15985821845558748 |
Apply MOVING_AVERAGE()
to field keys that match a regular expression
SELECT MOVING_AVERAGE(/[ab]/, 3) FROM numbers LIMIT 6
time |
moving_average_a |
moving_average_b |
2023-01-01T00:02:00Z |
-0.4523100565236256 |
-0.16985063875199669 |
2023-01-01T00:03:00Z |
-0.8116096015718056 |
-0.13962738340200423 |
2023-01-01T00:04:00Z |
-0.8552749161074944 |
0.40755300431282615 |
2023-01-01T00:05:00Z |
-0.8453174444111498 |
0.815583226512337 |
2023-01-01T00:06:00Z |
-0.5620041570439896 |
0.9357989917743662 |
2023-01-01T00:07:00Z |
-0.3569778402485757 |
0.15985821845558748 |
Apply MOVING_AVERAGE()
to time windows (grouped by time)
SELECT
MOVING_AVERAGE(SUM(a), 3)
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
moving_average |
2023-01-01T00:20:00Z |
-0.9193144769987766 |
2023-01-01T00:30:00Z |
0.626884141339178 |
2023-01-01T00:40:00Z |
0.27189834404638374 |
2023-01-01T00:50:00Z |
0.6890200973149928 |
NON_NEGATIVE_DERIVATIVE()
Returns only non-negative rate of change between subsequent
field values.
Negative rates of change return null.
NON_NEGATIVE_DERIVATIVE(field_expression[, unit])
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric field types.
- unit: Unit of time to use to calculate the rate of change.
Supports duration literals.
Default is
1s
(per second).
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply NON_NEGATIVE_DERIVATIVE()
to a field to calculate the per second change
SELECT NON_NEGATIVE_DERIVATIVE(b) FROM numbers LIMIT 6
time |
non_negative_derivative |
2023-01-01T00:01:00Z |
0.005011290382993233 |
2023-01-01T00:03:00Z |
0.006832832141810439 |
2023-01-01T00:04:00Z |
0.03085914700123513 |
2023-01-01T00:08:00Z |
0.0227877053636946 |
2023-01-01T00:10:00Z |
0.001676063810538834 |
2023-01-01T00:11:00Z |
0.014999637478226817 |
Apply NON_NEGATIVE_DERIVATIVE()
to a field to calculate the per 5 minute change
SELECT NON_NEGATIVE_DERIVATIVE(b, 5m) FROM numbers LIMIT 6
time |
non_negative_derivative |
2023-01-01T00:01:00Z |
1.5033871148979698 |
2023-01-01T00:03:00Z |
2.0498496425431316 |
2023-01-01T00:04:00Z |
9.257744100370537 |
2023-01-01T00:08:00Z |
6.836311609108379 |
2023-01-01T00:10:00Z |
0.5028191431616502 |
2023-01-01T00:11:00Z |
4.499891243468045 |
Apply NON_NEGATIVE_DERIVATIVE()
to each field
SELECT NON_NEGATIVE_DERIVATIVE(*) FROM numbers LIMIT 6
time |
non_negative_derivative_a |
non_negative_derivative_b |
2023-01-01T00:01:00Z |
|
0.005011290382993233 |
2023-01-01T00:03:00Z |
0.0030371603214445152 |
0.006832832141810439 |
2023-01-01T00:04:00Z |
|
0.03085914700123513 |
2023-01-01T00:05:00Z |
0.00024692132561391543 |
|
2023-01-01T00:06:00Z |
0.016704951104985283 |
|
2023-01-01T00:08:00Z |
|
0.0227877053636946 |
2023-01-01T00:09:00Z |
0.018437240876186967 |
|
2023-01-01T00:10:00Z |
|
0.001676063810538834 |
2023-01-01T00:11:00Z |
|
0.014999637478226817 |
2023-01-01T00:13:00Z |
0.006694752202850366 |
|
2023-01-01T00:14:00Z |
0.011836797386191167 |
|
Apply NON_NEGATIVE_DERIVATIVE()
to field keys that match a regular expression
SELECT NON_NEGATIVE_DERIVATIVE(/[ab]/) FROM numbers LIMIT 6
time |
non_negative_derivative_a |
non_negative_derivative_b |
2023-01-01T00:01:00Z |
|
0.005011290382993233 |
2023-01-01T00:03:00Z |
0.0030371603214445152 |
0.006832832141810439 |
2023-01-01T00:04:00Z |
|
0.03085914700123513 |
2023-01-01T00:05:00Z |
0.00024692132561391543 |
|
2023-01-01T00:06:00Z |
0.016704951104985283 |
|
2023-01-01T00:08:00Z |
|
0.0227877053636946 |
2023-01-01T00:09:00Z |
0.018437240876186967 |
|
2023-01-01T00:10:00Z |
|
0.001676063810538834 |
2023-01-01T00:11:00Z |
|
0.014999637478226817 |
2023-01-01T00:13:00Z |
0.006694752202850366 |
|
2023-01-01T00:14:00Z |
0.011836797386191167 |
|
Apply NON_NEGATIVE_DERIVATIVE()
to time windows (grouped by time)
SELECT
NON_NEGATIVE_DERIVATIVE(MEAN(b), 1m)
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
non_negative_derivative |
2023-01-01T00:20:00Z |
0.010434324849926194 |
2023-01-01T00:40:00Z |
0.05657514203880348 |
2023-01-01T00:50:00Z |
0.021317362457152655 |
NON_NEGATIVE_DIFFERENCE()
Returns only non-negative result of subtraction between subsequent
field values.
Negative differences return null.
NON_NEGATIVE_DIFFERENCE(field_expression)
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply NON_NEGATIVE_DIFFERENCE()
to a field
SELECT NON_NEGATIVE_DIFFERENCE(b) FROM numbers LIMIT 6
time |
non_negative_difference |
2023-01-01T00:01:00Z |
0.300677422979594 |
2023-01-01T00:03:00Z |
0.40996992850862635 |
2023-01-01T00:04:00Z |
1.8515488200741077 |
2023-01-01T00:08:00Z |
1.367262321821676 |
2023-01-01T00:10:00Z |
0.10056382863233004 |
2023-01-01T00:11:00Z |
0.899978248693609 |
Apply NON_NEGATIVE_DIFFERENCE()
to each field
SELECT NON_NEGATIVE_DIFFERENCE(*) FROM numbers LIMIT 6
time |
non_negative_difference_a |
non_negative_difference_b |
2023-01-01T00:01:00Z |
|
0.300677422979594 |
2023-01-01T00:03:00Z |
0.18222961928667092 |
0.40996992850862635 |
2023-01-01T00:04:00Z |
|
1.8515488200741077 |
2023-01-01T00:05:00Z |
0.014815279536834924 |
|
2023-01-01T00:06:00Z |
1.002297066299117 |
|
2023-01-01T00:08:00Z |
|
1.367262321821676 |
2023-01-01T00:09:00Z |
1.106234452571218 |
|
2023-01-01T00:10:00Z |
|
0.10056382863233004 |
2023-01-01T00:11:00Z |
|
0.899978248693609 |
2023-01-01T00:13:00Z |
0.401685132171022 |
|
2023-01-01T00:14:00Z |
0.71020784317147 |
|
Apply NON_NEGATIVE_DIFFERENCE()
to field keys that match a regular expression
SELECT NON_NEGATIVE_DIFFERENCE(/[ab]/) FROM numbers LIMIT 6
time |
non_negative_difference_a |
non_negative_difference_b |
2023-01-01T00:01:00Z |
|
0.300677422979594 |
2023-01-01T00:03:00Z |
0.18222961928667092 |
0.40996992850862635 |
2023-01-01T00:04:00Z |
|
1.8515488200741077 |
2023-01-01T00:05:00Z |
0.014815279536834924 |
|
2023-01-01T00:06:00Z |
1.002297066299117 |
|
2023-01-01T00:08:00Z |
|
1.367262321821676 |
2023-01-01T00:09:00Z |
1.106234452571218 |
|
2023-01-01T00:10:00Z |
|
0.10056382863233004 |
2023-01-01T00:11:00Z |
|
0.899978248693609 |
2023-01-01T00:13:00Z |
0.401685132171022 |
|
2023-01-01T00:14:00Z |
0.71020784317147 |
|
Apply NON_NEGATIVE_DIFFERENCE()
to time windows (grouped by time)
SELECT
NON_NEGATIVE_DIFFERENCE(MEAN(b))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
non_negative_difference |
2023-01-01T00:20:00Z |
0.10434324849926194 |
2023-01-01T00:40:00Z |
0.5657514203880348 |
2023-01-01T00:50:00Z |
0.21317362457152655 |
POW()
Returns the field value to the power of x
.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
- x: Power to raise to.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply POW()
to a field with a power of 3
SELECT
b,
POW(b, 3)
FROM numbers
LIMIT 6
time |
b |
pow |
2023-01-01T00:00:00Z |
-0.163643058925645 |
-0.004382205777325515 |
2023-01-01T00:01:00Z |
0.137034364053949 |
0.002573288422171338 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
-0.1126388541916811 |
2023-01-01T00:03:00Z |
-0.0729732928756677 |
-0.0003885901893904874 |
2023-01-01T00:04:00Z |
1.77857552719844 |
5.626222933751733 |
2023-01-01T00:05:00Z |
0.741147445214238 |
0.4071119474284653 |
Apply POW()
to each field with a power of 5
SELECT POW(*, 5) FROM numbers LIMIT 6
time |
pow_a |
pow_b |
2023-01-01T00:00:00Z |
0.004483135555212479 |
-0.00011735131084020357 |
2023-01-01T00:01:00Z |
-0.2795528536239978 |
0.000048322282876973225 |
2023-01-01T00:02:00Z |
-0.6628050073932118 |
-0.026271227986693114 |
2023-01-01T00:03:00Z |
-0.22011853819169455 |
-0.000002069282189962477 |
2023-01-01T00:04:00Z |
-0.6103699296012646 |
17.797604890097084 |
2023-01-01T00:05:00Z |
-0.5620694808926487 |
0.22362640363833164 |
Apply POW()
to time windows (grouped by time)
SELECT
POW(SUM(a), 10)
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
pow |
2023-01-01T00:00:00Z |
2402278.159218532 |
2023-01-01T00:10:00Z |
12.380844221267186 |
2023-01-01T00:20:00Z |
0.000006244365466732681 |
2023-01-01T00:30:00Z |
0.0000046424621235691315 |
2023-01-01T00:40:00Z |
2.7973126174031977e-7 |
2023-01-01T00:50:00Z |
81.6292140233699 |
ROUND()
Returns a field value rounded to the nearest integer.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply ROUND()
to a field
SELECT
b,
ROUND(b)
FROM numbers
LIMIT 6
time |
b |
round |
2023-01-01T00:00:00Z |
-0.163643058925645 |
-0 |
2023-01-01T00:01:00Z |
0.137034364053949 |
0 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
-0 |
2023-01-01T00:03:00Z |
-0.0729732928756677 |
-0 |
2023-01-01T00:04:00Z |
1.77857552719844 |
2 |
2023-01-01T00:05:00Z |
0.741147445214238 |
1 |
Apply ROUND()
to each field
SELECT ROUND(*) FROM numbers LIMIT 6
time |
round_a |
round_b |
2023-01-01T00:00:00Z |
0 |
-0 |
2023-01-01T00:01:00Z |
-1 |
0 |
2023-01-01T00:02:00Z |
-1 |
-0 |
2023-01-01T00:03:00Z |
-1 |
-0 |
2023-01-01T00:04:00Z |
-1 |
2 |
2023-01-01T00:05:00Z |
-1 |
1 |
Apply ROUND()
to time windows (grouped by time)
SELECT
ROUND(SUM(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
round |
2023-01-01T00:00:00Z |
-4 |
2023-01-01T00:10:00Z |
1 |
2023-01-01T00:20:00Z |
0 |
2023-01-01T00:30:00Z |
0 |
2023-01-01T00:40:00Z |
0 |
2023-01-01T00:50:00Z |
2 |
SIN()
Returns the sine of a field value.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply SIN()
to a field
SELECT
b,
SIN(b)
FROM numbers
LIMIT 6
time |
b |
sin |
2023-01-01T00:00:00Z |
-0.163643058925645 |
-0.1629136686003898 |
2023-01-01T00:01:00Z |
0.137034364053949 |
0.13660588515594851 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
-0.4643877941052164 |
2023-01-01T00:03:00Z |
-0.0729732928756677 |
-0.0729085450859347 |
2023-01-01T00:04:00Z |
1.77857552719844 |
0.9784914502058565 |
2023-01-01T00:05:00Z |
0.741147445214238 |
0.6751348197618099 |
Apply SIN()
to each field
SELECT SIN(*) FROM numbers LIMIT 6
time |
sin_a |
sin_b |
2023-01-01T00:00:00Z |
0.3326300722640741 |
-0.1629136686003898 |
2023-01-01T00:01:00Z |
-0.6997047077914582 |
0.13660588515594851 |
2023-01-01T00:02:00Z |
-0.7962295291135749 |
-0.4643877941052164 |
2023-01-01T00:03:00Z |
-0.673406844448706 |
-0.0729085450859347 |
2023-01-01T00:04:00Z |
-0.7870301289278495 |
0.9784914502058565 |
2023-01-01T00:05:00Z |
-0.7778043295686337 |
0.6751348197618099 |
Apply SIN()
to time windows (grouped by time)
SELECT
SIN(SUM(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
sin |
2023-01-01T00:00:00Z |
0.933528830283535 |
2023-01-01T00:10:00Z |
0.9597472276784815 |
2023-01-01T00:20:00Z |
0.29712628761434723 |
2023-01-01T00:30:00Z |
0.2887011711003489 |
2023-01-01T00:40:00Z |
0.21934537994884437 |
2023-01-01T00:50:00Z |
0.9998424824522808 |
SQRT()
Returns the square root of a field value.
Field values must be greater than or equal to 0.
Negative field values return null.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply SQRT()
to a field
SELECT
b,
SQRT(b)
FROM numbers
LIMIT 6
time |
b |
sqrt |
2023-01-01T00:00:00Z |
-0.163643058925645 |
|
2023-01-01T00:01:00Z |
0.137034364053949 |
0.370181528515334 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
|
2023-01-01T00:03:00Z |
-0.0729732928756677 |
|
2023-01-01T00:04:00Z |
1.77857552719844 |
1.3336324558132349 |
2023-01-01T00:05:00Z |
0.741147445214238 |
0.860899207349059 |
Apply SQRT()
to each field
SELECT SQRT(*) FROM numbers LIMIT 6
time |
sqrt_a |
sqrt_b |
2023-01-01T00:00:00Z |
0.5823152811928947 |
|
2023-01-01T00:01:00Z |
|
0.370181528515334 |
2023-01-01T00:02:00Z |
|
|
2023-01-01T00:03:00Z |
|
|
2023-01-01T00:04:00Z |
|
1.3336324558132349 |
2023-01-01T00:05:00Z |
|
0.860899207349059 |
Apply SQRT()
to time windows (grouped by time)
SELECT
SQRT(SUM(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
sqrt |
2023-01-01T00:00:00Z |
|
2023-01-01T00:10:00Z |
1.134063865909604 |
2023-01-01T00:20:00Z |
0.5492555015405052 |
2023-01-01T00:30:00Z |
0.5411746169982342 |
2023-01-01T00:40:00Z |
0.4702589287652642 |
2023-01-01T00:50:00Z |
1.2462130097934059 |
TAN()
Returns the tangent of the field value.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, or wildcard (
*
).
Supports numeric field types.
Notable behaviors
Examples
The following examples use the
Random numbers sample data.
Apply TAN()
to a field
SELECT
b,
TAN(b)
FROM numbers
LIMIT 6
time |
b |
tan |
2023-01-01T00:00:00Z |
-0.163643058925645 |
-0.16511961248511045 |
2023-01-01T00:01:00Z |
0.137034364053949 |
0.13789861917955581 |
2023-01-01T00:02:00Z |
-0.482943221384294 |
-0.5243575352718546 |
2023-01-01T00:03:00Z |
-0.0729732928756677 |
-0.07310309943905952 |
2023-01-01T00:04:00Z |
1.77857552719844 |
-4.743341375725582 |
2023-01-01T00:05:00Z |
0.741147445214238 |
0.9151958486043346 |
Apply TAN()
to each field
SELECT TAN(*) FROM numbers LIMIT 6
time |
tan_a |
tan_b |
2023-01-01T00:00:00Z |
0.3527145610408791 |
-0.16511961248511045 |
2023-01-01T00:01:00Z |
-0.9793857830953787 |
0.13789861917955581 |
2023-01-01T00:02:00Z |
-1.3160934857179802 |
-0.5243575352718546 |
2023-01-01T00:03:00Z |
-0.9109052733075013 |
-0.07310309943905952 |
2023-01-01T00:04:00Z |
-1.2757522322802637 |
-4.743341375725582 |
2023-01-01T00:05:00Z |
-1.2375438046768912 |
0.9151958486043346 |
Apply TAN()
to time windows (grouped by time)
SELECT
TAN(SUM(a))
FROM numbers
WHERE
time >= '2023-01-01T00:00:00Z'
AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)
time |
tan |
2023-01-01T00:00:00Z |
-2.603968631156288 |
2023-01-01T00:10:00Z |
3.4171098358131733 |
2023-01-01T00:20:00Z |
0.31117972731464494 |
2023-01-01T00:30:00Z |
0.30154101138968664 |
2023-01-01T00:40:00Z |
0.22482036866737865 |
2023-01-01T00:50:00Z |
56.3338223288096 |
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community!
We welcome and encourage your feedback and bug reports for InfluxDB and this documentation.
To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.