Functions

Aggregate Functions

Aggregate queries are supported in TDengine by the following aggregate functions and selection functions.

COUNT

  1. SELECT COUNT([*|field_name]) FROM tb_name [WHERE clause];

Description: Get the number of rows or the number of non-null values in a table or a super table.

Return value type: Long integer INT64

Applicable column types: All

Applicable table types: table, super table, sub table

More explanation:

  • Wildcard (*) is used to represent all columns. The COUNT function is used to get the total number of all rows.
  • The number of non-NULL values will be returned if this function is used on a specific column.

Examples:

  1. taos> SELECT COUNT(*), COUNT(voltage) FROM meters;
  2.     count(*)        |    count(voltage)     |
  3. ================================================
  4.                     9 |                     9 |
  5. Query OK, 1 row(s) in set (0.004475s)
  7. taos> SELECT COUNT(*), COUNT(voltage) FROM d1001;
  8.     count(*)        |    count(voltage)     |
  9. ================================================
  10.                     3 |                     3 |
  11. Query OK, 1 row(s) in set (0.001075s)

AVG

  1. SELECT AVG(field_name) FROM tb_name [WHERE clause];

Description: Get the average value of a column in a table or STable

Return value type: Double precision floating number

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

Examples:

  1. taos> SELECT AVG(current), AVG(voltage), AVG(phase) FROM meters;
  2.     avg(current)        |       avg(voltage)        |        avg(phase)         |
  3. ====================================================================================
  4.             11.466666751 |             220.444444444 |               0.293333333 |
  5. Query OK, 1 row(s) in set (0.004135s)
  7. taos> SELECT AVG(current), AVG(voltage), AVG(phase) FROM d1001;
  8.     avg(current)        |       avg(voltage)        |        avg(phase)         |
  9. ====================================================================================
  10.             11.733333588 |             219.333333333 |               0.316666673 |
  11. Query OK, 1 row(s) in set (0.000943s)

TWA

  1. SELECT TWA(field_name) FROM tb_name WHERE clause;

Description: Time weighted average on a specific column within a time range

Return value type: Double precision floating number

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

More explanations:

  • Since version 2.1.3.0, function TWA can be used on stable with GROUP BY, i.e. timelines generated by GROUP BY tbname on a STable.

IRATE

  1. SELECT IRATE(field_name) FROM tb_name WHERE clause;

Description: instantaneous rate on a specific column. The last two samples in the specified time range are used to calculate instantaneous rate. If the last sample value is smaller, then only the last sample value is used instead of the difference between the last two sample values.

Return value type: Double precision floating number

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

More explanations:

  • Since version 2.1.3.0, function IRATE can be used on stble with GROUP BY, i.e. timelines generated by GROUP BY tbname on a STable.

SUM

  1. SELECT SUM(field_name) FROM tb_name [WHERE clause];

Description: The sum of a specific column in a table or STable

Return value type: Double precision floating number or long integer

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

Examples:

  1. taos> SELECT SUM(current), SUM(voltage), SUM(phase) FROM meters;
  2.     sum(current)        |     sum(voltage)      |        sum(phase)         |
  3. ================================================================================
  4.             103.200000763 |                  1984 |               2.640000001 |
  5. Query OK, 1 row(s) in set (0.001702s)
  7. taos> SELECT SUM(current), SUM(voltage), SUM(phase) FROM d1001;
  8.     sum(current)        |     sum(voltage)      |        sum(phase)         |
  9. ================================================================================
  10.             35.200000763 |                   658 |               0.950000018 |
  11. Query OK, 1 row(s) in set (0.000980s)

STDDEV

  1. SELECT STDDEV(field_name) FROM tb_name [WHERE clause];

Description: Standard deviation of a specific column in a table or STable

Return value type: Double precision floating number

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable (since version 2.0.15.1)

Examples:

  1. taos> SELECT STDDEV(current) FROM d1001;
  2.     stddev(current)      |
  3. ============================
  4.             1.020892909 |
  5. Query OK, 1 row(s) in set (0.000915s)

LEASTSQUARES

  1. SELECT LEASTSQUARES(field_name, start_val, step_val) FROM tb_name [WHERE clause];

Description: The linear regression function of the specified column and the timestamp column (primary key), start_val is the initial value and step_val is the step value.

Return value type: A string in the format of “(slope, intercept)”

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table only

Examples:

  1. taos> SELECT LEASTSQUARES(current, 1, 1) FROM d1001;
  2.             leastsquares(current, 1, 1)             |
  3. =====================================================
  4. {slop:1.000000, intercept:9.733334}                 |
  5. Query OK, 1 row(s) in set (0.000921s)

MODE

  1. SELECT MODE(field_name) FROM tb_name [WHERE clause];

Description:The value which has the highest frequency of occurrence. One random value is returned if there are multiple values which have highest frequency of occurrence. It can’t be used on timestamp column or tags.

Return value type:Same as the data type of the column being operated upon

Applicable column types:Data types except for timestamp

More explanations:Considering the number of returned result set is unpredictable, it’s suggested to limit the number of unique values to 100,000, otherwise error will be returned.

Applicable version:Since version 2.6.0.0

Examples:

  1. taos> select voltage from d002;
  2.     voltage        |
  3. ========================
  4.        1           |
  5.        1           |
  6.        2           |
  7.        19          |
  8. Query OK, 4 row(s) in set (0.003545s)
  10. taos> select mode(voltage) from d002;
  11.   mode(voltage)    |
  12. ========================
  13.        1           |
  14. Query OK, 1 row(s) in set (0.019393s)

HYPERLOGLOG

  1. SELECT HYPERLOGLOG(field_name) FROM { tb_name | stb_name } [WHERE clause];

Description:The cardinal number of a specific column is returned by using hyperloglog algorithm.

Return value type:Integer

Applicable column types:Any data type

More explanations: The benefit of using hyperloglog algorithm is that the memory usage is under control when the data volume is huge. However, when the data volume is very small, the result may be not accurate, it’s recommented to use select count(data) from (select unique(col) as data from table) in this case.

Applicable versions:Since version 2.6.0.0

Examples:

  1. taos> select dbig from shll;
  2.      dbig          |
  3. ========================
  4.        1           |
  5.        1           |
  6.        1           |
  7.        NULL        |
  8.        2           |
  9.        19          |
  10.        NULL        |
  11.        9           |
  12. Query OK, 8 row(s) in set (0.003755s)
  14. taos> select hyperloglog(dbig) from shll;
  15.   hyperloglog(dbig)|
  16. ========================
  17.        4           |
  18. Query OK, 1 row(s) in set (0.008388s)

HISTOGRAM

  1. SELECT HISTOGRAM(field_name，bin_type, bin_description, normalized) FROM tb_name [WHERE clause];

Description：Returns count of data points in user-specified ranges.

Return value type：Double or INT64, depends on normalized parameter settings.

Applicable column type：Numerical types.

Applicable versions：Since version 2.6.0.0.

Applicable table types: table, STable

