Search
Document Center

# Transformation functions

Last Updated: May 19, 2022

This topic describes the syntax and parameters of transformation functions. It also provides multiple examples.

## ABS()

Returns the absolute values for field values.

Basic syntax

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

Description

`ABS(field_key)`: returns the absolute values for the field values of the specified field key.

`ABS(*)`: returns the absolute values for the field values of each field key in the specified measurement.

The `ABS()` function supports INT64 or FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, the basic syntax does not support the GROUP BY clauses that are used to group data by time.

Example 1: Calculate the absolute values for the field values of the specified field key

``````SELECT ABS("a") FROM "data" WHERE time >='2018-06-24T12:00:00Z' AND time <='2018-06-24T12:05:00Z'

name: data
time                 abs
-------
15298416000000000001.33909108671076
15298416600000000000.774984088561186
15298417200000000000.921037167720451
15298417800000000001.73880754843378
15298418400000000000.905980032168252
15298419000000000000.891164752631417``````

This query returns the absolute values of the field values for the `a` field key in the`data` measurement.

Example 2: Calculate the absolute values for the field values of each field key in the specified measurement

``````SELECT ABS(*) FROM "data" WHERE time >='2018-06-24T12:00:00Z' AND time <='2018-06-24T12:05:00Z'

name: data
time                 abs_a              abs_b
--------------
15298416000000000001.339091086710760.163643058925645
15298416600000000000.7749840885611860.137034364053949
15298417200000000000.9210371677204510.482943221384294
15298417800000000001.738807548433780.0729732928756677
15298418400000000000.9059800321682521.77857552719844
15298419000000000000.8911647526314170.741147445214238``````

This query returns the absolute values of the field values for each field key that stores numeric values in the `data` measurement. The `data` measurement contains the `a` and `b` fields.

Example 3: Calculate the absolute values for the field values of the specified field key by using a query that includes multiple clauses

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

name: data
time                 abs
-------
15298417800000000001.73880754843378
15298417200000000000.921037167720451
15298416600000000000.774984088561186
15298416000000000001.33909108671076``````

This query returns the absolute values of the field values for the `a` field key in the `data` measurement. The specified time range is from `2018-06-24T12:00:00Z` to `2018-06-24T12:05:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The SIN() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries calculate the absolute values of these results.

ABS() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the absolute values of the average field values for the specified field key

``````SELECT ABS(MEAN("a")) FROM "data" WHERE time >='2018-06-24T12:00:00Z' AND time <='2018-06-24T13:00:00Z' GROUP BY time(12m)

time                 abs
-------
15298416000000000000.3960977256302787
15298423200000000000.0010541018316373302
15298430400000000000.04494733240283668
15298437600000000000.2553594777104415
15298444800000000000.20382988543108413
15298452000000000000.790836070736962``````

This query returns the absolute value of each average field value that is calculated based on a 12-minute interval for the `a` field key.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `a` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()`function and the `GROUP BY time()` clause, but does not use the `ABS()` function.

``````SELECT MEAN("a") FROM "data" WHERE time >='2018-06-24T12:00:00Z' AND time <='2018-06-24T13:00:00Z' GROUP BY time(12m)

name: data
time                 mean
--------
1529841600000000000-0.3960977256302787
15298423200000000000.0010541018316373302
15298430400000000000.04494733240283668
15298437600000000000.2553594777104415
15298444800000000000.20382988543108413
1529845200000000000-0.790836070736962``````

Then, TSDB for InfluxDB® calculates the absolute value of each average field value.

## ACOS()

Returns the arc cosines for the field values. These arc cosines are returned in radians. The field values range from -1 to +1.

Basic syntax

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

Description

`ACOS(field_key)`: returns the arc cosines of the field values for the specified field key.

`ACOS(*)`: returns the arc cosines of the field values for each field key in the specified measurement.

The `ACOS()` function supports INT64 and FLOAT64 field values. The field values range from -1 to +1.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag, but does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `ACOS()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data is used in the examples that are provided in this section. The data describes the simulated ratio of the occupied park space to the total park space. Note: All the field values fall within the valid range for the field values of the `ACOS()` function. The valid field values range from -1 to +1.

``````SELECT "of_capacity" FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'

name: park_occupancy
time                  capacity
------------
2017-05-01T00:00:00Z0.83
2017-05-02T00:00:00Z0.3
2017-05-03T00:00:00Z0.84
2017-05-04T00:00:00Z0.22
2017-05-05T00:00:00Z0.17
2017-05-06T00:00:00Z0.77
2017-05-07T00:00:00Z0.64
2017-05-08T00:00:00Z0.72
2017-05-09T00:00:00Z0.16``````

Example 1: Calculate the arc cosines of the field values for the specified field key

``````SELECT ACOS("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'

name: park_occupancy
time                  acos
--------
2017-05-01T00:00:00Z0.591688642426544
2017-05-02T00:00:00Z1.266103672779499
2017-05-03T00:00:00Z0.5735131044230969
2017-05-04T00:00:00Z1.3489818562981022
2017-05-05T00:00:00Z1.399966657665792
2017-05-06T00:00:00Z0.6919551751263169
2017-05-07T00:00:00Z0.8762980611683406
2017-05-08T00:00:00Z0.7669940078618667
2017-05-09T00:00:00Z1.410105673842986``````

This query returns the arc cosines of the field values for the `of_capacity` field key in the `park_occupancy` measurement

Example 2: Calculate the arc cosines of the field values for each field key in the specified measurement

``````SELECT ACOS(*) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'

name: park_occupancy
time                  acos_of_capacity
-----------------
2017-05-01T00:00:00Z0.591688642426544
2017-05-02T00:00:00Z1.266103672779499
2017-05-03T00:00:00Z0.5735131044230969
2017-05-04T00:00:00Z1.3489818562981022
2017-05-05T00:00:00Z1.399966657665792
2017-05-06T00:00:00Z0.6919551751263169
2017-05-07T00:00:00Z0.8762980611683406
2017-05-08T00:00:00Z0.7669940078618667
2017-05-09T00:00:00Z1.410105673842986``````

This query returns the arc cosines of the field values for each field key that stores numeric values in the `park_occupancy` measurement. The `park_occupancy` measurement contains only the numeric `of_capacity` field.

Example 3: Calculate the arc cosines of the field values for the specified field key by using a query that includes multiple clauses

``````SELECT ACOS("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: park_occupancy
time                  acos
--------
2017-05-07T00:00:00Z0.8762980611683406
2017-05-06T00:00:00Z0.6919551751263169
2017-05-05T00:00:00Z1.399966657665792
2017-05-04T00:00:00Z1.3489818562981022``````

This query returns the arc cosines of the field values for the `of_capacity` field key in the `park_occupancy` measurement. The specified time range is from `2017-05-01T00:00:00Z` to `2017-05-09T00:00:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The ACOS() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries calculate the arc cosines of these results.

ACOS() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the arc cosines of the average field values for the specified field key

``````SELECT ACOS(MEAN("of_capacity")) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)

name: park_occupancy
time                  acos
--------
2017-04-30T00:00:00Z0.9703630732143733
2017-05-03T00:00:00Z1.1483422646081407
2017-05-06T00:00:00Z0.7812981174487247
2017-05-09T00:00:00Z1.410105673842986``````

This query returns the arc cosines of the average field values for the `of_capacity` field key. The average field values are calculated based on a 3-day interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `of_capacity` field key based on a 3-day interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `ACOS()` function.

``````SELECT MEAN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)

name: park_occupancy
time                  mean
--------
2017-04-30T00:00:00Z0.565
2017-05-03T00:00:00Z0.41
2017-05-06T00:00:00Z0.71
2017-05-09T00:00:00Z0.16``````

Then, TSDB for InfluxDB® calculates the arc cosine of each average field value.

## ASIN()

Returns the arcsines of the field values for the specified field keys. The arcsines are returned in radians. The field values range from -1 to +1.

Basic syntax

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

Description

`ASIN(field_key)`: returns the arcsines of the field values for the specified field key.

`ASIN(*)`: returns the arcsines of the field values for each field key in the specified measurement.

The `ASIN()` function supports INT64 and FLOAT64 field values. The field values range from -1 to +1.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `ASIN()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data is used in the examples that are provided in this section. The data describes the simulated ratio of the occupied park space to the total park space. All the field values fall within the valid range for the field values of the `ASIN()` function. The valid field values range from -1 to +1.

``````> SELECT "of_capacity" FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'

name: park_occupancy
time                  capacity
------------
2017-05-01T00:00:00Z0.83
2017-05-02T00:00:00Z0.3
2017-05-03T00:00:00Z0.84
2017-05-04T00:00:00Z0.22
2017-05-05T00:00:00Z0.17
2017-05-06T00:00:00Z0.77
2017-05-07T00:00:00Z0.64
2017-05-08T00:00:00Z0.72
2017-05-09T00:00:00Z0.16``````

Example 1: Calculate the arcsines of the field values for the specified field key

``````SELECT ASIN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'

name: park_occupancy
time                  asin
--------
2017-05-01T00:00:00Z0.9791076843683526
2017-05-02T00:00:00Z0.3046926540153975
2017-05-03T00:00:00Z0.9972832223717997
2017-05-04T00:00:00Z0.22181447049679442
2017-05-05T00:00:00Z0.1708296691291045
2017-05-06T00:00:00Z0.8788411516685797
2017-05-07T00:00:00Z0.6944982656265559
2017-05-08T00:00:00Z0.8038023189330299
2017-05-09T00:00:00Z0.1606906529519106``````

This query returns the arcsines of the field values for the `of_capacity` field key in the `park_occupancy` measurement.

Example 2: Calculate the arcsines of the field values for each field key in the specified measurement

``````SELECT ASIN(*) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'

name: park_occupancy
time                  asin_of_capacity
-----------------
2017-05-01T00:00:00Z0.9791076843683526
2017-05-02T00:00:00Z0.3046926540153975
2017-05-03T00:00:00Z0.9972832223717997
2017-05-04T00:00:00Z0.22181447049679442
2017-05-05T00:00:00Z0.1708296691291045
2017-05-06T00:00:00Z0.8788411516685797
2017-05-07T00:00:00Z0.6944982656265559
2017-05-08T00:00:00Z0.8038023189330299
2017-05-09T00:00:00Z0.1606906529519106``````

This query returns the arcsines of the field values for each field key that stores numeric values in the `park_occupancy` measurement. The `park_occupancy` measurement contains only the numeric `of_capacity` field.

Example 3: Calculate the arcsines of the field values for the specified field key by using a query that includes multiple clauses

``````SELECT ASIN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: park_occupancy
time                  asin
--------
2017-05-07T00:00:00Z0.6944982656265559
2017-05-06T00:00:00Z0.8788411516685797
2017-05-05T00:00:00Z0.1708296691291045
2017-05-04T00:00:00Z0.22181447049679442``````

This query returns the arcsines of the field values for the `of_capacity` field key in the `park_occupancy` measurement. The specified time range is from `2017-05-01T00:00:00Z` to `2017-05-09T00:00:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The ASIN() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries calculate the arcsines of these results.

