Transformations

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

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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:

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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:

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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.

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

ATAN2()

Returns 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.

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: data {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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' 

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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)

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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)

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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)

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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)

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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)

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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.

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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.

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% note %}} The first point actually occurs at 2019-08-18T00:42:00Z, not 2019-08-18T00:36:00Z. {{% /note %}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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.

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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.

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

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.

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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

{{< expand-wrapper >}}

{{% expand "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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "Calculate the moving average of the field values associated with a field key" %}}

Return the rolling average across a two-field-value window for the water_level field key and the h2o_feet measurement.

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

The first result (2.3655000000) is the average of the first two points in the raw data: (2.3520000000 + 2.3790000000) / 2). The second result (2.3610000000) is the average of the second two points in the raw data: (2.3790000000 + 2.3430000000) / 2).

{{% /expand %}}

{{% expand "Calculate the moving average of the field values associated with each field key in a measurement" %}}

Return the rolling average across a three-field-value window for each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement has one numeric field: water_level.

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the moving average of the field values associated with each field key that matches a regular expression" %}}

Return the rolling average across a four-field-value window for each numeric field with a field key that includes the word level in the h2o_feet measurement.

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the moving average of the field values associated with a field key and include several clauses" %}}

Return the rolling average across a two-field-value window for the water_level field key in the h2o_feet measurement 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 two and offsets results by three points.

SELECT MOVING_AVERAGE("water_level",2) 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 2 OFFSET 3

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

Advanced syntax

SELECT MOVING_AVERAGE(<function> ([ * | <field_key> | /<regular_expression>/ ]) , N ) 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 MOVING_AVERAGE() function to those results.

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

Examples

{{< expand-wrapper >}}

{{% expand "Calculate the moving average of maximum values" %}}

Return the rolling average across a two-value window of maximum water_levels that are calculated at 12-minute intervals.

SELECT MOVING_AVERAGE(MAX("water_level"),2) 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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

NON_NEGATIVE_DERIVATIVE()

Returns the non-negative rate of change between subsequent field values. Non-negative rates of change include positive rates of change and rates of change that equal zero.

Basic syntax

SELECT NON_NEGATIVE_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). NON_NEGATIVE_DERIVATIVE() returns only positive rates of change or rates of change that equal zero.

NON_NEGATIVE_DERIVATIVE(field_key)
Returns the non-negative rate of change between subsequent field values associated with the field key.

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

NON_NEGATIVE_DERIVATIVE(*)
Returns the non-negative rate of change between subsequent field values associated with each field key in the measurement.

NON_NEGATIVE_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 NON_NEGATIVE_DERIVATIVE() with a GROUP BY time() clause, see Advanced syntax.

Examples

See the examples in the DERIVATIVE() documentation. NON_NEGATIVE_DERIVATIVE() behaves the same as the DERIVATIVE() function but NON_NEGATIVE_DERIVATIVE() returns only positive rates of change or rates of change that equal zero.

Advanced syntax

SELECT NON_NEGATIVE_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 NON_NEGATIVE_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. NON_NEGATIVE_DERIVATIVE() returns only positive rates of change or rates of change that equal zero.

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

Examples

See the examples in the DERIVATIVE() documentation. NON_NEGATIVE_DERIVATIVE() behaves the same as the DERIVATIVE() function but NON_NEGATIVE_DERIVATIVE() returns only positive rates of change or rates of change that equal zero.

NON_NEGATIVE_DIFFERENCE()

Returns the non-negative result of subtraction between subsequent field values. Non-negative results of subtraction include positive differences and differences that equal zero.

Basic syntax

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

NON_NEGATIVE_DIFFERENCE(field_key)
Returns the non-negative difference between subsequent field values associated with the field key.

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

NON_NEGATIVE_DIFFERENCE(*)
Returns the non-negative difference between subsequent field values associated with each field key in the measurement.

