InfluxQL transformation functions

InfluxQL transformation functions modify and return values each row of queried data.

Each transformation function below covers syntax, including parameters to pass to the function, and examples of how to use the function. Examples use NOAA water sample data and data from sample_test.txt.

• ABS()
• ACOS()
• ASIN()
• ATAN()
• ATAN2()
• CEIL()
• COS()
• CUMULATIVE_SUM()
• DERIVATIVE()
• DIFFERENCE()
• ELAPSED()
• EXP()
• FLOOR()
• HISTOGRAM()
• LN()
• LOG()
• LOG2()
• LOG10()
• MOVING_AVERAGE()
• NON_NEGATIVE_DERIVATIVE()
• NON_NEGATIVE_DIFFERENCE
• POW
• ROUND
• SIN
• SQRT
• TAN

ABS()

Returns the absolute value of the field value. Supports GROUP BY clauses that group by tags but not group by time.

Basic syntax

SELECT ABS( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

ABS(field_key)
Returns the absolute values of field values associated with the field key.

ABS(*)
Returns the absolute values of field values associated with each field key in the measurement.

ABS() supports int64 and float64 field value data types.

Examples

Calculate the absolute values of field values associated with a field key

Return the absolute values of field values in the water_level field key in the h2o_feet measurement.

SELECT ABS("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:15:00Z'

name: data

Calculate the absolute values of field values associated with each field key in a measurement

Return the absolute values of field values for each field key that stores numeric values in the data measurement. The h2o_feet measurement has one numeric field water_level.

SELECT ABS(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:15:00Z'

name: data

Calculate the absolute values of field values associated with a field key and include several clauses

Return the absolute values of field values associated with the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT ABS("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: data

Advanced syntax

SELECT ABS(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the ABS() function to those results.

ABS() supports the following nested functions:

COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the absolute values of mean values

Return the absolute values of mean water_levels that are calculated at 12-minute intervals.

SELECT ABS(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' GROUP BY time(12m)

name: data

ACOS()

Returns the arccosine (in radians) of the field value. Field values must be between -1 and 1. Supports GROUP BY clauses that group by tags but does not support group by time.

Basic syntax

SELECT ACOS( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

ACOS(field_key)
Returns the arccosine of field values associated with the field key.

ACOS(*)
Returns the arccosine of field values associated with each field key in the measurement.

ACOS() supports int64 and float64 field value data types with values between -1 and 1.

Examples

The examples below use a subset of data from sample_test.txt, which only includes field values within the calculable range (-1 to 1). This value range is required for the ACOS() function:

Calculate the arccosine of field values associated with a field key

Return the arccosine of field values in the a field key in the data measurement.

SELECT ACOS("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arccosine of field values associated with each field key in a measurement

Return the arccosine of field values for each field key that stores numeric values in the data measurement, field a and b.

SELECT ACOS(*) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arccosine of field values associated with a field key and include several clauses

Return the arccosine of field values associated with the a field key in the time range between 2018-06-24T00:00:00Z and 2018-06-25T00:00:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT ACOS("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: data

Advanced syntax

SELECT ACOS(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the ACOS() function to those results.

ACOS() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the arccosine of mean values

Return the arccosine of mean a that are calculated at 3 hour intervals.

SELECT ACOS(MEAN("a")) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' GROUP BY time(3h)

name: data

ASIN()

Returns the arcsine (in radians) of the field value. Field values must be between -1 and 1.

Basic syntax

SELECT ASIN( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

ASIN(field_key)
Returns the arcsine of field values associated with the field key.

ASIN(*)
Returns the arcsine of field values associated with each field key in the measurement.

ASIN() supports int64 and float64 field value data types with values between -1 and 1.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use ASIN() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following data from sample_test.txt.

The following data from this data set only includes field value within the calculable range (-1 to 1) required for the ASIN() function:

Calculate the arcsine of field values associated with a field key

Return the arcsine of field values in the a field key in the data measurement.

SELECT ASIN("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arcsine of field values associated with each field key in a measurement

Return the arcsine of field values for each field key that stores numeric values in the data measurement. The data measurement has one numeric field: a.

SELECT ASIN(*) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arcsine of field values associated with a field key and include several clauses

Return the arcsine of field values associated with the a field key in the time range between 2018-06-24T00:00:00Z and 2018-06-25T00:00:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT ASIN("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: data

Advanced syntax

SELECT ASIN(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the ASIN() function to those results.

ASIN() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the arcsine of mean values

Return the arcsine of mean as that are calculated at 3-hour intervals.

SELECT ASIN(MEAN("a")) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' GROUP BY time(3h)

name: data

ATAN()

Returns the arctangent (in radians) of the field value. Field values must be between -1 and 1.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use ATAN() with a GROUP BY time() clause, see the Advanced syntax.

Basic syntax

SELECT ATAN( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

ATAN(field_key)
Returns the arctangent of field values associated with the field key.

ATAN(*)
Returns the arctangent of field values associated with each field key in the measurement.

ATAN() supports int64 and float64 field value data types with values between -1 and 1.

Examples

The examples below use a subset of data from sample_test.txt that only includes field values within the calculable range (-1 to 1) required for the of the ATAN() function.

Calculate the arctangent of field values associated with a field key

Return the arctangent of field values in the a field key in the data measurement.

SELECT ATAN("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arctangent of field values associated with each field key in a measurement

Return the arctangent of field values for each field key that stores numeric values in the data measurement–fields a and b.

SELECT ATAN(*) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arctangent of field values associated with a field key and include several clauses

Return the arctangent of field values associated with the a field key in time range between 2017-05-01T00:00:00Z and 2017-05-09T00:00:00Z and returns results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT ATAN("a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: data

Advanced syntax

SELECT ATAN(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the ATAN() function to those results.

ATAN() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples of advanced syntax

Calculate the arctangent of mean values

Return the arctangent of mean as that are calculated at 3-hour intervals.

SELECT ATAN(MEAN("a")) FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' GROUP BY time(3h)

name: data

ATAN2()

Returns the the arctangent of y/x in radians.

Basic syntax

SELECT ATAN2( [ * | <field_key> | num ], [ <field_key> | num ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

ATAN2(field_key_y, field_key_x)
Returns the arctangent of field values associated with the field key, field_key_y, divided by field values associated with field_key_x.

ATAN2(*, field_key_x)
Returns the field values associated with each field key in the measurement divided by field values associated with field_key_x.

ATAN2() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use ATAN2() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use sample_test.txt.

Calculate the arctangent of field_key_b over field_key_a

Return the arctangents of field values in the a field key divided by values in the b field key. Both are part of the data measurement.

SELECT ATAN2("a", "b") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arctangent of values associated with each field key in a measurement divided by field_key_a

Return the arctangents of all numeric field values in the data measurement divided by values in the a field key. The data measurement has two numeric fields: a and b.

SELECT ATAN2(*, "a") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z'

name: data

Calculate the arctangents of field values and include several clauses

Return the arctangent of field values associated with the a field key divided by the b field key in the time range between 2018-05-16T12:10:00Z and 2018-05-16T12:10:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT ATAN2("a", "b") FROM "data" WHERE time >= '2018-06-24T00:00:00Z' AND time <= '2018-06-25T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: data

Advanced syntax

SELECT ATAN2(<function()>, <function()>) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the ATAN2() function to those results.

ATAN2() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate arctangents of mean values

Return the arctangents of mean as divided by average bs. Averages are calculated at 2-hour intervals.

SELECT ATAN2(MEAN("b"), MEAN("a")) FROM "data" WHERE time >= '2018-06-24T12:00:00Z' AND time <= '2018-06-25T00:00:00Z' GROUP BY time(2h)

name: data

CEIL()

Returns the subsequent value rounded up to the nearest integer.

Basic syntax

SELECT CEIL( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

CEIL(field_key)
Returns the field values associated with the field key rounded up to the nearest integer.

CEIL(*)
Returns the field values associated with each field key in the measurement rounded up to the nearest integer.

CEIL() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use CEIL() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

Calculate the ceiling of field values associated with a field key

Return field values in the water_level field key in the h2o_feet measurement rounded up to the nearest integer.

SELECT CEIL("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

Calculate the ceiling of field values associated with each field key in a measurement

Return field values for each field key that stores numeric values in the h2o_feet measurement rounded up to the nearest integer. The h2o_feet measurement has one numeric field: water_level.

SELECT CEIL(*) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

Calculate the ceiling of field values associated with a field key and include several clauses

Return field values associated with the water_level field key rounded up to the nearest integer in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT CEIL("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Advanced syntax

SELECT CEIL(<function>( [ * | <field_key> | /<regular_expression>/ ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the CEIL() function to those results.

CEIL() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate mean values rounded up to the nearest integer

Return the mean water_levels that are calculated at 12-minute intervals and rounds them up to the nearest integer.

SELECT CEIL(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

COS()

Returns the cosine of the field value.

Basic syntax

SELECT COS( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

COS(field_key)
Returns the cosine of field values associated with the field key.

COS(*)
Returns the cosine of field values associated with each field key in the measurement.

COS() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use COS() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cosine of field values associated with a field key

Return the cosine of field values in the water_level field key in the h2o_feet measurement.

SELECT COS("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cosine of field values associated with each field key in a measurement

Return the cosine of field values for each numeric field in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

SELECT COS(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cosine of field values associated with a field key and include several clauses

Return the cosine of field values associated with the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT COS("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Advanced syntax

SELECT COS(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the COS() function to those results.

COS() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the cosine of mean values

Return the cosine of mean water_levels that are calculated at 12-minute intervals.

SELECT COS(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

CUMULATIVE_SUM()

Returns the running total of subsequent field values.

Basic syntax

SELECT CUMULATIVE_SUM( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

CUMULATIVE_SUM(field_key)
Returns the running total of subsequent field values associated with the field key.

CUMULATIVE_SUM(/regular_expression/)
Returns the running total of subsequent field values associated with each field key that matches the regular expression.

CUMULATIVE_SUM(*)
Returns the running total of subsequent field values associated with each field key in the measurement.

CUMULATIVE_SUM() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use CUMULATIVE_SUM() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cumulative sum of the field values associated with a field key

Return the running total of the field values in the water_level field key and in the h2o_feet measurement.

SELECT CUMULATIVE_SUM("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cumulative sum of the field values associated with each field key in a measurement

Return the running total of the field values for each numeric field in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

SELECT CUMULATIVE_SUM(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cumulative sum of the field values associated with each field key that matches a regular expression

Return the running total of the field values for each field key that stores numeric values and includes the word water in the h2o_feet measurement.

SELECT CUMULATIVE_SUM(/water/) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the cumulative sum of the field values associated with a field key and include several clauses

Return the running total of the field values associated with the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT CUMULATIVE_SUM("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Advanced syntax

SELECT CUMULATIVE_SUM(<function>( [ * | <field_key> | /<regular_expression>/ ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the CUMULATIVE_SUM() function to those results.

CUMULATIVE_SUM() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the cumulative sum of mean values

Return the running total of mean water_levels that are calculated at 12-minute intervals.

SELECT CUMULATIVE_SUM(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

DERIVATIVE()

Returns the rate of change between subsequent field values.

Basic syntax

SELECT DERIVATIVE( [ * | <field_key> | /<regular_expression>/ ] [ , <unit> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

InfluxDB calculates the difference between subsequent field values and converts those results into the rate of change per unit. The unit argument is an integer followed by a duration and it is optional. If the query does not specify the unit the unit defaults to one second (1s).

DERIVATIVE(field_key)
Returns the rate of change between subsequent field values associated with the field key.

DERIVATIVE(/regular_expression/)
Returns the rate of change between subsequent field values associated with each field key that matches the regular expression.

DERIVATIVE(*)
Returns the rate of change between subsequent field values associated with each field key in the measurement.

DERIVATIVE() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use DERIVATIVE() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples in this section use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

Calculate the derivative between the field values associated with a field key

Return the one-second rate of change between the water_level field values in the h2o_feet measurement.

SELECT DERIVATIVE("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

The first result (0.0000750000) is the one-second rate of change between the first two subsequent field values in the raw data. InfluxDB calculates the difference between the field values (subtracts the first field value from the second field value) and then normalizes that value to the one-second rate of change (dividing the difference between the field values’ timestamps in seconds (360s) by the default unit (1s)):

(2.379 - 2.352) / (360s / 1s)

Calculate the derivative between the field values associated with a field key and specify the unit option

Return the six-minute rate of change between the field values in the water_level field in the h2o_feet measurement.

SELECT DERIVATIVE("water_level",6m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

The first result (0.0270000000) is the six-minute rate of change between the first two subsequent field values in the raw data. InfluxDB calculates the difference between the field values (subtracts the first field value from the second field value) and then normalizes that value to the six-minute rate of change (dividing the difference between the field values’ timestamps in minutes (6m) by the specified interval (6m)):

(2.379 - 2.352) / (6m / 6m)

Calculate the derivative between the field values associated with each field key in a measurement and specify the unit option

Returns three-minute rate of change between the field values associated with each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

SELECT DERIVATIVE(*,3m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

The first result (0.0135000000) is the three-minute rate of change between the first two subsequent field values in the raw data.

InfluxDB calculates the difference between the field values (subtracts the first field value from the second field value) and then normalizes that value to the three-minute rate of change (dividing the difference between the field values’ timestamps in minutes (6m) by the specified interval (3m)):

(2.379 - 2.352) / (6m / 3m)

Calculate the derivative between the field values associated with each field key that matches a regular expression and specify the unit option

Return the two-minute rate of change between the field values associated with each field key that stores numeric values and includes the word water in the h2o_feet measurement.

SELECT DERIVATIVE(/water/,2m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet

The first result (0.0090000000) is the two-minute rate of change between the first two subsequent field values in the raw data.

InfluxDB calculates the difference between the field values (subtracts the first field value from the second field value) and then normalizes that value to the three-minute rate of change (dividing the difference between the field values’ timestamps in minutes (6m) by the specified interval (2m)):

(2.379 - 2.352)  / (6m / 2m)

Calculate the derivative between the field values associated with a field key and include several clauses

Return the one-second rate of change between water_level field values in the h2o_feet measurement in time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to one and offsets results by two points.

SELECT DERIVATIVE("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' ORDER BY time DESC LIMIT 1 OFFSET 2

name: h2o_feet

The only result (0.0000388889) is the one-second rate of change between the relevant subsequent field values in the raw data. InfluxDB calculates the difference between the field values (subtracts the first field value from the second field value) and then normalizes that value to the one-second rate of change (dividing the difference between the field values’ timestamps in seconds (360) by the specified rate of change (1s)):

(2.379 - 2.352) / (360s / 1s)

Advanced syntax

SELECT DERIVATIVE(<function> ([ * | <field_key> | /<regular_expression>/ ]) [ , <unit> ] ) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the DERIVATIVE() function to those results.

The unit argument is an integer followed by a duration and it is optional. If the query does not specify the unit the unit defaults to the GROUP BY time() interval. Note that this behavior is different from the basic syntax’s default behavior.

DERIVATIVE() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the derivative of mean values

Return the 12-minute rate of change between mean water_levels that are calculated at 12-minute intervals.

SELECT DERIVATIVE(MEAN("water_level")) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' GROUP BY time(12m)

name: h2o_feet

Calculate the derivative of mean values and specify the unit option

Return the six-minute rate of change between average water_levels that are calculated at 12-minute intervals.

SELECT DERIVATIVE(MEAN("water_level"),6m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' GROUP BY time(12m)

name: h2o_feet

DIFFERENCE()

Returns the result of subtraction between subsequent field values.

Syntax

SELECT DIFFERENCE( [ * | <field_key> | /<regular_expression>/ ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

DIFFERENCE(field_key)
Returns the difference between subsequent field values associated with the field key.

DIFFERENCE(/regular_expression/)
Returns the difference between subsequent field values associated with each field key that matches the regular expression.

DIFFERENCE(*)
Returns the difference between subsequent field values associated with each field key in the measurement.

DIFFERENCE() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use DIFFERENCE() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the difference between the field values associated with a field key

Return the difference between the subsequent field values in the water_level field key and in the h2o_feet measurement.

SELECT DIFFERENCE("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the difference between the field values associated with each field key in a measurement

Return the difference between the subsequent field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

SELECT DIFFERENCE(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the difference between the field values associated with each field key that matches a regular expression

Return the difference between the subsequent field values for each field key that stores numeric values and includes the word water in the h2o_feet measurement.

SELECT DIFFERENCE(/water/) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the difference between the field values associated with a field key and include several clauses

SELECT DIFFERENCE("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 2 OFFSET 2

name: h2o_feet

Return the difference between the subsequent field values in the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. They query also limits the number of points returned to two and offsets results by two points.

Advanced syntax

SELECT DIFFERENCE(<function>( [ * | <field_key> | /<regular_expression>/ ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the DIFFERENCE() function to those results.

DIFFERENCE() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the difference between maximum values

Return the difference between maximum water_levels that are calculated at 12-minute intervals.

SELECT DIFFERENCE(MAX("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

ELAPSED()

Returns the difference between subsequent field value’s timestamps.

Syntax

SELECT ELAPSED( [ * | <field_key> | /<regular_expression>/ ] [ , <unit> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

InfluxDB calculates the difference between subsequent timestamps. The unit option is an integer followed by a duration and it determines the unit of the returned difference. If the query does not specify the unit option the query returns the difference between timestamps in nanoseconds.

ELAPSED(field_key)
Returns the difference between subsequent timestamps associated with the field key.

ELAPSED(/regular_expression/)
Returns the difference between subsequent timestamps associated with each field key that matches the regular expression.

ELAPSED(*)
Returns the difference between subsequent timestamps associated with each field key in the measurement.

ELAPSED() supports all field value data types.

Examples

The examples use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:12:00Z'

name: h2o_feet

Calculate the elapsed time between field values associated with a field key

Return the elapsed time (in nanoseconds) between subsequent timestamps in the water_level field key and in the h2o_feet measurement.

SELECT ELAPSED("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:12:00Z'

name: h2o_feet

Calculate the elapsed time between field values associated with a field key and specify the unit option

Return the elapsed time (in minutes) between subsequent timestamps in the water_level field key and in the h2o_feet measurement.

SELECT ELAPSED("water_level",1m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:12:00Z'

name: h2o_feet

Calculate the elapsed time between field values associated with each field key in a measurement and specify the unit option

Return the difference (in minutes) between subsequent timestamps associated with each field key in the h2o_feetmeasurement. The h2o_feet measurement has two field keys: level description and water_level.

SELECT ELAPSED(*,1m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:12:00Z'

name: h2o_feet

Calculate the elapsed time between field values associated with each field key that matches a regular expression and specify the unit option

Return the difference (in seconds) between subsequent timestamps associated with each field key that includes the word level in the h2o_feet measurement.

SELECT ELAPSED(/level/,1s) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:12:00Z'

name: h2o_feet

Calculate the elapsed time between field values associated with a field key and include several clauses

Return the difference (in milliseconds) between subsequent timestamps in the water_level field key and in the h2o_feet measurement in the time range between 2019-08-17T00:00:00Z and 2019-08-17T00:12:00Z with timestamps in descending order. The query also limits the number of points returned to one and offsets results by one point.

SELECT ELAPSED("water_level",1ms) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:12:00Z' ORDER BY time DESC LIMIT 1 OFFSET 1

name: h2o_feet

Notice that the result is negative; the ORDER BY time DESC clause sorts timestamps in descending order so ELAPSED() calculates the difference between timestamps in reverse order.

Common issues with ELAPSED()

ELAPSED() and units greater than the elapsed time

InfluxDB returns 0 if the unit option is greater than the difference between the timestamps.

Example

The timestamps in the h2o_feet measurement occur at six-minute intervals. If the query sets the unit option to one hour, InfluxDB returns 0:

SELECT ELAPSED("water_level",1h) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:12:00Z'

name: h2o_feet

ELAPSED() with GROUP BY time() clauses

The ELAPSED() function supports the GROUP BY time() clause but the query results aren’t particularly useful. Currently, an ELAPSED() query with a nested function and a GROUP BY time() clause simply returns the interval specified in the GROUP BY time() clause.

The GROUP BY time() clause determines the timestamps in the results; each timestamp marks the start of a time interval. That behavior also applies to nested selector functions (like FIRST() or MAX()) which would, in all other cases, return a specific timestamp from the raw data. Because the GROUP BY time() clause overrides the original timestamps, the ELAPSED() calculation always returns the same value as the GROUP BY time() interval.

Example

In the codeblock below, the first query attempts to use the ELAPSED() function with a GROUP BY time() clause to find the time elapsed (in minutes) between minimum water_levels. Returns 12 minutes for both time intervals.

To get those results, InfluxDB first calculates the minimum water_levels at 12-minute intervals. The second query in the codeblock shows the results of that step. The step is the same as using the MIN() function with the GROUP BY time() clause and without the ELAPSED() function. Notice that the timestamps returned by the second query are 12 minutes apart. In the raw data, the first result (2.0930000000) occurs at 2019-08-18T00:42:00Z but the GROUP BY time() clause overrides that original timestamp. Because the timestamps are determined by the GROUP BY time() interval and not by the original data, the ELAPSED() calculation always returns the same value as the GROUP BY time() interval.

SELECT ELAPSED(MIN("water_level"),1m) FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:36:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(12m)

name: h2o_feet

SELECT MIN("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:36:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(12m)

name: h2o_feet

The first point actually occurs at 2019-08-18T00:42:00Z, not 2019-08-18T00:36:00Z.

EXP()

Returns the exponential of the field value.

Syntax

SELECT EXP( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

EXP(field_key)
Returns the exponential of field values associated with the field key.

EXP(*)
Returns the exponential of field values associated with each field key in the measurement.

EXP() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use EXP() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the exponential of field values associated with a field key

Return the exponential of field values in the water_level field key in the h2o_feet measurement.

SELECT EXP("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the exponential of field values associated with each field key in a measurement

Return the exponential of field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

SELECT EXP(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the exponential of field values associated with a field key and include several clauses

SELECT EXP("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Return the exponentials of field values associated with the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

Advanced syntax

SELECT EXP(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the EXP() function to those results.

EXP() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the exponential of mean values

Return the exponential of mean water_levels that are calculated at 12-minute intervals.

SELECT EXP(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

FLOOR()

Returns the subsequent value rounded down to the nearest integer.

Syntax

SELECT FLOOR( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

FLOOR(field_key)
Returns the field values associated with the field key rounded down to the nearest integer.

FLOOR(*)
Returns the field values associated with each field key in the measurement rounded down to the nearest integer.

FLOOR() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use FLOOR() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the floor of field values associated with a field key

Return field values in the water_level field key in the h2o_feet measurement rounded down to the nearest integer.

SELECT FLOOR("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the floor of field values associated with each field key in a measurement

Return field values for each field key that stores numeric values in the h2o_feet measurement rounded down to the nearest integer. The h2o_feet measurement has one numeric field: water_level.

SELECT FLOOR(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the floor of field values associated with a field key and include several clauses

Return field values associated with the water_level field key rounded down to the nearest integer in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT FLOOR("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Advanced syntax

SELECT FLOOR(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the FLOOR() function to those results.

FLOOR() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate mean values rounded down to the nearest integer

Return the mean water_levels that are calculated at 12-minute intervals and rounds them up to the nearest integer.

SELECT FLOOR(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

HISTOGRAM()

InfluxQL does not currently support histogram generation. For information about creating histograms with data stored in InfluxDB, see Flux’s histogram() function.

LN()

Returns the natural logarithm of the field value.

Syntax

SELECT LN( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

LN(field_key)
Returns the natural logarithm of field values associated with the field key.

LN(*)
Returns the natural logarithm of field values associated with each field key in the measurement.

LN() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use LN() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the natural logarithm of field values associated with a field key

Return the natural logarithm of field values in the water_level field key in the h2o_feet measurement.

SELECT LN("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the natural logarithm of field values associated with each field key in a measurement

Return the natural logarithm of field values for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

SELECT LN(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the natural logarithm of field values associated with a field key and include several clauses

SELECT LN("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Return the natural logarithms of field values associated with the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

Advanced syntax

SELECT LN(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the LN() function to those results.

LN() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the natural logarithm of mean values

Return the natural logarithm of mean water_levels that are calculated at 12-minute intervals.

SELECT LN(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

LOG()

Returns the logarithm of the field value with base b.

Basic syntax

SELECT LOG( [ * | <field_key> ], <b> ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

LOG(field_key, b)
Returns the logarithm of field values associated with the field key with base b.

LOG(*, b)
Returns the logarithm of field values associated with each field key in the measurement with base b.

LOG() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use LOG() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the logarithm base 4 of field values associated with a field key

Return the logarithm base 4 of field values in the water_level field key in the h2o_feet measurement.

SELECT LOG("water_level", 4) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the logarithm base 4 of field values associated with each field key in a measurement

Return the logarithm base 4 of field values for each numeric field in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

SELECT LOG(*, 4) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the logarithm base 4 of field values associated with a field key and include several clauses

Return the logarithm base 4 of field values associated with the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT LOG("water_level", 4) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Advanced syntax

SELECT LOG(<function>( [ * | <field_key> ] ), <b>) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the LOG() function to those results.

LOG() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the logarithm base 4 of mean values

Return the logarithm base 4 of mean water_levels that are calculated at 12-minute intervals.

SELECT LOG(MEAN("water_level"), 4) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

LOG2()

Returns the logarithm of the field value to the base 2.

Basic syntax

SELECT LOG2( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

LOG2(field_key)
Returns the logarithm of field values associated with the field key to the base 2.

LOG2(*)
Returns the logarithm of field values associated with each field key in the measurement to the base 2.

LOG2() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use LOG2() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the logarithm base 2 of field values associated with a field key

Return the logarithm base 2 of field values in the water_level field key in the h2o_feet measurement.

SELECT LOG2("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the logarithm base 2 of field values associated with each field key in a measurement

SELECT LOG2(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Return the logarithm base 2 of field values for each numeric field in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

Calculate the logarithm base 2 of field values associated with a field key and include several clauses

Return the logarithm base 2 of field values associated with the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT LOG2("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Advanced syntax

SELECT LOG2(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the LOG2() function to those results.

LOG2() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the logarithm base 2 of mean values

Return the logarithm base 2 of mean water_levels that are calculated at 12-minute intervals.

SELECT LOG2(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

LOG10()

Returns the logarithm of the field value to the base 10.

Basic syntax

SELECT LOG10( [ * | <field_key> ] ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

LOG10(field_key)
Returns the logarithm of field values associated with the field key to the base 10.

LOG10(*)
Returns the logarithm of field values associated with each field key in the measurement to the base 10.

LOG10() supports int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use LOG10() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the logarithm base 10 of field values associated with a field key

Return the logarithm base 10 of field values in the water_level field key in the h2o_feet measurement.

SELECT LOG10("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the logarithm base 10 of field values associated with each field key in a measurement

Return the logarithm base 10 of field values for each numeric field in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

SELECT LOG10(*) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica'

name: h2o_feet

Calculate the logarithm base 10 of field values associated with a field key and include several clauses

Return the logarithm base 10 of field values associated with the water_level field key in the time range between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z with results in descending timestamp order. The query also limits the number of points returned to four and offsets results by two points.

SELECT LOG10("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' ORDER BY time DESC LIMIT 4 OFFSET 2

name: h2o_feet

Advanced syntax

SELECT LOG10(<function>( [ * | <field_key> ] )) FROM_clause [WHERE_clause] GROUP_BY_clause [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

The advanced syntax requires a GROUP BY time() clause and a nested InfluxQL function. The query first calculates the results for the nested function at the specified GROUP BY time() interval and then applies the LOG10() function to those results.

LOG10() supports the following nested functions: COUNT(), MEAN(), MEDIAN(), MODE(), SUM(), FIRST(), LAST(), MIN(), MAX(), and PERCENTILE().

Examples

Calculate the logarithm base 10 of mean values

Return the logarithm base 10 of mean water_levels that are calculated at 12-minute intervals.

SELECT LOG10(MEAN("water_level")) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(12m)

name: h2o_feet

MOVING_AVERAGE()

Returns the rolling average across a window of subsequent field values.

Basic syntax

SELECT MOVING_AVERAGE( [ * | <field_key> | /<regular_expression>/ ] , <N> ) FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

MOVING_AVERAGE() calculates the rolling average across a window of N subsequent field values. The N argument is an integer and it is required.

MOVING_AVERAGE(field_key,N)
Returns the rolling average across N field values associated with the field key.

MOVING_AVERAGE(/regular_expression/,N)
Returns the rolling average across N field values associated with each field key that matches the regular expression.

MOVING_AVERAGE(*,N)
Returns the rolling average across N field values associated with each field key in the measurement.

MOVING_AVERAGE() int64 and float64 field value data types.

Supports GROUP BY clauses that group by tags but not GROUP BY clauses that group by time. To use MOVING_AVERAGE() with a GROUP BY time() clause, see Advanced syntax.

Examples

The examples below use the following subsample of the NOAA water sample data:

SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z'

name: h2o_feet