ASIN() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the arcsines of the average field values for the specified field key

``````> SELECT ASIN(MEAN("of_capacity")) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)

name: park_occupancy
time                  asin
--------
2017-04-30T00:00:00Z0.6004332535805232
2017-05-03T00:00:00Z0.42245406218675574
2017-05-06T00:00:00Z0.7894982093461719
2017-05-09T00:00:00Z0.1606906529519106``````

This query returns the arcsines of the average field values for the `of_capacity` field key. The average field values are calculated based on a 3-day interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `of_capacity` field key based on a 3-day interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `ASIN()` function.

``````> SELECT MEAN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)

name: park_occupancy
time                  mean
--------
2017-04-30T00:00:00Z0.565
2017-05-03T00:00:00Z0.41
2017-05-06T00:00:00Z0.71
2017-05-09T00:00:00Z0.16``````

Then, TSDB for InfluxDB® calculates the arcsine of each average field value.

## ATAN()

Returns the arc tangents of the field values for the specified field keys. The arc tangents are returned in radians. The field values range from -1 to +1.
Basic syntax
``SELECT ATAN([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]``

Description

`ATAN(field_key)`: returns the arc tangents of the field values for the specified field key.

`ATAN(*)`: returns the arc tangents of the field values for each field key in the specified measurement.

The `ATAN()` function supports INT64 and FLOAT64 field values. The field values range from -1 to +1.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `ATAN()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data is used in the examples that are provided in this section. The data describes the simulated ratio of the occupied park space to the total park space. Note: All the field values fall within the valid range for the field values of the `ATAN()` function. The valid field values range from -1 to 1.

``````> SELECT "of_capacity" FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'

name: park_occupancy
time                  capacity
------------
2017-05-01T00:00:00Z0.83
2017-05-02T00:00:00Z0.3
2017-05-03T00:00:00Z0.84
2017-05-04T00:00:00Z0.22
2017-05-05T00:00:00Z0.17
2017-05-06T00:00:00Z0.77
2017-05-07T00:00:00Z0.64
2017-05-08T00:00:00Z0.72
2017-05-09T00:00:00Z0.16``````

Example 1: Calculate the arc tangents for the field values of the specified field key

``````SELECT ATAN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'

name: park_occupancy
time                  atan
--------
2017-05-01T00:00:00Z0.6927678353971222
2017-05-02T00:00:00Z0.2914567944778671
2017-05-03T00:00:00Z0.6986598247214632
2017-05-04T00:00:00Z0.2165503049760893
2017-05-05T00:00:00Z0.16839015714752992
2017-05-06T00:00:00Z0.6561787179913948
2017-05-07T00:00:00Z0.5693131911006619
2017-05-08T00:00:00Z0.6240230529767568
2017-05-09T00:00:00Z0.1586552621864014``````

This query returns the arc tangents of the field values for the `of_capacity` field key in the `park_occupancy` measurement.

Example 2: Calculate the arc tangents of the field values for each field key in the specified measurement

``````SELECT ATAN(*) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z'

name: park_occupancy
time                  atan_of_capacity
-----------------
2017-05-01T00:00:00Z0.6927678353971222
2017-05-02T00:00:00Z0.2914567944778671
2017-05-03T00:00:00Z0.6986598247214632
2017-05-04T00:00:00Z0.2165503049760893
2017-05-05T00:00:00Z0.16839015714752992
2017-05-06T00:00:00Z0.6561787179913948
2017-05-07T00:00:00Z0.5693131911006619
2017-05-08T00:00:00Z0.6240230529767568
2017-05-09T00:00:00Z0.1586552621864014``````

This query returns the arc tangents of the field values for each field key that stores numeric values in the `park_occupancy` measurement. The `park_occupancy` measurement contains only the numeric `of_capacity` field.

Example 3: Calculate the arc tangents of the field values for the specified field key by using a query that includes multiple clauses

``````SELECT ATAN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: park_occupancy
time                  atan
--------
2017-05-07T00:00:00Z0.5693131911006619
2017-05-06T00:00:00Z0.6561787179913948
2017-05-05T00:00:00Z0.16839015714752992
2017-05-04T00:00:00Z0.2165503049760893``````

This query returns the arc tangents of the field values for the `of_capacity` field key in the `park_occupancy` measurement. The specified time range is from `2017-05-01T00:00:00Z` to `2017-05-09T00:00:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The ATAN() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries calculate the arc tangents of these results.

ATAN() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the arc tangents of the average field values for the specified field key

``````> SELECT ATAN(MEAN("of_capacity")) FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)

name: park_occupancy
time                 atan
--------
2017-04-30T00:00:00Z0.5142865412694495
2017-05-03T00:00:00Z0.3890972310552784
2017-05-06T00:00:00Z0.6174058917515726
2017-05-09T00:00:00Z0.1586552621864014``````

This query returns the arc tangents of the average field values for the `of_capacity` field. The average field values are calculated based on a 3-day interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `of_capacity` field key based on a 3-day interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `ATAN()` function.

``````> SELECT MEAN("of_capacity") FROM "park_occupancy" WHERE time >='2017-05-01T00:00:00Z' AND time <='2017-05-09T00:00:00Z' GROUP BY time(3d)

name: park_occupancy
time                  mean
--------
2017-04-30T00:00:00Z0.565
2017-05-03T00:00:00Z0.41
2017-05-06T00:00:00Z0.71
2017-05-09T00:00:00Z0.16``````

Then, TSDB for InfluxDB® calculates the arc tangent of each average field value.

## ATAN2()

Returns the arc tangents of the values that are calculated based on the `y/x` formula. The arc tangents are returned in radians.
Basic syntax
``SELECT ATAN2([*|<field_key>| num ],[<field_key>| num ])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]``

Description

`ATAN2(field_key_y, field_key_x)`: returns the arc tangents of the values that are calculated based on the formula: Field values of Field key y/Field values of Field key x.

`ATAN2(*, field_key_x)<br />`: returns the arc tangents of the values that are calculated based on the formula: Field values of each field key in the specified measurement/Field values of Field key x.

The `ATAN2()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `ATAN2()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following simulated flight data is used in the examples that are provided in this section:

``````> SELECT "altitude_ft","distance_ft" FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T12:10:00Z'

name: flight_data
time                  altitude_ft  distance_ft
--------------------------
2018-05-16T12:01:00Z102650094
2018-05-16T12:02:00Z254953576
2018-05-16T12:03:00Z403355208
2018-05-16T12:04:00Z557958579
2018-05-16T12:05:00Z706561213
2018-05-16T12:06:00Z858964807
2018-05-16T12:07:00Z1018067707
2018-05-16T12:08:00Z1177769819
2018-05-16T12:09:00Z1332172452
2018-05-16T12:10:00Z1488575881``````

Example 1: Calculate the arc tangents of the values that are calculated based on the formula: Field values of each field key in the specified measurement/Field values of Field key x

``````> SELECT ATAN2("altitude_ft","distance_ft") FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T12:10:00Z'

name: flight_data
time                  atan2
---------
2018-05-16T12:01:00Z0.020478631571881498
2018-05-16T12:02:00Z0.04754142349303296
2018-05-16T12:03:00Z0.07292147724575364
2018-05-16T12:04:00Z0.09495251193874832
2018-05-16T12:05:00Z0.11490822875441563
2018-05-16T12:06:00Z0.13176409347584003
2018-05-16T12:07:00Z0.14923587589682233
2018-05-16T12:08:00Z0.1671059946640312
2018-05-16T12:09:00Z0.18182893717409565
2018-05-16T12:10:00Z0.1937028631495223``````

This query returns the arc tangents of the values that are calculated based on the formula: Field values of the `altitude_ft` field key/Field values of the `distance_ft` field key. The two field keys are contained in the `flight_data` measurement.

Example 2: Calculate the arc tangents of the values that are calculated based on the formula: Field values of each field key in the specified measurement/Field values of Field key x

``````> SELECT ATAN2(*,"distance_ft") FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T12:10:00Z'

name: flight_data
time                  atan2_altitude_ft     atan2_distance_ft
--------------------------------------
2018-05-16T12:01:00Z0.0204786315718814980.7853981633974483
2018-05-16T12:02:00Z0.047541423493032960.7853981633974483
2018-05-16T12:03:00Z0.072921477245753640.7853981633974483
2018-05-16T12:04:00Z0.094952511938748320.7853981633974483
2018-05-16T12:05:00Z0.114908228754415630.7853981633974483
2018-05-16T12:06:00Z0.131764093475840030.7853981633974483
2018-05-16T12:07:00Z0.149235875896822330.7853981633974483
2018-05-16T12:08:00Z0.16710599466403120.7853981633974483
2018-05-16T12:09:00Z0.181828937174095650.7853981633974483
2018-05-16T12:10:00Z0.193702863149522340.7853981633974483``````