Explanations：

  1. bin_type: parameter to indicate the bucket type, valid inputs are: “user_input”, “linear_bin”, “log_bin”。

  2. bin_description: parameter to describe how to generate buckets，can be in the following JSON formats for each bin_type respectively:

    • “user_input”: “[1, 3, 5, 7]“: User specified bin values.

    • “linear_bin”: “{“start”: 0.0, “width”: 5.0, “count”: 5, “infinity”: true}” “start” - bin starting point. “width” - bin offset. “count” - number of bins generated. “infinity” - whether to add（-inf, inf）as start/end point in generated set of bins. The above “linear_bin” descriptor generates a set of bins: [-inf, 0.0, 5.0, 10.0, 15.0, 20.0, +inf].

    • “log_bin”: “{“start”:1.0, “factor”: 2.0, “count”: 5, “infinity”: true}” “start” - bin starting point. “factor” - exponential factor of bin offset. “count” - number of bins generated. “infinity” - whether to add（-inf, inf）as start/end point in generated range of bins. The above “log_bin” descriptor generates a set of bins:[-inf, 1.0, 2.0, 4.0, 8.0, 16.0, +inf].

  3. normalized: setting to 1/0 to turn on/off result normalization.

Example：

  1. taos> SELECT HISTOGRAM(voltage, "user_input", "[1,3,5,7]", 1) FROM meters;
  2.      histogram(voltage, "user_input", "[1,3,5,7]", 1) |
  3.      =======================================================
  4.      {"lower_bin":1, "upper_bin":3, "count":0.333333}     |
  5.      {"lower_bin":3, "upper_bin":5, "count":0.333333}     |
  6.      {"lower_bin":5, "upper_bin":7, "count":0.333333}     |
  7.      Query OK, 3 row(s) in set (0.004273s)
  9. taos> SELECT HISTOGRAM(voltage, 'linear_bin', '{"start": 1, "width": 3, "count": 3, "infinity": false}', 0) FROM meters;
  10.          histogram(voltage, 'linear_bin', '{"start": 1, "width": 3, " |
  11.      ===================================================================
  12.      {"lower_bin":1, "upper_bin":4, "count":3}                        |
  13.      {"lower_bin":4, "upper_bin":7, "count":3}                        |
  14.      {"lower_bin":7, "upper_bin":10, "count":3}                       |
  15.      Query OK, 3 row(s) in set (0.004887s)
  17. taos> SELECT HISTOGRAM(voltage, 'log_bin', '{"start": 1, "factor": 3, "count": 3, "infinity": true}', 0) FROM meters;
  18.    histogram(voltage, 'log_bin', '{"start": 1, "factor": 3, "count" |
  19.    ===================================================================
  20.    {"lower_bin":-inf, "upper_bin":1, "count":3}                     |
  21.    {"lower_bin":1, "upper_bin":3, "count":2}                        |
  22.    {"lower_bin":3, "upper_bin":9, "count":6}                        |
  23.    {"lower_bin":9, "upper_bin":27, "count":3}                       |
  24.    {"lower_bin":27, "upper_bin":inf, "count":1}                     |

ELAPSED

  1. SELECT ELAPSED(field_name[, time_unit]) FROM { tb_name | stb_name } [WHERE clause] [INTERVAL(interval [, offset]) [SLIDING sliding]];

Description：elapsed function can be used to calculate the continuous time length in which there is valid data. If it’s used with INTERVAL clause, the returned result is the calcualted time length within each time window. If it’s used without INTERVAL caluse, the returned result is the calculated time length within the specified time range. Please be noted that the return value of elapsed is the number of time_unit in the calculated time length.

Return value type：Double

Applicable Column type：Timestamp

Applicable versions：Sicne version 2.6.0.0

Applicable tables: table, STable, outter in nested query

Explanations：

  • field_name parameter can only be the first column of a table, i.e. timestamp primary key.
  • The minimum value of time_unit is the time precision of the database. If time_unit is not specified, the time precision of the database is used as the default ime unit.
  • It can be used with INTERVAL to get the time valid time length of each time window. Please be noted that the return value is same as the time window for all time windows except for the first and the last time window.
  • order by asc/desc has no effect on the result.
  • group by tbname must be used together when elapsed is used against a STable.
  • group by must NOT be used together when elapsed is used against a table or sub table.
  • When used in nested query, it’s only applicable when the inner query outputs an implicit timestamp column as the primary key. For example, select elapsed(ts) from (select diff(value) from sub1) is legal usage while select elapsed(ts) from (select * from sub1) is not.
  • It can’t be used with leastsquares, diff, derivative, top, bottom, last_row, interp.

Selection Functions

When any select function is used, timestamp column or tag columns including tbname can be specified to show that the selected value are from which rows.

MIN

  1. SELECT MIN(field_name) FROM {tb_name | stb_name} [WHERE clause];

Description: The minimum value of a specific column in a table or STable

Return value type: Same as the data type of the column being operated upon

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

Examples:

  1. taos> SELECT MIN(current), MIN(voltage) FROM meters;
  2.     min(current)     | min(voltage) |
  3. ======================================
  4.             10.20000 |          218 |
  5. Query OK, 1 row(s) in set (0.001765s)
  7. taos> SELECT MIN(current), MIN(voltage) FROM d1001;
  8.     min(current)     | min(voltage) |
  9. ======================================
  10.             10.30000 |          218 |
  11. Query OK, 1 row(s) in set (0.000950s)

MAX

  1. SELECT MAX(field_name) FROM { tb_name | stb_name } [WHERE clause];

Description: The maximum value of a specific column of a table or STable

Return value type: Same as the data type of the column being operated upon

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

Examples:

  1. taos> SELECT MAX(current), MAX(voltage) FROM meters;
  2.     max(current)     | max(voltage) |
  3. ======================================
  4.             13.40000 |          223 |
  5. Query OK, 1 row(s) in set (0.001123s)
  7. taos> SELECT MAX(current), MAX(voltage) FROM d1001;
  8.     max(current)     | max(voltage) |
  9. ======================================
  10.             12.60000 |          221 |
  11. Query OK, 1 row(s) in set (0.000987s)

FIRST

  1. SELECT FIRST(field_name) FROM { tb_name | stb_name } [WHERE clause];

Description: The first non-null value of a specific column in a table or STable

Return value type: Same as the column being operated upon

Applicable column types: Any data type

Applicable table types: table, STable

More explanations:

  • FIRST(*) can be used to get the first non-null value of all columns
  • NULL will be returned if all the values of the specified column are all NULL
  • A result will NOT be returned if all the columns in the result set are all NULL

Examples:

  1. taos> SELECT FIRST(*) FROM meters;
  2.         first(ts)        |    first(current)    | first(voltage) |     first(phase)     |
  3. =========================================================================================
  4. 2018-10-03 14:38:04.000 |             10.20000 |            220 |              0.23000 |
  5. Query OK, 1 row(s) in set (0.004767s)
  7. taos> SELECT FIRST(current) FROM d1002;
  8.     first(current)    |
  9. =======================
  10.             10.20000 |
  11. Query OK, 1 row(s) in set (0.001023s)

LAST

  1. SELECT LAST(field_name) FROM { tb_name | stb_name } [WHERE clause];

Description: The last non-NULL value of a specific column in a table or STable

Return value type: Same as the column being operated upon

Applicable column types: Any data type

Applicable table types: table, STable

More explanations:

  • LAST(*) can be used to get the last non-NULL value of all columns
  • If the values of a column in the result set are all NULL, NULL is returned for that column; if all columns in the result are all NULL, no result will be returned.
  • When it’s used on a STable, if there are multiple values with the timestamp in the result set, one of them will be returned randomly and it’s not guaranteed that the same value is returned if the same query is run multiple times.

Examples:

  1. taos> SELECT LAST(*) FROM meters;
  2.         last(ts)         |    last(current)     | last(voltage) |     last(phase)      |
  3. ========================================================================================
  4. 2018-10-03 14:38:16.800 |             12.30000 |           221 |              0.31000 |
  5. Query OK, 1 row(s) in set (0.001452s)
  7. taos> SELECT LAST(current) FROM d1002;
  8.     last(current)     |
  9. =======================
  10.             10.30000 |
  11. Query OK, 1 row(s) in set (0.000843s)

TOP

  1. SELECT TOP(field_name, K) FROM { tb_name | stb_name } [WHERE clause];

Description: The greatest k values of a specific column in a table or STable. If a value has multiple occurrences in the column but counting all of them in will exceed the upper limit k, then a part of them will be returned randomly.

Return value type: Same as the column being operated upon

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

More explanations:

  • k must be in range [1,100]
  • The timestamp associated with the selected values are returned too
  • Can’t be used with FILL

Examples:

  1. taos> SELECT TOP(current, 3) FROM meters;
  2.         ts            |   top(current, 3)    |
  3. =================================================
  4. 2018-10-03 14:38:15.000 |             12.60000 |
  5. 2018-10-03 14:38:16.600 |             13.40000 |
  6. 2018-10-03 14:38:16.800 |             12.30000 |
  7. Query OK, 3 row(s) in set (0.001548s)
  9. taos> SELECT TOP(current, 2) FROM d1001;
  10.         ts            |   top(current, 2)    |
  11. =================================================
  12. 2018-10-03 14:38:15.000 |             12.60000 |
  13. 2018-10-03 14:38:16.800 |             12.30000 |
  14. Query OK, 2 row(s) in set (0.000810s)

BOTTOM

  1. SELECT BOTTOM(field_name, K) FROM { tb_name | stb_name } [WHERE clause];

Description: The least k values of a specific column in a table or STable. If a value has multiple occurrences in the column but counting all of them in will exceed the upper limit k, then a part of them will be returned randomly.

Return value type: Same as the column being operated upon

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

More explanations:

  • k must be in range [1,100]
  • The timestamp associated with the selected values are returned too
  • Can’t be used with FILL

Examples:

  1. taos> SELECT BOTTOM(voltage, 2) FROM meters;
  2.         ts            | bottom(voltage, 2) |
  3. ===============================================
  4. 2018-10-03 14:38:15.000 |                218 |
  5. 2018-10-03 14:38:16.650 |                218 |
  6. Query OK, 2 row(s) in set (0.001332s)
  8. taos> SELECT BOTTOM(current, 2) FROM d1001;
  9.         ts            |  bottom(current, 2)  |
  10. =================================================
  11. 2018-10-03 14:38:05.000 |             10.30000 |
  12. 2018-10-03 14:38:16.800 |             12.30000 |
  13. Query OK, 2 row(s) in set (0.000793s)

PERCENTILE

  1. SELECT PERCENTILE(field_name, P) FROM { tb_name } [WHERE clause];

Description: The value whose rank in a specific column matches the specified percentage. If such a value matching the specified percentage doesn’t exist in the column, an interpolation value will be returned.

Return value type: Double precision floating point

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table

More explanations: P is in range [0,100], when P is 0, the result is same as using function MIN; when P is 100, the result is same as function MAX.

Examples:

  1. taos> SELECT PERCENTILE(current, 20) FROM d1001;
  2. percentile(current, 20)  |
  3. ============================
  4.             11.100000191 |
  5. Query OK, 1 row(s) in set (0.000787s)

APERCENTILE

  1. SELECT APERCENTILE(field_name, P[, algo_type])
  2. FROM { tb_name | stb_name } [WHERE clause]

Description: Similar to PERCENTILE, but a simulated result is returned

Return value type: Double precision floating point

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

More explanations

  • P is in range [0,100], when P is 0, the result is same as using function MIN; when P is 100, the result is same as function MAX.
  • algo_type can only be input as default or t-digest, if it’s not specified default will be used, i.e. apercentile(column_name, 50) is same as apercentile(column_name, 50, "default").
  • When t-digest is used, t-digest sampling is used to calculate. It can be used from version 2.2.0.0.

Nested query: It can be used in both the outer query and inner query in a nested query.

  1. taos> SELECT APERCENTILE(current, 20) FROM d1001;
  2. apercentile(current, 20)  |
  3. ============================
  4.             10.300000191 |
  5. Query OK, 1 row(s) in set (0.000645s)
  7. taos> select apercentile (count, 80, 'default') from stb1;
  8.  apercentile (c0, 80, 'default') |
  9. ==================================
  10.              601920857.210056424 |
  11. Query OK, 1 row(s) in set (0.012363s)
  13. taos> select apercentile (count, 80, 't-digest') from stb1;
  14.  apercentile (c0, 80, 't-digest') |
  15. ===================================
  16.               605869120.966666579 |
  17. Query OK, 1 row(s) in set (0.011639s)

LAST_ROW

  1. SELECT LAST_ROW(field_name) FROM { tb_name | stb_name };

Description: The last row of a table or STable

Return value type: Same as the column being operated upon

Applicable column types: Any data type

Applicable table types: table, STable

More explanations:

  • When it’s used against a STable, multiple rows with the same and largest timestamp may exist, in this case one of them is returned randomly and it’s not guaranteed that the result is same if the query is run multiple times.
  • Can’t be used with INTERVAL.

Examples:

  1.  taos> SELECT LAST_ROW(current) FROM meters;
  2.  last_row(current)   |
  3.  =======================
  4.              12.30000 |
  5.  Query OK, 1 row(s) in set (0.001238s)
  7.  taos> SELECT LAST_ROW(current) FROM d1002;
  8.  last_row(current)   |
  9.  =======================
  10.              10.30000 |
  11.  Query OK, 1 row(s) in set (0.001042s)

INTERP [Since version 2.3.1]

  1. SELECT INTERP(field_name) FROM { tb_name | stb_name } [WHERE where_condition] [ RANGE(timestamp1,timestamp2) ] [EVERY(interval)] [FILL ({ VALUE | PREV | NULL | LINEAR | NEXT})];

Description: The value that matches the specified timestamp range is returned, if existing; or an interpolation value is returned.

Return value type: Same as the column being operated upon

Applicable column types: Numeric data types

Applicable table types: table, STable, nested query

More explanations

  • INTERP is used to get the value that matches the specified time slice from a column. If no such value exists an interpolation value will be returned based on FILL parameter.
  • The input data of INTERP is the value of the specified column and a where clause can be used to filter the original data. If no where condition is specified then all original data is the input.
  • The output time range of INTERP is specified by RANGE(timestamp1,timestamp2) parameter, with timestamp1<=timestamp2. timestamp1 is the starting point of the output time range and must be specified. timestamp2 is the ending point of the output time range and must be specified. If RANGE is not specified, then the timestamp of the first row that matches the filter condition is treated as timestamp1, the timestamp of the last row that matches the filter condition is treated as timestamp2.
  • The number of rows in the result set of INTERP is determined by the parameter EVERY. Starting from timestamp1, one interpolation is performed for every time interval specified EVERY parameter. If EVERY parameter is not used, the time windows will be considered as no ending timestamp, i.e. there is only one time window from timestamp1.
  • Interpolation is performed based on FILL parameter. No interpolation is performed if FILL is not used, that means either the original data that matches is returned or nothing is returned.
  • INTERP can only be used to interpolate in single timeline. So it must be used with group by tbname when it’s used on a STable. It can’t be used with GROUP BY when it’s used in the inner query of a nested query.
  • The result of INTERP is not influenced by ORDER BY TIMESTAMP, which impacts the output order only..

Examples: Based on the meters schema used throughout the documents

  • Single point linear interpolation between “2017-07-14 18:40:00” and “2017-07-14 18:40:00:
  1.  taos> SELECT INTERP(current) FROM t1 RANGE('2017-7-14 18:40:00','2017-7-14 18:40:00') FILL(LINEAR);
  • Get original data every 5 seconds, no interpolation, between “2017-07-14 18:00:00” and “2017-07-14 19:00:00:
  1.  taos> SELECT INTERP(current) FROM t1 RANGE('2017-7-14 18:00:00','2017-7-14 19:00:00') EVERY(5s);
  • Linear interpolation every 5 seconds between “2017-07-14 18:00:00” and “2017-07-14 19:00:00:
  1.   taos> SELECT INTERP(current) FROM t1 RANGE('2017-7-14 18:00:00','2017-7-14 19:00:00') EVERY(5s) FILL(LINEAR);
  • Backward interpolation every 5 seconds
  1.   taos> SELECT INTERP(current) FROM t1 EVERY(5s) FILL(NEXT);
  • Linear interpolation every 5 seconds between “2017-07-14 17:00:00” and “2017-07-14 20:00:00”
  1.   taos> SELECT INTERP(current) FROM t1 where ts >= '2017-07-14 17:00:00' and ts <= '2017-07-14 20:00:00' RANGE('2017-7-14 18:00:00','2017-7-14 19:00:00') EVERY(5s) FILL(LINEAR);

INTERP [Since version 2.0.15.0]

  1. SELECT INTERP(field_name) FROM { tb_name | stb_name } WHERE ts='timestamp' [FILL ({ VALUE | PREV | NULL | LINEAR | NEXT})];

Description: The value of a specific column that matches the specified time slice

Return value type: Same as the column being operated upon

Applicable column types: Numeric data type

Applicable table types: table, STable

More explanations:

  • Time slice must be specified. If there is no data matching the specified time slice, interpolation is performed based on FILL parameter. Conditions such as tags or tbname can be used Where clause can be used to filter data.
  • The timestamp specified must be within the time range of the data rows of the table or STable. If it is beyond the valid time range, nothing is returned even with FILL parameter.
  • INTERP can be used to query only single time point once. INTERP can be used with EVERY to get the interpolation value every time interval.
  • Examples:
  1.  taos> SELECT INTERP(*) FROM meters WHERE ts='2017-7-14 18:40:00.004';
  2.         interp(ts)        |   interp(current)    | interp(voltage) |    interp(phase)     |
  3.  ==========================================================================================
  4.   2017-07-14 18:40:00.004 |              9.84020 |             216 |              0.32222 |
  5.  Query OK, 1 row(s) in set (0.002652s)

If there is no data corresponding to the specified timestamp, an interpolation value is returned if interpolation policy is specified by FILL parameter; or nothing is returned.

  1.  taos> SELECT INTERP(*) FROM meters WHERE tbname IN ('d636') AND ts='2017-7-14 18:40:00.005';
  2.  Query OK, 0 row(s) in set (0.004022s)
  4.  taos> SELECT INTERP(*) FROM meters WHERE tbname IN ('d636') AND ts='2017-7-14 18:40:00.005' FILL(PREV);
  5.         interp(ts)        |   interp(current)    | interp(voltage) |    interp(phase)     |
  6.  ==========================================================================================
  7.   2017-07-14 18:40:00.005 |              9.88150 |             217 |              0.32500 |
  8.  Query OK, 1 row(s) in set (0.003056s)

Interpolation is performed every 5 milliseconds between ['2017-7-14 18:40:00', '2017-7-14 18:40:00.014']

  1.  taos> SELECT INTERP(current) FROM d636 WHERE ts>='2017-7-14 18:40:00' AND ts<='2017-7-14 18:40:00.014' EVERY(5a);
  2.             ts            |   interp(current)    |
  3.  =================================================
  4.   2017-07-14 18:40:00.000 |             10.04179 |
  5.   2017-07-14 18:40:00.010 |             10.16123 |
  6.  Query OK, 2 row(s) in set (0.003487s)

TAIL

  1. SELECT TAIL(field_name, k, offset_val) FROM {tb_name | stb_name} [WHERE clause];

Description: The next k rows are returned after skipping the last offset_val rows, NULL values are not ignored. offset_val is optional parameter. When it’s not specified, the last k rows are returned. When offset_val is used, the effect is same as order by ts desc LIMIT k OFFSET offset_val.

Parameter value range: k: [1,100] offset_val: [0,100]

Return value type: Same as the column being operated upon

Applicable column types: Any data type except form timestamp, i.e. the primary key

Applicable versions: Since version 2.6.0.0

Examples:

  1. taos> select ts,dbig from tail2;
  2.        ts            |         dbig          |
  3. ==================================================
  4. 2021-10-15 00:31:33.000 |                     1 |
  5. 2021-10-17 00:31:31.000 |                  NULL |
  6. 2021-12-24 00:31:34.000 |                     2 |
  7. 2022-01-01 08:00:05.000 |                    19 |
  8. 2022-01-01 08:00:06.000 |                  NULL |
  9. 2022-01-01 08:00:07.000 |                     9 |
  10. Query OK, 6 row(s) in set (0.001952s)
  12. taos> select tail(dbig,2,2) from tail2;
  13. ts                      |    tail(dbig,2,2)     |
  14. ==================================================
  15. 2021-12-24 00:31:34.000 |                     2 |
  16. 2022-01-01 08:00:05.000 |                    19 |
  17. Query OK, 2 row(s) in set (0.002307s)

UNIQUE

  1. SELECT UNIQUE(field_name) FROM {tb_name | stb_name} [WHERE clause];

Description: The values that occur the first time in the specified column. The effect is similar to distinct keyword, but it can also be used to match tags or timestamp.

Return value type: Same as the column or tag being operated upon

Applicable column types: Any data types except for timestamp

Applicable versions: Since version 2.6.0.0

More explanations:

  • It can be used against table or STable, but can’t be used together with time window, like interval, state_window or session_window .
  • Considering the number of result sets is unpredictable, it’s suggested to limit the distinct values under 100,000 to control the memory usage, otherwise error will be returned.

Examples:

  1. taos> select ts,voltage from unique1;
  2.        ts            |        voltage        |
  3. ==================================================
  4. 2021-10-17 00:31:31.000 |                     1 |
  5. 2022-01-24 00:31:31.000 |                     1 |
  6. 2021-10-17 00:31:31.000 |                     1 |
  7. 2021-12-24 00:31:31.000 |                     2 |
  8. 2022-01-01 08:00:01.000 |                    19 |
  9. 2021-10-17 00:31:31.000 |                  NULL |
  10. 2022-01-01 08:00:02.000 |                  NULL |
  11. 2022-01-01 08:00:03.000 |                     9 |
  12. Query OK, 8 row(s) in set (0.003018s)
  14. taos> select unique(voltage) from unique1;
  15. ts                      |    unique(voltage)    |
  16. ==================================================
  17. 2021-10-17 00:31:31.000 |                     1 |
  18. 2021-10-17 00:31:31.000 |                  NULL |
  19. 2021-12-24 00:31:31.000 |                     2 |
  20. 2022-01-01 08:00:01.000 |                    19 |
  21. 2022-01-01 08:00:03.000 |                     9 |
  22. Query OK, 5 row(s) in set (0.108458s)

Scalar functions

DIFF

  1. SELECT {DIFF(field_name, ignore_negative) | DIFF(field_name)} FROM tb_name [WHERE clause];

Description: The different of each row with its previous row for a specific column. ignore_negative can be specified as 0 or 1, the default value is 1 if it’s not specified. 1 means negative values are ignored.

Return value type: Same as the column being operated upon

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

More explanations:

  • The number of result rows is the number of rows subtracted by one, no output for the first row
  • Since version 2.1.30, DIFF can be used on STable with GROUP by tbname
  • Since version 2.6.0, ignore_negative parameter is supported

Examples:

  1. taos> SELECT DIFF(current) FROM d1001;
  2.         ts            |    diff(current)     |
  3. =================================================
  4. 2018-10-03 14:38:15.000 |              2.30000 |
  5. 2018-10-03 14:38:16.800 |             -0.30000 |
  6. Query OK, 2 row(s) in set (0.001162s)

DERIVATIVE

  1. SELECT DERIVATIVE(field_name, time_interval, ignore_negative) FROM tb_name [WHERE clause];

Description: The derivative of a specific column. The time rage can be specified by parameter time_interval, the minimum allowed time range is 1 second (1s); the value of ignore_negative can be 0 or 1, 1 means negative values are ignored.

Return value type: Double precision floating point

Applicable column types: Data types except for timestamp, binary, nchar and bool

Applicable table types: table, STable

More explanations:

  • It is available from version 2.1.3.0, the number of result rows is the number of total rows in the time range subtracted by one, no output for the first row.
  • It can be used together with GROUP BY tbname against a STable.

Examples:

  1. taos> select derivative(current, 10m, 0) from t1;
  2.            ts            | derivative(current, 10m, 0) |
  3. ========================================================
  4.  2021-08-20 10:11:22.790 |                 0.500000000 |
  5.  2021-08-20 11:11:22.791 |                 0.166666620 |
  6.  2021-08-20 12:11:22.791 |                 0.000000000 |
  7.  2021-08-20 13:11:22.792 |                 0.166666620 |
  8.  2021-08-20 14:11:22.792 |                -0.666666667 |
  9. Query OK, 5 row(s) in set (0.004883s)

SPREAD

  1. SELECT SPREAD(field_name) FROM { tb_name | stb_name } [WHERE clause];

Description: The difference between the max and the min of a specific column

Return value type: Double precision floating point

Applicable column types: Data types except for binary, nchar, and bool

Applicable table types: table, STable

More explanations: Can be used on a column of TIMESTAMP type, the result is the time range size.

Examples:

  1. taos> SELECT SPREAD(voltage) FROM meters;
  2.     spread(voltage)      |
  3. ============================
  4.             5.000000000 |
  5. Query OK, 1 row(s) in set (0.001792s)
  7. taos> SELECT SPREAD(voltage) FROM d1001;
  8.     spread(voltage)      |
  9. ============================
  10.             3.000000000 |
  11. Query OK, 1 row(s) in set (0.000836s)

CEIL

  1. SELECT CEIL(field_name) FROM { tb_name | stb_name } [WHERE clause];

Description: The rounded up value of a specific column

Return value type: Same as the column being used

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and outer query

More explanations:

  • Can’t be used on any tags of any type
  • Arithmetic operation can be performed on the result of ceil function
  • Can’t be used with aggregate functions

FLOOR

  1. SELECT FLOOR(field_name) FROM { tb_name | stb_name } [WHERE clause];

Description: The rounded down value of a specific column

More explanations: The restrictions are same as those of the CEIL function.

ROUND

  1. SELECT ROUND(field_name) FROM { tb_name | stb_name } [WHERE clause];

Description: The rounded value of a specific column.

More explanations: The restrictions are same as CEIL function.

CSUM

  1.     SELECT CSUM(field_name) FROM { tb_name | stb_name } [WHERE clause]

Description: The cumulative sum of each row for a specific column. The number of output rows is same as that of the input rows.

Return value type: Long integer for integers; Double for floating points. Timestamp is returned for each row.

Applicable data types: Data types except for timestamp, binary, nchar, and bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

More explanations:

  • Can’t be used on tags when it’s used on STable
  • Arithmetic operation can’t be performed on the result of csum function
  • Can only be used with aggregate functions
  • Group by tbname must be used together on a STable to force the result on a single timeline

Applicable versions: Since 2.3.0.x

MAVG

  1.     SELECT MAVG(field_name, K) FROM { tb_name | stb_name } [WHERE clause]

Description: The moving average of continuous k values of a specific column. If the number of input rows is less than k, nothing is returned. The applicable range of k is [1,1000].

Return value type: Double precision floating point

Applicable data types: Data types except for timestamp, binary, nchar, and bool

Applicable nested query: Inner query and Outer query

Applicable table types: table, STable

More explanations:

  • Arithmetic operation can’t be performed on the result of MAVG.
  • Can only be used with data columns, can’t be used with tags.
  • Can’t be used with aggregate functions.
  • Must be used with GROUP BY tbname when it’s used on a STable to force the result on each single timeline.

Applicable versions: Since 2.3.0.x

SAMPLE

  1.     SELECT SAMPLE(field_name, K) FROM { tb_name | stb_name } [WHERE clause]

Description: k sampling values of a specific column. The applicable range of k is [1,10000]

Return value type: Same as the column being operated plus the associated timestamp

Applicable data types: Any data type except for tags of STable

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

More explanations:

  • Arithmetic operation can’t be operated on the result of SAMPLE function
  • Must be used with Group by tbname when it’s used on a STable to force the result on each single timeline

Applicable versions: Since 2.3.0.x

ASIN

  1. SELECT ASIN(field_name) FROM { tb_name | stb_name } [WHERE clause]

Description: The anti-sine of a specific column

Return value type: Double if the input value is not NULL; or NULL if the input value is NULL

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • Can’t be used with tags
  • Can’t be used with aggregate functions

ACOS

  1. SELECT ACOS(field_name) FROM { tb_name | stb_name } [WHERE clause]

Description: The anti-cosine of a specific column

Return value type: Double if the input value is not NULL; or NULL if the input value is NULL

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • Can’t be used with tags
  • Can’t be used with aggregate functions

ATAN

  1. SELECT ATAN(field_name) FROM { tb_name | stb_name } [WHERE clause]

Description: anti-tangent of a specific column

Description: The anti-cosine of a specific column

Return value type: Double if the input value is not NULL; or NULL if the input value is NULL

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • Can’t be used with tags
  • Can’t be used with aggregate functions

SIN

  1. SELECT SIN(field_name) FROM { tb_name | stb_name } [WHERE clause]

Description: The sine of a specific column

Description: The anti-cosine of a specific column

Return value type: Double if the input value is not NULL; or NULL if the input value is NULL

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • Can’t be used with tags
  • Can’t be used with aggregate functions

COS

  1. SELECT COS(field_name) FROM { tb_name | stb_name } [WHERE clause]

Description: The cosine of a specific column

Description: The anti-cosine of a specific column

Return value type: Double if the input value is not NULL; or NULL if the input value is NULL

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • Can’t be used with tags
  • Can’t be used with aggregate functions

TAN

  1. SELECT TAN(field_name) FROM { tb_name | stb_name } [WHERE clause]

Description: The tangent of a specific column

Description: The anti-cosine of a specific column

Return value type: Double if the input value is not NULL; or NULL if the input value is NULL

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • Can’t be used with tags
  • Can’t be used with aggregate functions

POW

  1. SELECT POW(field_name, power) FROM { tb_name | stb_name } [WHERE clause]

Description: The power of a specific column with power as the index

Return value type: Double if the input value is not NULL; or NULL if the input value is NULL

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • Can’t be used with tags
  • Can’t be used with aggregate functions

LOG

  1. SELECT LOG(field_name, base) FROM { tb_name | stb_name } [WHERE clause]

Description: The log of a specific with base as the radix

Return value type: Double if the input value is not NULL; or NULL if the input value is NULL

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • Can’t be used with tags
  • Can’t be used with aggregate functions

ABS

  1. SELECT ABS(field_name) FROM { tb_name | stb_name } [WHERE clause]

Description: The absolute of a specific column

Return value type: UBIGINT if the input value is integer; DOUBLE if the input value is FLOAT/DOUBLE

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • Can’t be used with tags
  • Can’t be used with aggregate functions

SQRT

  1. SELECT SQRT(field_name) FROM { tb_name | stb_name } [WHERE clause]

Description: The square root of a specific column

Return value type: Double if the input value is not NULL; or NULL if the input value is NULL

Applicable data types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • Can’t be used with tags
  • Can’t be used with aggregate functions

CAST

  1. SELECT CAST(expression AS type_name) FROM { tb_name | stb_name } [WHERE clause]

Description: It’s used for type casting. The input parameter expression can be data columns, constants, scalar functions or arithmetic between them. Can’t be used with tags, and can only be used in select clause.

Return value type: The type specified by parameter type_name

Applicable data types:

  • Parameter expression can be any data type except for JSON, more specifically it can be any of BOOL/TINYINT/SMALLINT/INT/BIGINT/FLOAT/DOUBLE/BINARY(M)/TIMESTAMP/NCHAR(M)/TINYINT UNSIGNED/SMALLINT UNSIGNED/INT UNSIGNED/BIGINT UNSIGNED
  • The output data type specified by type_name can only be one of BIGINT/BINARY(N)/TIMESTAMP/NCHAR(N)/BIGINT UNSIGNED

Applicable versions: From 2.6.0.0

More explanations:

  • Error will be reported for unsupported type casting
  • NULL will be returned if the input value is NULL
  • Some values of some supported data types may not be casted, below are known issues: 1）When casting BINARY/NCHAR to BIGINT/BIGINT UNSIGNED, some characters may be treated as illegal, for example “a” may be converted to 0. 2）There may be overflow when casting singed integer or TIMESTAMP to unsigned BIGINT 3）There may be overflow when casting unsigned BIGINT to BIGINT 4）There may be overflow when casting FLOAT/DOUBLE to BIGINT or UNSIGNED BIGINT

CONCAT

  1. SELECT CONCAT(str1|column1, str2|column2, ...) FROM { tb_name | stb_name } [WHERE clause]

Description: The concatenation result of two or more strings, the number of strings to be concatenated is at least 2 and at most 8

Return value type: Same as the columns being operated, BINARY or NCHAR; or NULL if all the input are NULL

Applicable data types: The input data must be in either all BINARY or in all NCHAR; can’t be used on tag columns

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

CONCAT_WS

  1. SELECT CONCAT_WS(separator, str1|column1, str2|column2, ...) FROM { tb_name | stb_name } [WHERE clause]

Description: The concatenation result of two or more strings with separator, the number of strings to be concatenated is at least 3 and at most 9

Return value type: Same as the columns being operated, BINARY or NCHAR; or NULL if all the input are NULL

Applicable data types: The input data must be in either all BINARY or in all NCHAR; can’t be used on tag columns

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • If the value of separator is NULL, the output is NULL. If the value of separator is not NULL but other input are all NULL, the output is empty string.

LENGTH

  1. SELECT LENGTH(str|column) FROM { tb_name | stb_name } [WHERE clause]

Description: The length in bytes of a string

Return value type: Integer

Applicable data types: BINARY or NCHAR, can’t be used on tags

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations

  • If the input value is NULL, the output is NULL too

CHAR_LENGTH

  1. SELECT CHAR_LENGTH(str|column) FROM { tb_name | stb_name } [WHERE clause]

Description: The length in number of characters of a string

Return value type: Integer

Applicable data types: BINARY or NCHAR, can’t be used on tags

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations

  • If the input value is NULL, the output is NULL too

LOWER

  1. SELECT LOWER(str|column) FROM { tb_name | stb_name } [WHERE clause]

Description: Convert the input string to lower case

Return value type: Same as input

Applicable data types: BINARY or NCHAR, can’t be used on tags

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations

  • If the input value is NULL, the output is NULL too

UPPER

  1. SELECT UPPER(str|column) FROM { tb_name | stb_name } [WHERE clause]

Description: Convert the input string to upper case

Return value type: Same as input

Applicable data types: BINARY or NCHAR, can’t be used on tags

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations

  • If the input value is NULL, the output is NULL too

LTRIM

  1. SELECT LTRIM(str|column) FROM { tb_name | stb_name } [WHERE clause]

Description: Remove the left leading blanks of a string

Return value type: Same as input

Applicable data types: BINARY or NCHAR, can’t be used on tags

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations

  • If the input value is NULL, the output is NULL too

RTRIM

  1. SELECT RTRIM(str|column) FROM { tb_name | stb_name } [WHERE clause]

Description: Remove the right tailing blanks of a string

Return value type: Same as input

Applicable data types: BINARY or NCHAR, can’t be used on tags

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations

  • If the input value is NULL, the output is NULL too

SUBSTR

  1. SELECT SUBSTR(str,pos[,len]) FROM { tb_name | stb_name } [WHERE clause]

Description: The sub-string starting from pos with length of len from the original string str

Return value type: Same as input

Applicable data types: BINARY or NCHAR, can’t be used on tags

Applicable table types: table, STable

Applicable nested query: Inner query and Outer query

Applicable versions: From 2.6.0.0

More explanations:

  • If the input is NULL, the output is NULL
  • Parameter pos can be an positive or negative integer; If it’s positive, the starting position will be counted from the beginning of the string; if it’s negative, the starting position will be counted from the end of the string.
  • If len is not specified, it means from pos to the end.

Arithmetic Operations

  1. SELECT field_name [+|-|*|/|%][Value|field_name] FROM { tb_name | stb_name }  [WHERE clause];

Description: The sum, difference, product, quotient, or remainder between one or more columns

Return value type: Double precision floating point

Applicable column types: Data types except for timestamp, binary, nchar, bool

Applicable table types: table, STable

More explanations:

  • Arithmetic operations can be performed on two or more columns, Parentheses () can be used to control the order of precedence.
  • NULL doesn’t participate in the operation i.e. if one of the operands is NULL then result is NULL.

Examples:

  1. taos> SELECT current + voltage * phase FROM d1001;
  2. (current+(voltage*phase)) |
  3. ============================
  4.             78.190000713 |
  5.             84.540003240 |
  6.             80.810000718 |
  7. Query OK, 3 row(s) in set (0.001046s)

STATECOUNT

  1. SELECT STATECOUNT(field_name, oper, val) FROM { tb_name | stb_name } [WHERE clause];

Description: The number of continuous rows satisfying the specified conditions for a specific column. The result is shown as an extra column for each row. If the specified condition is evaluated as true, the number is increased by 1; otherwise the number is reset to -1. If the input value is NULL, then the corresponding row is skipped.

Applicable parameter values:

  • oper : Can be one of LT (lower than), GT (greater than), LE (lower than or euqal to), GE (greater than or equal to), NE (not equal to), EQ (equal to), the value is case insensitive
  • val ： Numeric types

Return value type: Integer

Applicable data types: Data types excpet for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Outer query only

Applicable versions: From 2.6.0.0

More explanations:

  • Must be used together with GROUP BY tbname when it’s used on a STable to force the result into each single timeline]
  • Can’t be used with window operation, like interval/state_window/session_window

Examples:

  1. taos> select ts,dbig from statef2;
  2.           ts               |         dbig          |
  3. ========================================================
  4. 2021-10-15 00:31:33.000000000 |                     1 |
  5. 2021-10-17 00:31:31.000000000 |                  NULL |
  6. 2021-12-24 00:31:34.000000000 |                     2 |
  7. 2022-01-01 08:00:05.000000000 |                    19 |
  8. 2022-01-01 08:00:06.000000000 |                  NULL |
  9. 2022-01-01 08:00:07.000000000 |                     9 |
  10. Query OK, 6 row(s) in set (0.002977s)
  12. taos> select stateCount(dbig,GT,2) from statef2;
  13. ts               |         dbig          | statecount(dbig,gt,2) |
  14. ================================================================================
  15. 2021-10-15 00:31:33.000000000 |                     1 |                    -1 |
  16. 2021-10-17 00:31:31.000000000 |                  NULL |                  NULL |
  17. 2021-12-24 00:31:34.000000000 |                     2 |                    -1 |
  18. 2022-01-01 08:00:05.000000000 |                    19 |                     1 |
  19. 2022-01-01 08:00:06.000000000 |                  NULL |                  NULL |
  20. 2022-01-01 08:00:07.000000000 |                     9 |                     2 |
  21. Query OK, 6 row(s) in set (0.002791s)

STATEDURATION

  1. SELECT stateDuration(field_name, oper, val, unit) FROM { tb_name | stb_name } [WHERE clause];

Description: The length of time range in which all rows satisfy the specified condition for a specific column. The result is shown as an extra column for each row. The length for the first row that satisfies the condition is 0. Next, if the condition is evaluated as true for a row, the time interval between current row and its previous row is added up to the time range; otherwise the time range length is reset to -1. If the value of the column is NULL, the corresponding row is skipped.

Applicable parameter values:

  • oper : Can be one of LT (lower than), GT (greater than), LE (lower than or euqal to), GE (greater than or equal to), NE (not equal to), EQ (equal to), the value is case insensitive
  • val ： Numeric types
  • unit: The unit of time interval, can be [1s, 1m, 1h], default is 1s

Return value type: Integer

Applicable data types: Data types excpet for timestamp, binary, nchar, bool

Applicable table types: table, STable

Applicable nested query: Outer query only

Applicable versions: From 2.6.0.0

More explanations:

  • Must be used together with GROUP BY tbname when it’s used on a STable to force the result into each single timeline]
  • Can’t be used with window operation, like interval/state_window/session_window