NON_NEGATIVE_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 NON_NEGATIVE_DIFFERENCE() with a GROUP BY time() clause, see Advanced syntax.

Examples

See the examples in the DIFFERENCE() documentation. NON_NEGATIVE_DIFFERENCE() behaves the same as the DIFFERENCE() function but NON_NEGATIVE_DIFFERENCE() returns only positive differences or differences that equal zero.

Advanced syntax

SELECT NON_NEGATIVE_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 NON_NEGATIVE_DIFFERENCE() function to those results.

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

Examples

See the examples in the DIFFERENCE() documentation. NON_NEGATIVE_DIFFERENCE() behaves the same as the DIFFERENCE() function but NON_NEGATIVE_DIFFERENCE() returns only positive differences or differences that equal zero.

POW()

Returns the field value to the power of x.

Basic syntax

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

POW(field_key, x)
Returns the field values associated with the field key to the power of x.

POW(*, x)
Returns the field values associated with each field key in the measurement to the power of x.

POW() 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 POW() 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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "Calculate field values associated with a field key to the power of 4" %}}

Return field values in the water_level field key in the h2o_feet measurement multiplied to a power of 4.

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate field values associated with each field key in a measurement to the power of 4" %}}

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

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate field values associated with a field key to the power of 4 and include several clauses" %}}

Return field values associated with the water_level field key multiplied to the power of 4 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 POW("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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

Advanced syntax

SELECT POW(<function>( [ * | <field_key> ] ), <x>) 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 POW() function to those results.

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

Examples

{{< expand-wrapper >}}

{{% expand "Calculate mean values to the power of 4" %}}

Return mean water_levels that are calculated at 12-minute intervals multiplied to the power of 4.

SELECT POW(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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

ROUND()

Returns the subsequent value rounded to the nearest integer.

Basic syntax

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

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

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

ROUND() 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](/influxdb/version/. To use ROUND() 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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "Round field values associated with a field key" %}}

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

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Round field values associated with each field key in a measurement" %}}

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

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Round field values associated with a field key and include several clauses" %}}

Return field values associated with the water_level field key rounded 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 ROUND("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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

Advanced syntax

SELECT ROUND(<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 ROUND() function to those results.

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

Examples

{{< expand-wrapper >}}

{{% expand "Calculate mean values rounded to the nearest integer" %}}

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

SELECT ROUND(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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

SIN()

Returns the sine of the field value.

Basic syntax

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

SIN(field_key)
Returns the sine of field values associated with the field key.

SIN(*)
Returns the sine of field values associated with each field key in the measurement.

SIN() 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 SIN() 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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "Calculate the sine of field values associated with a field key" %}}

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

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the sine of field values associated with each field key in a measurement" %}}

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

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the sine of field values associated with a field key and include several clauses" %}}

Return the sine 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 SIN("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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

Advanced syntax

SELECT SIN(<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 SIN() function to those results.

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

Examples

{{< expand-wrapper >}}

{{% expand "Calculate the sine of mean values" %}}

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

SELECT SIN(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)

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

SQRT()

Returns the square root of field value.

Basic syntax

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

SQRT(field_key)
Returns the square root of field values associated with the field key.

SQRT(*)
Returns the square root field values associated with each field key in the measurement.

SQRT() 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 SQRT() 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'

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{< expand-wrapper >}}

{{% expand "Calculate the square root of field values associated with a field key" %}}

Return the square roots of field values in the water_level field key in the h2o_feet measurement.

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the square root of field values associated with each field key in a measurement" %}}

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

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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{% expand "Calculate the square root of field values associated with a field key and include several clauses" %}}

Return the square roots 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 SQRT("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

{{% influxql/table-meta %}} name: h2o_feet {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}

Advanced syntax

SELECT SQRT(<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 SQRT() function to those results.

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

Examples

{{< expand-wrapper >}}

{{% expand "Calculate the square root of mean values" %}}

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

SELECT SQRT(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)