This query returns the arc tangents of the values that are calculated based on the formula: Field values of each field key that stores numeric values in the `flight_data` measurement/Field values of the `distance_ft` field key. The `flight_data` measurement contains the numeric `altitude_ft` and `distance_ft` fields.

Example 3: Calculate the arc tangents of the values by using a query that includes multiple clauses

``````> SELECT ATAN2("altitude_ft","distance_ft") FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T12:10:00Z' ORDER BY time DESC LIMIT 4 OFFSET 2

name: flight_data
time                  atan2
---------
2018-05-16T12:08:00Z0.1671059946640312
2018-05-16T12:07:00Z0.14923587589682233
2018-05-16T12:06:00Z0.13176409347584003
2018-05-16T12:05:00Z0.11490822875441563``````

This query returns the arc tangents of the values that are calculated based on the formula: Field values of the `altitude_ft` field key/Field values of the `distance_ft` field key. The specified time range is from `2018-05-16T12:10:00Z` to `2018-05-16T12:10:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The ATAN2() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries calculate the arc tangents (`ATAN2()`) of these results.

ATAN2() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the arc tangents of the values that are calculated based on the formula: Average field values of Field key y/Average field values of Field key x

``````> SELECT ATAN2(MEAN("altitude_ft"), MEAN("distance_ft")) FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T13:01:00Z' GROUP BY time(12m)

name: flight_data
time                  atan2
---------
2018-05-16T12:00:00Z0.133815587896842
2018-05-16T12:12:00Z0.2662716308351908
2018-05-16T12:24:00Z0.2958845306108965
2018-05-16T12:36:00Z0.23783439588429497
2018-05-16T12:48:00Z0.1906803720242831
2018-05-16T13:00:00Z0.17291511946158172``````

This query returns the arc tangents of the values that are calculated based on the formula: Average field values of the `altitude_ft` field key/Average field values of the `distance_ft` field key. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `altitude_ft` and `distance_ft` field keys based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `ATAN2()` function.

``````> SELECT MEAN("altitude_ft"), MEAN("distance_ft") FROM "flight_data" WHERE time >='2018-05-16T12:01:00Z' AND time <='2018-05-16T13:01:00Z' GROUP BY time(12m)

name: flight_data
time                  mean                mean_1
--------------
2018-05-16T12:00:00Z867464433.181818181816
2018-05-16T12:12:00Z26419.83333333333296865.25
2018-05-16T12:24:00Z40337.416666666664132326.41666666666
2018-05-16T12:36:00Z41149.583333333336169743.16666666666
2018-05-16T12:48:00Z41230.416666666664213600.91666666666
2018-05-16T13:00:00Z41184.5235799``````

Then, TSDB for InfluxDB® calculates the arc tangent of each average field value.

## CEIL()

Rounds each specified field value up to the nearest integer.

Basic syntax

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

Description

`CEIL(field_key)`: rounds each field value of the specified field key up to the nearest integer.

`CEIL(*)`: rounds each field value of all the field keys in the specified measurement up to the nearest integer.

The `CEIL()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `CEIL()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the `NOAA_water_database` dataset is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Round each field value of the specified field key up to the nearest integer

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

name: h2o_feet
time                  ceil
--------
2015-08-18T00:00:00Z3
2015-08-18T00:06:00Z3
2015-08-18T00:12:00Z3
2015-08-18T00:18:00Z3
2015-08-18T00:24:00Z3
2015-08-18T00:30:00Z3``````

The CEIL() function in this query rounds each field value of the `water_level` field key in the `h2o_feet` measurement up to the nearest integer.

Example 2: Round each field value of all the field keys in the specified measurement up to the nearest integer

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

name: h2o_feet
time                  ceil_water_level
--------------------
2015-08-18T00:00:00Z3
2015-08-18T00:06:00Z3
2015-08-18T00:12:00Z3
2015-08-18T00:18:00Z3
2015-08-18T00:24:00Z3
2015-08-18T00:30:00Z3``````

The CEIL() function in this query rounds each field value of all the specified field keys up to the nearest integer. The specified field keys store numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the `water_level` field.

Example 3: Round each field value of the specified field key up to the nearest integer by using a query that includes multiple clauses

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

name: h2o_feet
time                  ceil
--------
2015-08-18T00:18:00Z3
2015-08-18T00:12:00Z3
2015-08-18T00:06:00Z3
2015-08-18T00:00:00Z3``````

The CEIL() function in this query rounds each field value of the `water_level` field key up to the nearest integer. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The CEIL() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries apply the `CEIL()` function to these results.

CEIL() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Round each average field value up to the nearest integer

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

name: h2o_feet
time                  ceil
--------
2015-08-18T00:00:00Z3
2015-08-18T00:12:00Z3
2015-08-18T00:24:00Z3``````

The CEIL() function in this query rounds each average field value of the `water_level` field key up to the nearest integer. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `CEIL()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® rounds each average field value up to the nearest integer.

## COS()