Examples:

  1. taos> select ts,dbig from statef2;
  2.           ts               |         dbig          |
  3. ========================================================
  4. 2021-10-15 00:31:33.000000000 |                     1 |
  5. 2021-10-17 00:31:31.000000000 |                  NULL |
  6. 2021-12-24 00:31:34.000000000 |                     2 |
  7. 2022-01-01 08:00:05.000000000 |                    19 |
  8. 2022-01-01 08:00:06.000000000 |                  NULL |
  9. 2022-01-01 08:00:07.000000000 |                     9 |
  10. Query OK, 6 row(s) in set (0.002407s)
  12. taos> select stateDuration(dbig,GT,2) from statef2;
  13. ts               |         dbig          | stateduration(dbig,gt,2) |
  14. ===================================================================================
  15. 2021-10-15 00:31:33.000000000 |                     1 |                       -1 |
  16. 2021-10-17 00:31:31.000000000 |                  NULL |                     NULL |
  17. 2021-12-24 00:31:34.000000000 |                     2 |                       -1 |
  18. 2022-01-01 08:00:05.000000000 |                    19 |                        0 |
  19. 2022-01-01 08:00:06.000000000 |                  NULL |                     NULL |
  20. 2022-01-01 08:00:07.000000000 |                     9 |                        2 |
  21. Query OK, 6 row(s) in set (0.002613s)

