Transformation functions - Time Series Database - Alibaba Cloud Documentation Center

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

Advanced syntax

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.

Advanced syntax

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.

Advanced syntax

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.

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

Advanced syntax

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.

> 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

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.

Advanced syntax

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.

Advanced syntax

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

Advanced syntax

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.

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

Advanced syntax

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.

> 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

Advanced syntax

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.

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

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

Advanced syntax

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

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® returns0.

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

Advanced syntax

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.

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

Advanced syntax

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.

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

Advanced syntax

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.

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

Advanced syntax

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.

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

Advanced syntax

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.

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

Advanced syntax

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.

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

Advanced syntax

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.

Advanced syntax

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.

NON_NEGATIVE_DERIVATIVE() supports the following nested functions:

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

Examples

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.

Advanced syntax

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

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.

Advanced syntax

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.

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

Advanced syntax

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.

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

Advanced syntax

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

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.

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

Advanced syntax

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.

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

Advanced syntax

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.

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