Returns the cosines of the field values for the specified field keys.
Basic syntax
``SELECT COS([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]``

Description

`COS(field_key)`: returns the cosines of the field values for the specified field key.

`COS(*)`: returns the cosines of the field values for each field key in the specified measurement.

The `COS()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `COS()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the `NOAA_water_database` dataset is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the cosines of the field values for the specified field key

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

name: h2o_feet
time                  cos
-------
2015-08-18T00:00:00Z-0.47345017433543124
2015-08-18T00:06:00Z-0.5185922462666872
2015-08-18T00:12:00Z-0.4414407189100776
2015-08-18T00:18:00Z-0.5271163912192579
2015-08-18T00:24:00Z-0.45306786455514825
2015-08-18T00:30:00Z-0.4619598230611262``````

This query returns the cosines of the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the cosines of the field values for each field key in the specified measurement

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

name: h2o_feet
time                  cos_water_level
-------------------
2015-08-18T00:00:00Z-0.47345017433543124
2015-08-18T00:06:00Z-0.5185922462666872
2015-08-18T00:12:00Z-0.4414407189100776
2015-08-18T00:18:00Z-0.5271163912192579
2015-08-18T00:24:00Z-0.45306786455514825
2015-08-18T00:30:00Z-0.4619598230611262``````

This query returns the cosines of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet `measurement contains only the numeric `water_level` field.

## COS()

Returns the cosines of the field values for the specified field keys.
Basic syntax
``SELECT COS([*|<field_key>])[INTO_clause] FROM_clause [WHERE_clause][GROUP_BY_clause][ORDER_BY_clause][LIMIT_clause][OFFSET_clause][SLIMIT_clause][SOFFSET_clause]``

Description

`COS(field_key)`: returns the cosines of the field values for the specified field key.

`COS(*)`: returns the cosines of the field values for each field key in the specified measurement.

The `COS()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `COS()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the `NOAA_water_database` dataset is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the cosines of the field values for the specified field key

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

name: h2o_feet
time                  cos
-------
2015-08-18T00:00:00Z-0.47345017433543124
2015-08-18T00:06:00Z-0.5185922462666872
2015-08-18T00:12:00Z-0.4414407189100776
2015-08-18T00:18:00Z-0.5271163912192579
2015-08-18T00:24:00Z-0.45306786455514825
2015-08-18T00:30:00Z-0.4619598230611262``````

This query returns the cosines of the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the cosines of the field values for each field key in the specified measurement

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

name: h2o_feet
time                  cos_water_level
-------------------
2015-08-18T00:00:00Z-0.47345017433543124
2015-08-18T00:06:00Z-0.5185922462666872
2015-08-18T00:12:00Z-0.4414407189100776
2015-08-18T00:18:00Z-0.5271163912192579
2015-08-18T00:24:00Z-0.45306786455514825
2015-08-18T00:30:00Z-0.4619598230611262``````

This query returns the cosines of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

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

Description

The COS() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries calculate the cosines of these results.

COS() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the cosines of the average field values for the specified field key

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

name: h2o_feet
time                  cos
-------
2015-08-18T00:00:00Z-0.49618891270599885
2015-08-18T00:12:00Z-0.4848605136571181
2015-08-18T00:24:00Z-0.4575195627907578``````

This query returns the cosines of the average field values for the `water_level` field key. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `COS()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the cosine of each average field value.

## CUMULATIVE_SUM()

Returns the cumulative sum of the field values for each specified field key.

Basic syntax

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

Description

`CUMULATIVE_SUM(field_key)`: returns the cumulative sum of the field values for the specified field key.

`CUMULATIVE_SUM(/regular_expression/)`: returns the cumulative sum of the field values for each field key that matches the specified regular expression.

`CUMULATIVE_SUM(*)`: returns the cumulative sum of the field values for each field key in the specified measurement.

The `CUMULATIVE_SUM()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `CUMULATIVE_SUM()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the `NOAA_water_database` data set is used in the examples that are provided in this section:

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

name: h2o_feet
time                   water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the cumulative sum of the field values for the specified field key

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

name: h2o_feet
time                   cumulative_sum
------------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z4.18
2015-08-18T00:12:00Z6.208
2015-08-18T00:18:00Z8.334
2015-08-18T00:24:00Z10.375
2015-08-18T00:30:00Z12.426``````

This query returns the cumulative sum of the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the cumulative sum of the field values for each field key in the specified measurement

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

name: h2o_feet
time                   cumulative_sum_water_level
------------------------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z4.18
2015-08-18T00:12:00Z6.208
2015-08-18T00:18:00Z8.334
2015-08-18T00:24:00Z10.375
2015-08-18T00:30:00Z12.426``````

This query returns the cumulative sum of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Calculate the cumulative sum of field values for each field key that matches the specified regular expression

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

name: h2o_feet
time                   cumulative_sum_water_level
------------------------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z4.18
2015-08-18T00:12:00Z6.208
2015-08-18T00:18:00Z8.334
2015-08-18T00:24:00Z10.375
2015-08-18T00:30:00Z12.426``````

This query returns the cumulative sum of the field values for each field key that contains `water` and stores numeric values in the `h2o_feet` measurement.

Example 4: Calculate the cumulative sum of the field values for the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                  cumulative_sum
------------------
2015-08-18T00:18:00Z6.218
2015-08-18T00:12:00Z8.246
2015-08-18T00:06:00Z10.362
2015-08-18T00:00:00Z12.426``````

This query returns the cumulative sum of the field values for the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2. This indicates that the first two points are not returned.

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

Description

The CUMULATIVE_SUM() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries calculate the cumulative sum of these results.

CUMULATIVE_SUM() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the cumulative sum of the average field values for the specified field key

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

name: h2o_feet
time                   cumulative_sum
------------------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z4.167
2015-08-18T00:24:00Z6.213``````

This query returns the cumulative sum of the average field values for the `water_level` field key. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `CUMULATIVE_SUM()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the cumulative sum of the average field values. In the query results, the second point `4.167` is the sum of `2.09` and `2.077`, and the third point `6.213` is the sum of `2.09`, `2.077`, and `2.0460000000000003`.

## DERIVATIVE()

Returns the change rates or the derivatives for the field values of the specified field keys.

Basic syntax

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

Description

TSDB for InfluxDB® calculates the difference between two field values and converts the results into change rates. The change rates indicate how much the field values have changed based on the time granularity that is specified by the `unit` parameter. The value of the `unit` parameter is an integer that is suffixed by a unit of time. This parameter is optional. If you do not specify the `unit` parameter in your query, the `unit` parameter is set to `1s`, which is the default value.

`DERIVATIVE(field_key)`: returns the change rates of the field values for the specified field key.

`DERIVATIVE(/regular_expression/)`: returns the change rates of the field values for each field key that matches the specified regular expression.

`DERIVATIVE(*)`: returns the change rates of the field values for each field key in the specified measurement.

The `DERIVATIVE()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `DERIVATIVE()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the `NOAA_water_database` data set is used in the examples that are provided in this section:

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

name: h2o_feet
time                   water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the derivatives between the field values of the specified field key

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

name: h2o_feet
time                   derivative
--------------
2015-08-18T00:06:00Z0.00014444444444444457
2015-08-18T00:12:00Z-0.00024444444444444465
2015-08-18T00:18:00Z0.0002722222222222218
2015-08-18T00:24:00Z-0.000236111111111111
2015-08-18T00:30:00Z2.777777777777842e-05``````

This query returns the change rates of the field values for the `water_level` field key in the `h2o_feet` measurement. The change rates indicate how much the field values have changed every second.

The first result `0.00014444444444444457` is the one-second rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 1-second rate of change.

``````(2.116-2.064)/(360s/1s)
------------------------
||
|          the difference between the field values' timestamps / the default unit
second field value - first field value``````

Example 2: Calculate the derivatives between the field values of the specified field key and specify the unit parameter

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

name: h2o_feet
time                    derivative
--------------
2015-08-18T00:06:00Z0.052000000000000046
2015-08-18T00:12:00Z-0.08800000000000008
2015-08-18T00:18:00Z0.09799999999999986
2015-08-18T00:24:00Z-0.08499999999999996
2015-08-18T00:30:00Z0.010000000000000231``````

This query returns the change rates of the field values for the `water_level` field key in the `h2o_feet` measurement. The change rates indicate how much the field values have changed every 6 minutes.

The first result `0.052000000000000046` is the 6-minute rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 6-minute rate of change.

``````(2.116-2.064)/(6m/6m)
------------------------
||
|          the difference between the field values' timestamps / the specified unit
second field value - first field value``````

Example 3: Calculate the derivatives between the field values of each field key in the specified measurement and specify the unit parameter

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

name: h2o_feet
time                   derivative_water_level
--------------------------
2015-08-18T00:06:00Z0.026000000000000023
2015-08-18T00:12:00Z-0.04400000000000004
2015-08-18T00:18:00Z0.04899999999999993
2015-08-18T00:24:00Z-0.04249999999999998
2015-08-18T00:30:00Z0.0050000000000001155``````

This query returns the change rates of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The change rates indicate how much the field values have changed every 3 minutes. The `h2o_feet` measurement contains only the numeric `water_level` field.

The first result `0.026000000000000023` is the 3-minute rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 3-minute rate of change.

``````(2.116-2.064)/(6m/3m)
------------------------
||
|          the difference between the field values' timestamps / the specified unit
second field value - first field value``````

Example 4: Calculate the derivatives between the field values of each field key that matches the specified regular expression and specify the unit parameter

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

name: h2o_feet
time                   derivative_water_level
--------------------------
2015-08-18T00:06:00Z0.01733333333333335
2015-08-18T00:12:00Z-0.02933333333333336
2015-08-18T00:18:00Z0.03266666666666662
2015-08-18T00:24:00Z-0.02833333333333332
2015-08-18T00:30:00Z0.0033333333333334103``````

This query returns the change rates of the field values for each field key that contains `water` and stores numeric values in the `h2o_feet` measurement. The change rates indicate how much the field values have changed every 2 minutes. The `h2o_feet` measurement contains only the numeric `water_level` field.

The first result `0.01733333333333335` is the 2-minute rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 2-minute rate of change.

``````(2.116-2.064)/(6m/2m)
------------------------
||
|          the difference between the field values' timestamps / the specified unit
second field value - first field value``````

Example 5: Calculate the derivative between the field values of the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                   derivative
--------------
2015-08-18T00:12:00Z-0.0002722222222222218``````

This query returns the change rate of the field values for the `water_level` field key in the `h2o_feet` measurement. The change rate indicates how much the field values have changed within 1 second. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 1. The point offset is set to 2. This indicates that the first two points are not returned.

The only result `-0.0002722222222222218` is the 1-second rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 1-second rate of change.

``````(2.126-2.028)/(360s/1s)
------------------------
||
|          the difference between the field values' timestamps / the default unit
second field value - first field value``````

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

Description

The DERIVATIVE() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries are used to calculate the derivatives of these results.

The value of the `unit` parameter is an integer that is suffixed by a unit of time. This parameter is optional. You do not need to specify the `unit` parameter. By default, the `unit` parameter is set to the time interval that is specified by the `GROUP BY time()` clause. Note: The default value of the `unit` parameter in the advanced syntax is different from that of the `unit` parameter in the basic syntax.

DERIVATIVE() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Examples

Example 1: Calculate the derivatives between average field values

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

name: h2o_feet
time                   derivative
--------------
2015-08-18T00:12:00Z-0.0129999999999999
2015-08-18T00:24:00Z-0.030999999999999694``````

This query returns the change rates of the average field values that are calculated based on a 12-minute interval for the `water_level` field key. The change rates indicate how much the field values have changed every 12 minutes.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `DERIVATIVE()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the change rates of these average field values. The change rates indicate how much the field values have changed every 12 minutes. The first result `-0.0129999999999999` is the 12-miniute rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 12-minute rate of change

``````(2.077-2.09)/(12m/12m)
-----------------------
||
|          the difference between the field values' timestamps / the default unit
second field value - first field value``````

Example 2: Calculate the derivatives between average field values and specify the unit parameter

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

name: h2o_feet
time                   derivative
--------------
2015-08-18T00:12:00Z-0.00649999999999995
2015-08-18T00:24:00Z-0.015499999999999847``````

This query returns the change rates of the average field values that are calculated based on a 12-minute interval for the `water_level` field key. The change rates indicate how the field values have changed every 6 minutes.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `DERIVATIVE()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the change rates of these average field values. The change rates indicate how much the field values have changed every 6 minutes. The first result `-0.00649999999999995` is the 6-minute rate of change between the first two field values in the raw data. TSDB for InfluxDB® calculates the difference between the two field values. Then, it normalizes the result to the 6-minute rate of change.

``````(2.077-2.09)/(12m/6m)
-----------------------
||
|          the difference between the field values' timestamps / the specified unit
second field value - first field value``````

## DIFFERENCE()

Returns the difference between field values.

Basic syntax

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

Description

`DIFFERENCE(field_key)`: returns the difference between the field values of the specified field key.

`DIFFERENCE(/regular_expression/)`: returns the difference between the field values of each field key that matches the specified regular expression.

`DIFFERENCE(*)`: returns the difference between the field values of each field key in the specified measurement.

The `DIFFERENCE()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag, but does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `DIFFERENCE()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the `NOAA_water_database` dataset is used in the examples that are provided in this section:

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

name: h2o_feet
time                   water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the difference between the field values of the specified field key

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

name: h2o_feet
time                   difference
--------------
2015-08-18T00:06:00Z0.052000000000000046
2015-08-18T00:12:00Z-0.08800000000000008
2015-08-18T00:18:00Z0.09799999999999986
2015-08-18T00:24:00Z-0.08499999999999996
2015-08-18T00:30:00Z0.010000000000000231``````

This query returns the difference between the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the difference between the field values of each field key in the specified measurement

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

name: h2o_feet
time                   difference_water_level
--------------------------
2015-08-18T00:06:00Z0.052000000000000046
2015-08-18T00:12:00Z-0.08800000000000008
2015-08-18T00:18:00Z0.09799999999999986
2015-08-18T00:24:00Z-0.08499999999999996
2015-08-18T00:30:00Z0.010000000000000231``````

This query returns the difference between the field values of each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet `measurement contains only the numeric `water_level` field.

Example 3: Calculate the difference between the field values of each field key that matches the specified regular expression

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

name: h2o_feet
time                   difference_water_level
--------------------------
2015-08-18T00:06:00Z0.052000000000000046
2015-08-18T00:12:00Z-0.08800000000000008
2015-08-18T00:18:00Z0.09799999999999986
2015-08-18T00:24:00Z-0.08499999999999996
2015-08-18T00:30:00Z0.010000000000000231``````

This query returns the difference between the field values of each field key that contains `water` and stores numeric values in the `h2o_feet` measurement.

Example 4: Calculate the difference between the field values of the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                   difference
--------------
2015-08-18T00:12:00Z-0.09799999999999986
2015-08-18T00:06:00Z0.08800000000000008``````

This query returns the difference between the field values of the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 2. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The DIFFERENCE() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries calculate the difference between the results.

DIFFERENCE() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the difference between the maximum values

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

name: h2o_feet
time                   difference
--------------
2015-08-18T00:12:00Z0.009999999999999787
2015-08-18T00:24:00Z-0.07499999999999973``````

This query returns the difference between the maximum values that are calculated based on a 12-minute interval for the `water_level` field key.

To obtain the results, TSDB for InfluxDB® first calculates the largest field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MAX()` function in combination with the `GROUP BY time()` clause, and does not use the `DIFFERENCE()` function.

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

name: h2o_feet
time                   max
-------
2015-08-18T00:00:00Z2.116
2015-08-18T00:12:00Z2.126
2015-08-18T00:24:00Z2.051``````

Then, TSDB for InfluxDB® calculates the difference between these maximum values. In the query results, the first point `0.009999999999999787` is the difference between `2.126` and `2.116`, and the second point `-0.07499999999999973` is the difference between `2.051` and `2.126`.

## ELAPSED()

Returns the difference between the timestamps of the field values for the specified field keys.

Syntax

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

Description

TSDB for InfluxDB® calculates the difference between timestamps. The value of the `unit` parameter is an integer that is suffixed by a unit of time. The difference between timestamps is measured in the time unit. This parameter is optional. If you do not specify the `unit` parameter in queries, the difference between timestamps is measured in nanoseconds.

`ELAPSED(field_key)`: returns the difference between the timestamps of the field values for the specified field key.

`ELAPSED(/regular_expression/)`: returns the difference between the timestamps of the field values for each field key that matches the specified regular expression.

`ELAPSED(*)`: returns the difference between the timestamps of the field values for each field key in the specified measurement.

The `ELAPSED()` function supports field values of all data types.

Examples

The following data in the `NOAA_water_database` dataset is used in the examples that are provided in this section:

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

name: h2o_feet
time                   water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028``````

Example 1: Calculate the time interval between the field values of the specified field key

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

name: h2o_feet
time                   elapsed
-----------
2015-08-18T00:06:00Z360000000000
2015-08-18T00:12:00Z360000000000``````

This query returns the difference between the timestamps of the field values for the `water_level` field key in the `h2o_feet` measurement. The difference between the timestamps is measured in nanoseconds.

Example 2: Calculate the time interval between the field values of the specified field key and specify the unit parameter

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

name: h2o_feet
time                   elapsed
-----------
2015-08-18T00:06:00Z6
2015-08-18T00:12:00Z6``````

This query returns the difference between the timestamps of the field values of the `water_level` field key in the `h2o_feet` measurement. The difference between the timestamps is measured in minutes.

Example 3: Calculate the time interval between the field values of each field key in the specified measurement and specify the unit parameter

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

name: h2o_feet
time                   elapsed_level description   elapsed_water_level
------------------------------------------------
2015-08-18T00:06:00Z66
2015-08-18T00:12:00Z66``````

This query returns the difference between the timestamps of the field values of each field key in the `h2o_feet` measurement. The difference between the timestamps is measured in minutes. The `h2o_feet` measurement contains the `level description` and `water_level` field keys.

Example 4: Calculate the time interval between the field values of each field key that matches the specified regular expression, and specify the unit parameter.

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

name: h2o_feet
time                   elapsed_level description   elapsed_water_level
------------------------------------------------
2015-08-18T00:06:00Z360360
2015-08-18T00:12:00Z360360``````

This query returns the difference between the timestamps of the field values for each field key that contains `level` in the `h2o_feet` measurement. The difference between the timestamps is measured in seconds.

Example 5: Calculate the time interval between the field values of the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                   elapsed
-----------
2015-08-18T00:00:00Z-360000``````

This query returns the difference between the timestamps of the field values for the `water_level` field key in the `h2o_feet` measurement. The difference between the timestamps is measured in milliseconds. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:12:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 1. The point offset is set to 1, which indicates that the first point is not returned.

Note

The query returns a negative value. This occurs when the `ORDER BY time DESC `clause sorts the timestamps in descending order. Therefore, the `ELAPSED()` function calculates the difference between the timestamps that are sorted in reverse order.

FAQ

What are the impacts if the value of the unit parameter in the ELAPSED() function is greater than the difference between the timestamps?

If the value of the `unit `parameter is greater than the difference between the timestamps, TSDB for InfluxDB® returns `0`.

Example: One point is generated for the `h2o_feet` measurement every 6 minutes. If you set the `unit` parameter in queries to 1 hour, TSDB for InfluxDB® returns`0`.

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

name: h2o_feet
time                   elapsed
-----------
2015-08-18T00:06:00Z0
2015-08-18T00:12:00Z0``````

What are the impacts if ELAPSED() functions are used in combination with GROUP BY time() clauses in queries?

`ELAPSED()` functions can be used in combination with `GROUP BY time()` clauses in queries. However, the query results are used for reference only. If an `ELAPSED()` query contains a nested InfluxQL function and a `GROUP BY time()` clause, the query returns only the time interval that is specified by the `GROUP BY time()` clause.

The `GROUP BY time()` clause determines the timestamps that are returned in the query results. Each timestamp indicates the start time of each time interval. This rule also applies to nested selector functions, such as `FIRST()` and `MAX()`. If the GROUP BY time() clause is not used, these functions return the original timestamps of raw data. The `GROUP BY time()` clause overwrites the original timestamps. Therefore, the `ELAPSED()` function always returns the same value as the time interval that is specified by the `GROUP BY time()` clause.

Example: The first query in the following code uses the `ELAPSED()` function and the `GROUP BY time()` clause. This query returns the difference between the timestamps of the smallest field values for the `water_level` field key. The difference is measured in minutes. In the query results, the difference between the timestamps for each of the two time intervals is 12 minutes.

To obtain the results, TSDB for InfluxDB® first calculates the minimum field values of the `water_level` field key based on a 12-minute interval. The second query in the following code returns the minimum value for each time interval. This process returns the same result as a query if the query uses the `MIN()` function in combination with the `GROUP BY time()` clause, and does not use the `ELAPSED()` function. Note: The difference between the timestamps returned in the second query is 12 minutes. In raw data, the first result `2.057` occurs at `2015-08-18T00:42:00Z`. However, this original timestamp is overwritten by the `GROUP BY time()` clause. The returned timestamps are determined by the time interval that is specified by the `GROUP BY time()` clause rather than the original timestamps in raw data. Therefore, the `ELAPSED()` function always returns the difference between the timestamps as the same value as the time interval that is specified by the GROUP BY time() clause.

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

name: h2o_feet
time                   elapsed
-----------
2015-08-18T00:36:00Z12
2015-08-18T00:48:00Z12

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

name: h2o_feet
time                   min
-------
2015-08-18T00:36:00Z2.057<---Actually occurs at 2015-08-18T00:42:00Z
2015-08-18T00:48:00Z1.991``````

## EXP()

Returns the exponential of a field value.

Basic syntax

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

Description

`EXP(field_key)`: returns the exponential of the field values for the specified field key.

`EXP(*)`: returns the exponentials of the field values for each field key in the specified measurement.

The `EXP()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `EXP()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following sample data is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the exponential of the field values for the specified field key

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

name: h2o_feet
time                  exp
-------
2015-08-18T00:00:00Z7.877416541092307
2015-08-18T00:06:00Z8.297879498060171
2015-08-18T00:12:00Z7.598873404088091
2015-08-18T00:18:00Z8.381274573459967
2015-08-18T00:24:00Z7.6983036546645645
2015-08-18T00:30:00Z7.775672892658607``````

This query returns the exponential of the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the exponential of the field values for each field key in the specified measurement

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

name: h2o_feet
time                  exp_water_level
-------------------
2015-08-18T00:00:00Z7.877416541092307
2015-08-18T00:06:00Z8.297879498060171
2015-08-18T00:12:00Z7.598873404088091
2015-08-18T00:18:00Z8.381274573459967
2015-08-18T00:24:00Z7.6983036546645645
2015-08-18T00:30:00Z7.775672892658607``````

This query returns the exponential of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Calculate the exponential of the field values for the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                  exp
-------
2015-08-18T00:18:00Z8.381274573459967
2015-08-18T00:12:00Z7.598873404088091
2015-08-18T00:06:00Z8.297879498060171
2015-08-18T00:00:00Z7.877416541092307``````

This query returns the exponential of the field values for the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The EXP() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries calculate the exponentials of these results.

EXP() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the exponential of the average field values for the specified field key

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

name: h2o_feet
time                  exp
-------
2015-08-18T00:00:00Z8.084915164305059
2015-08-18T00:12:00Z7.980491491670466
2015-08-18T00:24:00Z7.736891562315577``````

This query raises e to the power of the average field values for the `water_level` field key. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `EXP()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the exponential of each average field value.

## FLOOR()

Rounds each specified field value down to the nearest integer.

Basic syntax

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

Description

`FLOOR(field_key)`: rounds each field value of the specified field key down to the nearest integer.

`FLOOR(*)`: rounds each field value of all the field keys in the specified measurement down to the nearest integer.

The `FLOOR()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `FLOOR()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the `NOAA_water_database` dataset is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Round each field value of the specified field key down to the nearest integer

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

name: h2o_feet
time                  floor
---------
2015-08-18T00:00:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:18:00Z2
2015-08-18T00:24:00Z2
2015-08-18T00:30:00Z2``````

The FLOOR() function in this query rounds each field value of the `water_level` field key in the `h2o_feet` measurement down to the nearest integer.

Example 2: Round each field value of all the field keys in the specified measurement down to the nearest integer

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

name: h2o_feet
time                  floor_water_level
---------------------
2015-08-18T00:00:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:18:00Z2
2015-08-18T00:24:00Z2
2015-08-18T00:30:00Z2``````

The FLOOR() function in this query rounds each field value of the specified field keys down to the nearest integer. The specified field keys store numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Round each field value of the specified field key down to the nearest integer by using a query that includes multiple clauses

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

name: h2o_feet
time                  floor
---------
2015-08-18T00:18:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:00:00Z2``````

The FLOOR() function in this query rounds each field value of the `water_level` field key down to the nearest integer. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The FLOOR() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries apply the `FLOOR()` function to these results.

FLOOR() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Round each average field value down to the nearest integer

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

name: h2o_feet
time                  floor
---------
2015-08-18T00:00:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:24:00Z2``````

The FLOOR() function in this query rounds each average field value of the `water_level` field key down to the nearest integer. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `FLOOR()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® rounds each average field value down to the nearest integer.

## LN()

Returns the natural logarithms of the field values for the specified field keys.

Basic syntax

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

Description

`LN(field_key)`: returns the natural logarithms of the field values for the specified field key.

`LN(*)`: returns the natural logarithms of the field values for each field key in the specified measurement.

The `LN()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `LN()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples of basic syntax

The following sample data is used in the examples provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the natural logarithms of the field values for the specified field key

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

name: h2o_feet
time                  ln
------
2015-08-18T00:00:00Z0.7246458476193163
2015-08-18T00:06:00Z0.749527513996053
2015-08-18T00:12:00Z0.7070500857289368
2015-08-18T00:18:00Z0.7542422799197561
2015-08-18T00:24:00Z0.7134398838277077
2015-08-18T00:30:00Z0.7183274790902436``````

This query returns the natural logarithms of the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the natural logarithms of the field values for each field key in the specified measurement

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

name: h2o_feet
time                  ln_water_level
------------------
2015-08-18T00:00:00Z0.7246458476193163
2015-08-18T00:06:00Z0.749527513996053
2015-08-18T00:12:00Z0.7070500857289368
2015-08-18T00:18:00Z0.7542422799197561
2015-08-18T00:24:00Z0.7134398838277077
2015-08-18T00:30:00Z0.7183274790902436``````

This query returns the natural logarithms of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Calculate the natural logarithms of the field values for the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                  exp
-------
2015-08-18T00:18:00Z8.381274573459967
2015-08-18T00:12:00Z7.598873404088091
2015-08-18T00:06:00Z8.297879498060171
2015-08-18T00:00:00Z7.877416541092307``````

This query returns the natural logarithms of the field values for the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The LN() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries calculate the natural logarithms of these results.

LN() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the natural logarithms of the average field values for the specified field key

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

name: h2o_feet
time                  ln
------
2015-08-18T00:00:00Z0.7371640659767196
2015-08-18T00:12:00Z0.7309245448939752
2015-08-18T00:24:00Z0.7158866675294349``````

This query returns the natural logarithms of the average field values for the `water_level` field key. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `LN()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the natural logarithms of each average field value.

## LOG()

Returns the base-`b` logarithms of the field values for the specified field keys.

Basic syntax

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

Description

`LOG(field_key, b)`: returns the base-`b` logarithms of the field values for the specified field key.

`LOG(*, b)`: returns the base-`b` logarithms of the field values for each field key in the specified measurement.

The `LOG()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time For more information about how to use `LOG()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following sample data is used in the examples provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the base-4 logarithms of the field values for the specified field key

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

name: h2o_feet
time                  log
-------
2015-08-18T00:00:00Z0.5227214853805835
2015-08-18T00:06:00Z0.5406698137259695
2015-08-18T00:12:00Z0.5100288261706268
2015-08-18T00:18:00Z0.5440707984345088
2015-08-18T00:24:00Z0.5146380911853161
2015-08-18T00:30:00Z0.5181637459088826``````

This query returns the base-4 logarithms of the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the base-4 logarithms of the field values for each field key in the specified measurement

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

name: h2o_feet
time                  log_water_level
-------------------
2015-08-18T00:00:00Z0.5227214853805835
2015-08-18T00:06:00Z0.5406698137259695
2015-08-18T00:12:00Z0.5100288261706268
2015-08-18T00:18:00Z0.5440707984345088
2015-08-18T00:24:00Z0.5146380911853161
2015-08-18T00:30:00Z0.5181637459088826``````

This query returns the base-4 logarithms of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Calculate the base-4 logarithms of the field values for the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                  log
-------
2015-08-18T00:18:00Z0.5440707984345088
2015-08-18T00:12:00Z0.5100288261706268
2015-08-18T00:06:00Z0.5406698137259695
2015-08-18T00:00:00Z0.5227214853805835``````

This query returns the base-4 logarithms of the field values for the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The LOG() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries calculate the logarithms of these results.

LOG() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the base-4 logarithms of the average field values for the specified field key

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

name: h2o_feet
time                  log
-------
2015-08-18T00:00:00Z0.531751471153079
2015-08-18T00:12:00Z0.5272506080912802
2015-08-18T00:24:00Z0.5164030725416209``````

This query returns the base-4 logarithms of the average field values for the `water_level` field key. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `LOG()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the base-4 logarithm of each average field value.

## LOG2()

Returns the base-2 logarithms of the field values for the specified field keys.

Basic syntax

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

Description

`LOG2(field_key)`: returns the base-2 logarithms of the field values for the specified field key.

`LOG2(*)`: returns the base-2 logarithms of the field values for each field key in the specified measurement.

The `LOG2()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `LOG2()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following sample data is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the base-2 logarithms of the field values for the specified field key

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

name: h2o_feet
time                  log2
--------
2015-08-18T00:00:00Z1.045442970761167
2015-08-18T00:06:00Z1.081339627451939
2015-08-18T00:12:00Z1.0200576523412537
2015-08-18T00:18:00Z1.0881415968690176
2015-08-18T00:24:00Z1.0292761823706322
2015-08-18T00:30:00Z1.0363274918177652``````

This query returns the base-2 logarithms of the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the base-2 logarithms of the field values for each field key in the specified measurement

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

name: h2o_feet
time                  log2_water_level
--------------------
2015-08-18T00:00:00Z1.045442970761167
2015-08-18T00:06:00Z1.081339627451939
2015-08-18T00:12:00Z1.0200576523412537
2015-08-18T00:18:00Z1.0881415968690176
2015-08-18T00:24:00Z1.0292761823706322
2015-08-18T00:30:00Z1.0363274918177652``````

This query returns the base-2 logarithms of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Calculate the base-2 logarithms of the field values for the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                  log2
--------
2015-08-18T00:18:00Z1.0881415968690176
2015-08-18T00:12:00Z1.0200576523412537
2015-08-18T00:06:00Z1.081339627451939
2015-08-18T00:00:00Z1.045442970761167``````

This query returns the base-2 logarithms of the field values for the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The LOG2() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries calculate the base-2 logarithms of these results.

LOG2() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the base-2 logarithms of the average field values for the specified field key

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

name: h2o_feet
time                  log2
--------
2015-08-18T00:00:00Z1.063502942306158
2015-08-18T00:12:00Z1.0545012161825604
2015-08-18T00:24:00Z1.0328061450832418``````

This query returns the base-2 logarithms of the average field values that are calculated based on a 12-minute interval for the `water_level` field key.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `LOG2()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the base-2 logarithms of each average field value.

## LOG10()

Returns the base-10 logarithms of the field values for the specified field keys.

Basic syntax

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

Description

`LOG10(field_key)`: returns the base-10 logarithms of the field values for the specified field key.

`LOG10(*)`: returns the base-10 logarithms of the field values for each field key in the specified measurement.

The `LOG10()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time For more information about how to use `LOG10()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following sample data is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the base-10 logarithms of the field values for the specified field key

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

name: h2o_feet
time                  log10
---------
2015-08-18T00:00:00Z0.3147096929551737
2015-08-18T00:06:00Z0.32551566336314813
2015-08-18T00:12:00Z0.3070679506612984
2015-08-18T00:18:00Z0.32756326018727794
2015-08-18T00:24:00Z0.3098430047160705
2015-08-18T00:30:00Z0.3119656603683663``````

This query returns the base-10 logarithms of the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the base-10 logarithms of the field values for each field key in the specified measurement

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

name: h2o_feet
time                  log10_water_level
---------------------
2015-08-18T00:00:00Z0.3147096929551737
2015-08-18T00:06:00Z0.32551566336314813
2015-08-18T00:12:00Z0.3070679506612984
2015-08-18T00:18:00Z0.32756326018727794
2015-08-18T00:24:00Z0.3098430047160705
2015-08-18T00:30:00Z0.3119656603683663``````

This query returns the base-10 logarithms of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Calculate the base-10 logarithms of the field values for the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                  log10
---------
2015-08-18T00:18:00Z0.32756326018727794
2015-08-18T00:12:00Z0.3070679506612984
2015-08-18T00:06:00Z0.32551566336314813
2015-08-18T00:00:00Z0.3147096929551737``````

This query returns the base-10 logarithms of the field values for the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The LOG10() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries calculate the base-10 logarithms of these results.

LOG10() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the base-10 logarithms of the average field values for the specified field key

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

name: h2o_feet
time                  log10
---------
2015-08-18T00:00:00Z0.32014628611105395
2015-08-18T00:12:00Z0.3174364965350991
2015-08-18T00:24:00Z0.3109056293761414``````

This query returns the base-10 logarithms of the average field values for the `water_level` field key. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `LOG10()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the base-10 logarithms of each average field value.

## MOVING_AVERAGE()

Returns the moving average for each specified window of field values.

Basic syntax

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

Description

`MOVING_AVERAGE()`: calculates the moving average of each window that contains `N` consecutive field values. The `N` parameter is required, and the value of this parameter must be an integer.

`MOVING_AVERAGE(field_key,N)`: returns the moving average of N field values for the specified field key.

`MOVING_AVERAGE(/regular_expression/,N)`: returns the moving average of N field values for each field key that matches the specified regular expression.

`MOVING_AVERAGE(*,N)`: returns the moving average of N field values for each field key in the specified measurement.

The `MOVING_AVERAGE()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `MOVING_AVERAGE()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the `NOAA_water_database` dataset is used in the examples that are provided in this section:

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

name: h2o_feet
time                   water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the moving average for each window of the field values for the specified field key

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

name: h2o_feet
time                   moving_average
------------------
2015-08-18T00:06:00Z2.09
2015-08-18T00:12:00Z2.072
2015-08-18T00:18:00Z2.077
2015-08-18T00:24:00Z2.0835
2015-08-18T00:30:00Z2.0460000000000003``````

This query returns the moving average for each window of two field values for the `water_level` field key in the `h2o_feet` measurement. The first result `2.09` is the average of the first two field values in raw data, and is calculated based on the formula: (2.064 + 2.116)/2. The second result `2.072` is the average of the second and third field values in raw data, and is calculated based on the formula: (2.116 + 2.028)/2.

Example 2: Calculate the moving average for each window of the field values for each field key in the specified measurement

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

name: h2o_feet
time                   moving_average_water_level
------------------------------
2015-08-18T00:12:00Z2.0693333333333332
2015-08-18T00:18:00Z2.09
2015-08-18T00:24:00Z2.065
2015-08-18T00:30:00Z2.0726666666666667``````

This query returns the moving average for each window of three field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Calculate the moving average for each window of the field values for each field key that matches the specified regular expression

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

name: h2o_feet
time                    moving_average_water_level
------------------------------
2015-08-18T00:18:00Z2.0835
2015-08-18T00:24:00Z2.07775
2015-08-18T00:30:00Z2.0615``````

This query returns the moving average for each window of four field values for each specified field key. The specified field key contains `level` and stores numeric values in the `h2o_feet` measurement.

Example 4: Calculate the moving average for each window of the field values for the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                   moving_average
------------------
2015-08-18T00:06:00Z2.072
2015-08-18T00:00:00Z2.09``````

This query returns the moving average for each window of two field values for the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 2. The point offset is set to 3, which indicates that the first three points are not returned.

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

Description

The MOVING_AVERAGE() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries calculate the moving averages of these results.

MOVING_AVERAGE() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the moving average of the maximum values

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

name: h2o_feet
time                   moving_average
------------------
2015-08-18T00:12:00Z2.121
2015-08-18T00:24:00Z2.0885``````

This query returns the moving average for each window of the two largest values for the `water_level` field key. The largest values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the maximum field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MAX()` function in combination with the `GROUP BY time()` clause, and does not use the `MOVING_AVERAGE()` function.

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

name: h2o_feet
time                   max
-------
2015-08-18T00:00:00Z2.116
2015-08-18T00:12:00Z2.126
2015-08-18T00:24:00Z2.051``````

Then, TSDB for InfluxDB® calculates the moving average for each window of two maximum values. In the query results, the first point `2.121` is the average of the first two maximum values, and is calculated based on the formula: `(2.116 + 2.126) / 2`.

## NON_NEGATIVE_DERIVATIVE()

Returns the non-negative change rate between field values. A non-negative change rate is a positive number or 0.

Basic syntax

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

Description

TSDB for InfluxDB® calculates the difference between each two field values and converts the results into change rates. The change rates indicate how much the field values have changed based on the time granularity that is specified by the `unit` parameter. The value of the `unit` parameter is an integer that is suffixed by a unit of time. This parameter is optional. If you do not specify the `unit` parameter in your query, the `unit` parameter is set to the default value `1s`. `NON_NEGATIVE_DERIVATIVE()`: returns only the change rates that are positive numbers or 0.

`NON_NEGATIVE_DERIVATIVE(field_key)`: returns the non-negative change rates for the field values of the specified field key.

`NON_NEGATIVE_DERIVATIVE(/regular_expression/)`: returns the non-negative change rates for the field values of each field key that matches the specified regular expression.

`NON_NEGATIVE_DERIVATIVE(*)`: returns the non-negative change rates for the field values of each field key in the specified measurement.

The `NON_NEGATIVE_DERIVATIVE()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `NON_NEGATIVE_DERIVATIVE()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

For more information about the syntax examples, see the `DERIVATIVE()` section in this topic. The `NON_NEGATIVE_DERIVATIVE()` function runs in the same way as the `DERIVATIVE()` function. The difference is that the `NON_NEGATIVE_DERIVATIVE()` function returns only the change rates that are positive numbers or 0.

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

Description

The NON_NEGATIVE_DERIVATIVE() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clause. Then, the queries calculate the non-negative derivatives of these results.

The value of the `unit` parameter is an integer that is suffixed by a unit of time. This parameter is optional. You do not need to specify the `unit` parameter. By default, the `unit` parameter is set to the time interval that is specified by the `GROUP BY time()` clause. Note: The default value of the `unit` parameter in the advanced syntax is different from that of the `unit` parameter in the basic syntax. `NON_NEGATIVE_DERIVATIVE()`: returns only the change rates that are positive numbers or 0.

NON_NEGATIVE_DERIVATIVE() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Examples

For more information about the syntax examples, see the `DERIVATIVE()` section in this topic. The `NON_NEGATIVE_DERIVATIVE()` function runs in the same way as the `DERIVATIVE()` function. The difference is that the `NON_NEGATIVE_DERIVATIVE()` function returns only the change rates that are positive numbers or 0.

## NON_NEGATIVE_DIFFERENCE()

Returns the non-negative difference between field values. A non-negative difference is a positive number or 0.

Basic syntax

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

Description

`NON_NEGATIVE_DIFFERENCE(field_key)`: returns the non-negative difference between the field values of the specified field key.

`NON_NEGATIVE_DIFFERENCE(/regular_expression/)`: returns the non-negative difference between the field values of each field key that matches the specified regular expression.

`NON_NEGATIVE_DIFFERENCE(*)`: returns the non-negative difference between the field values of each field key in the specified measurement.

The `NON_NEGATIVE_DIFFERENCE()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `NON_NEGATIVE_DIFFERENCE()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

For more information about the syntax examples, see the `DIFFERENCE()` section in this topic. The `NON_NEGATIVE_DIFFERENCE()` function runs in the same way as the `DIFFERENCE()` function. The difference is that the `NON_NEGATIVE_DIFFERENCE()` function returns only the differences that are positive numbers or 0.

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

Description

The NON_NEGATIVE_DIFFERENCE() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries calculate the non-negative differences between these results.

NON_NEGATIVE_DIFFERENCE() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Examples

For more information about the syntax examples, see the `DIFFERENCE()` section in this topic. The `NON_NEGATIVE_DIFFERENCE()` function runs in the same way as the `DIFFERENCE()` function. The difference is that the `NON_NEGATIVE_DIFFERENCE()` function returns only the differences that are positive numbers or 0.

## POW()

Raises the specified field values to the power of `x`.

Basic syntax

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

Description

`POW(field_key, x)`: raises each field value of the specified field key to the power of `x`.

`POW(*, x)`: raises each field value of all the field keys in the specified measurement to the power of `x`.

The `POW()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `POW()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following sample data is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Raise each field value of the specified field key to the power of 4

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

name: h2o_feet
time                  pow
-------
2015-08-18T00:00:00Z18.148417929216
2015-08-18T00:06:00Z20.047612231936
2015-08-18T00:12:00Z16.914992230656004
2015-08-18T00:18:00Z20.429279055375993
2015-08-18T00:24:00Z17.352898193760993
2015-08-18T00:30:00Z17.69549197320101``````

This query returns the results of raising each field value of the `water_level` field key in the `h2o_feet` measurement to the power of 4.

Example 2: Raise each field value of all the field keys in the specified measurement to the power of 4

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

name: h2o_feet
time                  pow_water_level
-------------------
2015-08-18T00:00:00Z18.148417929216
2015-08-18T00:06:00Z20.047612231936
2015-08-18T00:12:00Z16.914992230656004
2015-08-18T00:18:00Z20.429279055375993
2015-08-18T00:24:00Z17.352898193760993
2015-08-18T00:30:00Z17.69549197320101``````

This query returns the results of raising the field values of each specified field key to the power of 4. The specified field key stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Raise each field value of the specified field key to the power of 4 by using a query that includes multiple clauses

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

name: h2o_feet
time                  pow
-------
2015-08-18T00:18:00Z20.429279055375993
2015-08-18T00:12:00Z16.914992230656004
2015-08-18T00:06:00Z20.047612231936
2015-08-18T00:00:00Z18.148417929216``````

This query returns the results of raising each field value of the `water_level` field key in the `h2o_feet` measurement to the power of 4. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The POW() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries raise these results to the power of `x`.

POW() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Raise the average field values of the specified field key to the power of 4

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

name: h2o_feet
time                  pow
-------
2015-08-18T00:00:00Z19.08029760999999
2015-08-18T00:12:00Z18.609983417041
2015-08-18T00:24:00Z17.523567165456008``````

This query returns the results of raising each average field value to the power of 4 for the `water_level` field key. The average field values are calculated based a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `POW()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® raises each average field value to the power of 4.

## ROUND()

Rounds the specified field values to the nearest integers.

Basic syntax

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

Description

`ROUND(field_key)`: rounds each field value of the specified field key to the nearest integer.

`ROUND(*)`: rounds each field value of all the field keys in the specified measurement to the nearest integer.

The `ROUND()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `ROUND()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following sample data is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Round each field value of the specified field key to the nearest integer

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

name: h2o_feet
time                  round
---------
2015-08-18T00:00:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:18:00Z2
2015-08-18T00:24:00Z2
2015-08-18T00:30:00Z2``````

The ROUND() function in this query rounds each field value of the `water_level` field key in the `h2o_feet` measurement to the nearest integer.

Example 2: Round each field value of all the field keys in the specified measurement to the nearest integer

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

name: h2o_feet
time                  round_water_level
---------------------
2015-08-18T00:00:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:18:00Z2
2015-08-18T00:24:00Z2
2015-08-18T00:30:00Z2``````

The ROUND() function in this query rounds each field value of the specified field keys to the nearest integer. The specified field keys store numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Round each field value of the specified field key to the nearest integer by using a query that includes multiple clauses

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

name: h2o_feet
time                  round
---------
2015-08-18T00:18:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:06:00Z2
2015-08-18T00:00:00Z2``````

The ROUND() function in this query rounds each field value of the `water_level` field key in the `h2o_feet` measurement to the nearest integer. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The ROUND() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries apply the ROUND() function to these results.

ROUND() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Round each average field value to the nearest integer

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

name: h2o_feet
time                  round
---------
2015-08-18T00:00:00Z2
2015-08-18T00:12:00Z2
2015-08-18T00:24:00Z2``````

The ROUND() function in this query rounds each average field value of the `water_level` field key to the nearest integer. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `ROUND()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® rounds each average field value to the nearest integer.

## SIN()

Returns the sines of the field values for the specified field keys.

Basic syntax

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

Description

`SIN(field_key)`: returns the sines of the field values for the specified field key.

`SIN(*)`: returns the sines of the field values for each field key in the specified measurement.

The `SIN()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `SIN()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the `NOAA_water_database` dataset is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the sines of the field values for the specified field key

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

name: h2o_feet
time                  sin
-------
2015-08-18T00:00:00Z0.8808206017241819
2015-08-18T00:06:00Z0.8550216851706579
2015-08-18T00:12:00Z0.8972904165810275
2015-08-18T00:18:00Z0.8497930984115993
2015-08-18T00:24:00Z0.8914760289023131
2015-08-18T00:30:00Z0.8869008523376968``````

This query returns the sines of the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the sines of the field values for each field key in the specified measurement

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

name: h2o_feet
time                  sin_water_level
-------------------
2015-08-18T00:00:00Z0.8808206017241819
2015-08-18T00:06:00Z0.8550216851706579
2015-08-18T00:12:00Z0.8972904165810275
2015-08-18T00:18:00Z0.8497930984115993
2015-08-18T00:24:00Z0.8914760289023131
2015-08-18T00:30:00Z0.8869008523376968``````

This query returns the sines of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Calculate the sines of the field values for the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                  sin
-------
2015-08-18T00:18:00Z0.8497930984115993
2015-08-18T00:12:00Z0.8972904165810275
2015-08-18T00:06:00Z0.8550216851706579
2015-08-18T00:00:00Z0.8808206017241819``````

This query returns the sines of the field values for the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The SIN() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals that are specified by the `GROUP BY time()` clauses. Then, the queries calculate the sines of these results.

SIN() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the sines of the average field values for the specified field key

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

name: h2o_feet
time                  sin
-------
2015-08-18T00:00:00Z0.8682145834456126
2015-08-18T00:12:00Z0.8745914945253902
2015-08-18T00:24:00Z0.8891995555912935``````

This query returns the sines of the average field values for the `water_level` field key. The average field values are calculated based on a 12-minute interval.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `SIN()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the sines of the average field values.

## SQRT()

Returns the square roots of the field values for the specified field keys.

Basic syntax

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

Description

`SQRT(field_key)`: returns the square roots of the field values for the specified field key.

`SQRT(*)`: returns the square roots of the field values for each field key in the specified measurement.

The `SQRT()` function supports INT64 and FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, it does not support the GROUP BY clauses that are used to group data by time For more information about how to use `SQRT()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the NOAA_water_database dataset is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the square roots of the field values for the specified field key

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

name: h2o_feet
time                  sqrt
--------
2015-08-18T00:00:00Z1.4366627996854378
2015-08-18T00:06:00Z1.4546477236774544
2015-08-18T00:12:00Z1.4240786495134319
2015-08-18T00:18:00Z1.4580809305384939
2015-08-18T00:24:00Z1.4286357128393508
2015-08-18T00:30:00Z1.4321312788986909``````

This query returns the square roots of the field values for the `h2o_feet` field key in the `water_level` measurement.

Example 2: Calculate the square roots of the field values for each field key in the specified measurement

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

name: h2o_feet
time                  sqrt_water_level
--------------------
2015-08-18T00:00:00Z1.4366627996854378
2015-08-18T00:06:00Z1.4546477236774544
2015-08-18T00:12:00Z1.4240786495134319
2015-08-18T00:18:00Z1.4580809305384939
2015-08-18T00:24:00Z1.4286357128393508
2015-08-18T00:30:00Z1.4321312788986909``````

This query returns the square roots of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Calculate the square roots of the field values for the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                  sqrt
--------
2015-08-18T00:18:00Z1.4580809305384939
2015-08-18T00:12:00Z1.4240786495134319
2015-08-18T00:06:00Z1.4546477236774544
2015-08-18T00:00:00Z1.4366627996854378``````

This query returns the square roots of the field values for the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The SQRT() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries calculate the square roots of these results.

SQRT() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the square roots of the average field values for the specified field key

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

name: h2o_feet
time                  sqrt
--------
2015-08-18T00:00:00Z1.445683229480096
2015-08-18T00:12:00Z1.4411800720243115
2015-08-18T00:24:00Z1.430384563675098``````

This query returns the square roots of the average field values that are calculated based on a 12-minute interval for the `water_level` field key.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `SQRT()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the square roots of the average field values.

## TAN()

Returns the tangents of the field values for the specified field keys.

Basic syntax

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

Description

`TAN(field_key)`: returns the tangents of the field values for the specified field key.

`TAN(*)`: returns the tangents of the field values for each field key in the specified measurement.

The `TAN()` function supports INT64 or FLOAT64 field values.

The basic syntax supports the `GROUP BY` clauses that are used to group data by tag. However, the basic syntax does not support the GROUP BY clauses that are used to group data by time. For more information about how to use `TAN()` functions in combination with `GROUP BY time()` clauses, see the "Advanced syntax" section.

Examples

The following data in the `NOAA_water_database` dataset is used in the examples that are provided in this section:

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

name: h2o_feet
time                  water_level
---------------
2015-08-18T00:00:00Z2.064
2015-08-18T00:06:00Z2.116
2015-08-18T00:12:00Z2.028
2015-08-18T00:18:00Z2.126
2015-08-18T00:24:00Z2.041
2015-08-18T00:30:00Z2.051``````

Example 1: Calculate the tangents of the field values for the specified field key

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

name: h2o_feet
time                  tan
-------
2015-08-18T00:00:00Z-1.8604293534384375
2015-08-18T00:06:00Z-1.6487359603347427
2015-08-18T00:12:00Z-2.0326408012302273
2015-08-18T00:18:00Z-1.6121545688343464
2015-08-18T00:24:00Z-1.9676434782626282
2015-08-18T00:30:00Z-1.9198657720074992``````

This query returns the tangents of the field values for the `water_level` field key in the `h2o_feet` measurement.

Example 2: Calculate the tangents of the field values for each field key in the specified measurement

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

name: h2o_feet
time                  tan_water_level
-------------------
2015-08-18T00:00:00Z-1.8604293534384375
2015-08-18T00:06:00Z-1.6487359603347427
2015-08-18T00:12:00Z-2.0326408012302273
2015-08-18T00:18:00Z-1.6121545688343464
2015-08-18T00:24:00Z-1.9676434782626282
2015-08-18T00:30:00Z-1.9198657720074992``````

This query returns the tangents of the field values for each field key that stores numeric values in the `h2o_feet` measurement. The `h2o_feet` measurement contains only the numeric `water_level` field.

Example 3: Calculate the tangents of the field values for the specified field key by using a query that includes multiple clauses

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

name: h2o_feet
time                  tan
-------
2015-08-18T00:18:00Z-1.6121545688343464
2015-08-18T00:12:00Z-2.0326408012302273
2015-08-18T00:06:00Z-1.6487359603347427
2015-08-18T00:00:00Z-1.8604293534384375``````

This query returns the tangents of the field values for the `water_level` field key in the `h2o_feet` measurement. The specified time range is from `2015-08-18T00:00:00Z` to `2015-08-18T00:30:00Z`. The query results are sorted in descending order based on timestamps. The query limits the number of returned points to 4. The point offset is set to 2, which indicates that the first two points are not returned.

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

Description

The TAN() advanced syntax requires a `GROUP BY time()` clause and a nested InfluxQL function. The queries that use the advanced syntax first calculate the results of the nested functions based on the time intervals specified by the `GROUP BY time()` clauses. Then, the queries calculate the tangents of these results.

TAN() supports the following nested functions:

• COUNT()

• MEAN()

• MEDIAN()

• MODE()

• SUM()

• FIRST()

• LAST()

• MIN()

• MAX()

• PERCENTILE()

Example: Calculate the sines of the average field values for the specified field key

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

name: h2o_feet
time                  tan
-------
2015-08-18T00:00:00Z-1.7497661902817365
2015-08-18T00:12:00Z-1.8038002062256624
2015-08-18T00:24:00Z-1.9435224805850773``````

This query returns the tangents of the average field values that are calculated based on a 12-minute interval for the `water_level` field key.

To obtain the results, TSDB for InfluxDB® first calculates the average field values of the `water_level` field key based on a 12-minute interval. This process returns the same result as a query if the query uses the `MEAN()` function in combination with the `GROUP BY time()` clause, and does not use the `TAN()` function.

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

name: h2o_feet
time                   mean
--------
2015-08-18T00:00:00Z2.09
2015-08-18T00:12:00Z2.077
2015-08-18T00:24:00Z2.0460000000000003``````

Then, TSDB for InfluxDB® calculates the tangents of the average field values.