Time Functions

Since version 2.6.0.0, below time related functions can be used in TDengine.

NOW

  1. SELECT NOW() FROM { tb_name | stb_name } [WHERE clause];
  2. SELECT select_expr FROM { tb_name | stb_name } WHERE ts_col cond_operatior NOW();
  3. INSERT INTO tb_name VALUES (NOW(), ...);

Description: The current time of the client side system

Return value type: TIMESTAMP

Applicable column types: TIMESTAMP only

Applicable table types: table, STable

More explanations:

  • Add and Subtract operation can be performed, for example NOW() + 1s, the time unit can be: b(nanosecond), u(microsecond), a(millisecond)), s(second), m(minute), h(hour), d(day), w(week)
  • The precision of the returned timestamp is same as the precision set for the current data base in use

Examples:

  1. taos> SELECT NOW() FROM meters;
  2.           now()          |
  3. ==========================
  4.   2022-02-02 02:02:02.456 |
  5. Query OK, 1 row(s) in set (0.002093s)
  7. taos> SELECT NOW() + 1h FROM meters;
  8.        now() + 1h         |
  9. ==========================
  10.   2022-02-02 03:02:02.456 |
  11. Query OK, 1 row(s) in set (0.002093s)
  13. taos> SELECT COUNT(voltage) FROM d1001 WHERE ts < NOW();
  14.         count(voltage)       |
  15. =============================
  16.                            5 |
  17. Query OK, 5 row(s) in set (0.004475s)
  19. taos> INSERT INTO d1001 VALUES (NOW(), 10.2, 219, 0.32);
  20. Query OK, 1 of 1 row(s) in database (0.002210s)

