Documentation

InfluxQL transformation functions

This page documents an earlier version of InfluxDB. InfluxDB v2.5 is the latest stable version. View this page in the v2.5 documentation.

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

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

Basic syntax

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

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

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

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

Examples

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

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

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

Advanced syntax

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

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

ABS() supports the following nested functions:

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

Examples

Calculate the absolute values of mean values

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:

timea
2018-06-24T12:01:00Z-0.774984088561186
2018-06-24T12:02:00Z-0.921037167720451
2018-06-24T12:04:00Z-0.905980032168252
2018-06-24T12:05:00Z-0.891164752631417
2018-06-24T12:09:00Z0.416579917279588
2018-06-24T12:10:00Z0.328968116955350
2018-06-24T12:11:00Z0.263585064411983

Calculate the arccosine of field values associated with a field key

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

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

Advanced syntax

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

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

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

Examples

Calculate the arccosine of mean values

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:

timea
2018-06-24T12:01:00Z-0.774984088561186
2018-06-24T12:02:00Z-0.921037167720451
2018-06-24T12:04:00Z-0.905980032168252
2018-06-24T12:05:00Z-0.891164752631417
2018-06-24T12:09:00Z0.416579917279588
2018-06-24T12:10:00Z0.328968116955350
2018-06-24T12:11:00Z0.263585064411983

Calculate the arcsine of field values associated with a field key

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

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

Advanced syntax

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

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

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

Examples

Calculate the arcsine of mean values

ATAN()

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

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

Basic syntax

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

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

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

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

Examples

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

Calculate the arctangent of field values associated with a field key

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

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

Advanced syntax

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

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

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

Examples of advanced syntax

Calculate the arctangent of mean values

ATAN2()

Returns the the arctangent of y/x in radians.

Basic syntax

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

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

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

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

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

Examples

The examples below use sample_test.txt.

Calculate the arctangent of field_key_b over field_key_a

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

Calculate the arctangents of field values and include several clauses

Advanced syntax

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

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

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

Examples

Calculate arctangents of mean values

CEIL()

Returns the subsequent value rounded up to the nearest integer.

Basic syntax

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

Calculate the ceiling of field values associated with a field key

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

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

Advanced syntax

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

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

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

Examples

Calculate mean values rounded up to the nearest integer

COS()

Returns the cosine of the field value.

Basic syntax

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

Calculate the cosine of field values associated with a field key

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

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

Advanced syntax

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

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

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

Examples

Calculate the cosine of mean values

CUMULATIVE_SUM()

Returns the running total of subsequent field values.

Basic syntax

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

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

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

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

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

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

Advanced syntax

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

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

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

Examples

Calculate the cumulative sum of mean values

DERIVATIVE()

Returns the rate of change between subsequent field values.

Basic syntax

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

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

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

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

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

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

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

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

Advanced syntax

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

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

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

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

Examples

Calculate the derivative of mean values

Calculate the derivative of mean values and specify the unit option

DIFFERENCE()

Returns the result of subtraction between subsequent field values.

Syntax

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

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

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

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

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

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

Advanced syntax

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

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

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

Examples

Calculate the difference between maximum values

ELAPSED()

Returns the difference between subsequent field value’s timestamps.

Syntax

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

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

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

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

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

ELAPSED() supports all field value data types.

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000

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

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

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

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

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

Common issues with ELAPSED()

ELAPSED() and units greater than the elapsed time

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

Example

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

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

name: h2o_feet

timeelapsed
2019-08-18T00:06:00Z0.0000000000
2019-08-18T00:12:00Z0.0000000000

ELAPSED() with GROUP BY time() clauses

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

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

Example

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

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

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

name: h2o_feet

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

name: h2o_feet

timemin
2019-08-18T00:36:00Z2.0930000000
2019-08-18T00:48:00Z2.0870000000

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

EXP()

Returns the exponential of the field value.

Syntax

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

Calculate the exponential of field values associated with a field key

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

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

Advanced syntax

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

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

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

Examples

Calculate the exponential of mean values

FLOOR()

Returns the subsequent value rounded down to the nearest integer.

Syntax

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

Calculate the floor of field values associated with a field key

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

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

Advanced syntax

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

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

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

Examples

Calculate mean values rounded down to the nearest integer

HISTOGRAM()

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

LN()

Returns the natural logarithm of the field value.

Syntax

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

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

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

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

Advanced syntax

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

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

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

Examples

Calculate the natural logarithm of mean values

LOG()

Returns the logarithm of the field value with base b.

Basic syntax

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

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

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

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

Advanced syntax

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

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

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

Examples

Calculate the logarithm base 4 of mean values

LOG2()

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

Basic syntax

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

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

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

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

Advanced syntax

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

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

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

Examples

Calculate the logarithm base 2 of mean values

LOG10()

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

Basic syntax

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

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

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

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

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

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

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

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

Advanced syntax

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

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

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

Examples

Calculate the logarithm base 10 of mean values

MOVING_AVERAGE()

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

Basic syntax

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

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

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

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

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

MOVING_AVERAGE() int64 and float64 field value data types.

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

Calculate the moving average of the field values associated with a field key

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

Calculate the moving average of the field values associated with each field key that matches a regular expression

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

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

Calculate the moving average of maximum values

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'

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

Calculate field values associated with a field key to the power of 4

Calculate field values associated with each field key in a measurement to the power of 4

Calculate field values associated with a field key to the power of 4 and include several clauses

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

Calculate mean values to the power of 4

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/v2.4/. 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'

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

Round field values associated with a field key

Round field values associated with each field key in a measurement

Round field values associated with a field key and include several clauses

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

Calculate mean values rounded to the nearest integer

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'

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

Calculate the sine of field values associated with a field key

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

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

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

Calculate the sine of mean values

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'

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

Calculate the square root of field values associated with a field key

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

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

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

Calculate the square root of mean values

TAN()

Returns the tangent of the field value.

Basic syntax

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

TAN(field_key)
Returns the tangent of field values associated with the field key.

TAN(*)
Returns the tangent of field values associated with each field key in the measurement.

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

Examples

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

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

name: h2o_feet

timewater_level
2019-08-18T00:00:00Z2.3520000000
2019-08-18T00:06:00Z2.3790000000
2019-08-18T00:12:00Z2.3430000000
2019-08-18T00:18:00Z2.3290000000
2019-08-18T00:24:00Z2.2640000000
2019-08-18T00:30:00Z2.2670000000

Calculate the tangent of field values associated with a field key

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

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

Advanced syntax

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

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

Examples

Calculate the tangent of mean values


Was this page helpful?

Thank you for your feedback!


Set your InfluxDB URL

Upgrade to InfluxDB Cloud or InfluxDB 2.0!

InfluxDB Cloud and InfluxDB OSS 2.0 ready for production.