TODAY

  1. SELECT TODAY() FROM { tb_name | stb_name } [WHERE clause];
  2. SELECT select_expr FROM { tb_name | stb_name } WHERE ts_col cond_operatior TODAY()];
  3. INSERT INTO tb_name VALUES (TODAY(), ...);

Description: The timestamp of 00:00:00 of the client side system

Return value type: TIMESTAMP

Applicable column types: TIMESTAMP only

Applicable table types: table, STable

More explanations:

  • Add and Subtract operation can be performed, for example NOW() + 1s, the time unit can be: b(nanosecond), u(microsecond), a(millisecond)), s(second), m(minute), h(hour), d(day), w(week)
  • The precision of the returned timestamp is same as the precision set for the current data base in use

Examples:

  1. taos> SELECT TODAY() FROM meters;
  2.          today()          |
  3. ==========================
  4.   2022-02-02 00:00:00.000 |
  5. Query OK, 1 row(s) in set (0.002093s)
  7. taos> SELECT TODAY() + 1h FROM meters;
  8.       today() + 1h        |
  9. ==========================
  10.   2022-02-02 01:00:00.000 |
  11. Query OK, 1 row(s) in set (0.002093s)
  13. taos> SELECT COUNT(voltage) FROM d1001 WHERE ts < TODAY();
  14.         count(voltage)       |
  15. =============================
  16.                            5 |
  17. Query OK, 5 row(s) in set (0.004475s)
  19. taos> INSERT INTO d1001 VALUES (TODAY(), 10.2, 219, 0.32);
  20. Query OK, 1 of 1 row(s) in database (0.002210s)

TIMEZONE

  1. SELECT TIMEZONE() FROM { tb_name | stb_name } [WHERE clause];

Description: The timezone of the client side system

Return value type: BINARY

Applicable column types: None

Applicable table types: table, STable

Examples:

  1. taos> SELECT TIMEZONE() FROM meters;
  2.            timezone()           |
  3. =================================
  4.  UTC (UTC, +0000)               |
  5. Query OK, 1 row(s) in set (0.002093s)

TO_ISO8601

  1. SELECT TO_ISO8601(ts_val | ts_col) FROM { tb_name | stb_name } [WHERE clause];

Description: The ISO8601 date/time format converted from a UNIX timestamp, plus the timezone of the client side system

Return value type: BINARY

Applicable column types: TIMESTAMP, constant or a column

Applicable table types: table, STable

More explanations:

  • If the input is UNIX timestamp constant, the precision of the returned value is determined by the digits of the input timestamp
  • If the input is a column of TIMESTAMP type, The precision of the returned value is same as the precision set for the current data base in use

Examples:

  1. taos> SELECT TO_ISO8601(1643738400) FROM meters;
  2.    to_iso8601(1643738400)    |
  3. ==============================
  4.  2022-02-02T02:00:00+0800    |
  6. taos> SELECT TO_ISO8601(ts) FROM meters;
  7.        to_iso8601(ts)        |
  8. ==============================
  9.  2022-02-02T02:00:00+0800    |
  10.  2022-02-02T02:00:00+0800    |
  11.  2022-02-02T02:00:00+0800    |

TO_UNIXTIMESTAMP

  1. SELECT TO_UNIXTIMESTAMP(datetime_string | ts_col) FROM { tb_name | stb_name } [WHERE clause];

Description: UNIX timestamp converted from a string of date/time format

Return value type: Long integer

Applicable column types: Constant or column of BINARY/NCHAR

Applicable table types: table, STable

More explanations:

  • The input string must be compatible with ISO8601/RFC3339 standard, 0 will be returned if the string can’t be converted
  • The precision of the returned timestamp is same as the precision set for the current data base in use

Examples:

  1. taos> SELECT TO_UNIXTIMESTAMP("2022-02-02T02:00:00.000Z") FROM meters;
  2. to_unixtimestamp("2022-02-02T02:00:00.000Z") |
  3. ==============================================
  4.                                1643767200000 |
  6. taos> SELECT TO_UNIXTIMESTAMP(col_binary) FROM meters;
  7.       to_unixtimestamp(col_binary)     |
  8. ========================================
  9.                          1643767200000 |
  10.                          1643767200000 |
  11.                          1643767200000 |

TIMETRUNCATE

  1. SELECT TIMETRUNCATE(ts_val | datetime_string | ts_col, time_unit) FROM { tb_name | stb_name } [WHERE clause];

Description: Truncate the input timestamp with unit specified by time_unit

Return value type: TIMESTAMP

Applicable column types: UNIX timestamp constant, string constant of date/time format, or a column of timestamp

Applicable table types: table, STable

More explanations:

  • Time unit specified by time_unit can be: 1u(microsecond),1a(millisecond),1s(second),1m(minute),1h(hour),1d(day).
  • The precision of the returned timestamp is same as the precision set for the current data base in use

Examples:

  1. taos> SELECT TIMETRUNCATE(1643738522000, 1h) FROM meters;
  2.   timetruncate(1643738522000, 1h) |
  3. ===================================
  4.       2022-02-02 02:00:00.000     |
  5. Query OK, 1 row(s) in set (0.001499s)
  7. taos> SELECT TIMETRUNCATE("2022-02-02 02:02:02", 1h) FROM meters;
  8.   timetruncate("2022-02-02 02:02:02", 1h) |
  9. ===========================================
  10.       2022-02-02 02:00:00.000             |
  11. Query OK, 1 row(s) in set (0.003903s)
  13. taos> SELECT TIMETRUNCATE(ts, 1h) FROM meters;
  14.   timetruncate(ts, 1h)   |
  15. ==========================
  16.  2022-02-02 02:00:00.000 |
  17.  2022-02-02 02:00:00.000 |
  18.  2022-02-02 02:00:00.000 |
  19. Query OK, 3 row(s) in set (0.003903s)

TIMEDIFF

  1. SELECT TIMEDIFF(ts_val1 | datetime_string1 | ts_col1, ts_val2 | datetime_string2 | ts_col2 [, time_unit]) FROM { tb_name | stb_name } [WHERE clause];

Description: The difference between two timestamps, and rounded to the time unit specified by time_unit

Return value type: Long Integer

Applicable column types: UNIX timestamp constant, string constant of date/time format, or a column of TIMESTAMP type

Applicable table types: table, STable

More explanations:

  • Time unit specified by time_unit can be: 1u(microsecond),1a(millisecond),1s(second),1m(minute),1h(hour),1d(day).
  • The precision of the returned timestamp is same as the precision set for the current data base in use

Applicable versions:Since version 2.6.0.0

Examples:

  1. taos> SELECT TIMEDIFF(1643738400000, 1643742000000) FROM meters;
  2.  timediff(1643738400000, 1643742000000) |
  3. =========================================
  4.                                 3600000 |
  5. Query OK, 1 row(s) in set (0.002553s)
  6. taos> SELECT TIMEDIFF(1643738400000, 1643742000000, 1h) FROM meters;
  7.  timediff(1643738400000, 1643742000000, 1h) |
  8. =============================================
  9.                                           1 |
  10. Query OK, 1 row(s) in set (0.003726s)
  12. taos> SELECT TIMEDIFF("2022-02-02 03:00:00", "2022-02-02 02:00:00", 1h) FROM meters;
  13.  timediff("2022-02-02 03:00:00", "2022-02-02 02:00:00", 1h) |
  14. =============================================================
  15.                                                           1 |
  16. Query OK, 1 row(s) in set (0.001937s)
  18. taos> SELECT TIMEDIFF(ts_col1, ts_col2, 1h) FROM meters;
  19.    timediff(ts_col1, ts_col2, 1h) |
  20. ===================================
  21.                                 1 |
  22. Query OK, 1 row(s) in set (0.001937s)