The TDengine team has just released TDengine 2.6. The new version of this time-series database (TSDB) brings many new features, including optimized SQL execution and a large number of computing and analytics functions to support more usage scenarios. There are also some bug fixes.
Some important updates in TDengine 2.6 are listed below.
SQL Syntax
Support TIMESTAMP shortcut format with only the date part.
For example, “2022-02-02” will be automatically translated to “2022-02-02 00:00:00”. If we execute the following statement in previous versions:
select last(ts), sum(f1) from t1987 where ts>="2017-07-15" interval(1m) ;
TDengine will report an error:
DB error: invalid SQL: invalid timestamp (0.000065s)
Therefore we have to write as follows:
select last(ts), sum(f1) from t1987 where ts>="2017-07-15 00:00:00.000" interval(1m)
In TDengine 2.6, we can provide only the date part.
Restrict like to be followed only by strings
Add some time window related keywords
_QSTART/_QSTOP/_QDURATION
The start, stop and duration of a query time window.
_WSTART/_WSTOP/_WDURATION
The start, stop and duration of aggegate query by time window, like interval, session window, state window.
Example:
taos>
select * from tb_i;
ts | c0|
========================================
2022-02-02 02:00:00.000 | 0 |
2022-02-02 02:00:01.000 | 1 |
2022-02-02 02:00:02.000 | 2 |
2022-02-02 02:00:03.000 | 3 |
2022-02-02 02:00:04.000 | 4 |
2022-02-02 02:00:05.000 | 5 |
Query OK, 6 row(s) in set (0.007324s)
taos> select _wstart,_wstop,_wduration, avg(c0) from tb_i interval (1s);
ts | _wstart | _wstop |_wduration | avg(c0) |
=================================================================================================
2022-02-02 02:00:00.000 | 2022-02-02 02:00:00.000 | 2022-02-02 02:00:00.999 | 999 | 0.000000000 |
2022-02-02 02:00:01.000 | 2022-02-02 02:00:01.000 | 2022-02-02 02:00:01.999 | 999 | 1.000000000 |
2022-02-02 02:00:02.000 | 2022-02-02 02:00:02.000 | 2022-02-02 02:00:02.999 | 999 | 2.000000000 |
2022-02-02 02:00:03.000 | 2022-02-02 02:00:03.000 | 2022-02-02 02:00:03.999 | 999 | 3.000000000 |
2022-02-02 02:00:04.000 | 2022-02-02 02:00:04.000 | 2022-02-02 02:00:04.999 | 999 | 4.000000000 |
2022-02-02 02:00:05.000 | 2022-02-02 02:00:05.000 | 2022-02-02 02:00:05.999 | 999 | 5.000000000 |
Query OK, 6 row(s) in set (0.003594s)
New Functions
Aggregate Functions
HYPERLOGLOG() :The cardinal number of a specific database column is returned by using the hyperloglog algorithm. The benefit of using the hyperloglog algorithm is that the memory usage is under control when the data volume is huge.
SELECT HYPERLOGLOG(field_name) FROM { tb_name | stb_name } [WHERE clause];
Example:
taos> select dbig from shll;
dbig |
========================
1 |
1 |
1 |
NULL |
2 |
19 |
NULL |
9 |
Query OK, 8 row(s) in set (0.003755s)
taos> select hyperloglog(dbig) from shll;
hyperloglog(dbig)|
========================
4 |
Query OK, 1 row(s) in set (0.008388s)
MODE(): The value which has the highest frequency of occurrence. NULL is returned if there are multiple values which have the highest frequency of occurrence.
HISTOGRAM(): Returns count of data points in user-specified ranges.
ELAPSED(): It can be used to calculate the continuous time length in which there is valid data.
Time Related Functions
NOW(): The current time of the database client side system.
TODAY(): The timestamp of 00:00:00 of the database client side system.
TIMEZONE(): The timezone of the client side system.
TO_ISO8601(): The ISO8601 date/time format converted from a UNIX timestamp, plus the timezone of the client side system.
TO_UNIXTIMESTAMP(): UNIX timestamp converted from a string of date/time format.
TIMETRUNCATE(): Truncate the input timestamp with unit specified by time_unit.
TIMEDIFF():The difference between two timestamps, and rounded to the time unit specified by time_unit.
String Functions
LOWER(): Convert the input string to lower case.
UPPER(): Convert the input string to upper case.
CONCAT(): The concatenation result of two or more strings.
CONCAT_WS(): The concatenation result of two or more strings with separator.
SUBSTR(): The sub-string.
LENGTH(): The length in bytes of a string.
CHAR_LENGTH(): The length in number of characters of a string.
Selection Functions
TAIL(): The next k rows are returned after skipping the last offset_val rows, NULL values are not ignored. offset_val is an optional parameter. When it’s not specified, the last k rows are returned.
SELECT TAIL(field_name, k, offset_val) FROM {tb_name | stb_name} [WHERE clause];
UNIQUE(): 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.
SELECT UNIQUE(field_name) FROM {tb_name | stb_name} [WHERE clause];
Other Scalar Functions
STATEDURATION(): 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.
STATECOUNT(): The number of continuous rows satisfying the specified conditions for a specific column. The result is shown as an extra column for each row.
CAST(): 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.
MAVG(): 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].
Typical mathematical functions, such as ABS(), COS(), LOG(), etc.
Enhanced DIFF() function to support ignoring negative values.
taosAdapter Update
- taosAdapter supports Prometheus
remote_readandremote_write. - taosAdapter adds a new RESTful interface to support unlimited data batch pulling functions, which ensures that the application can obtain query results of any size and greatly reduces the query latency.
- JDBC-RESTful supports taosAdapter for multiple batch returns of data results.
We also fixed some bugs found in the previous versions. Please refer to the release note for details. Now you can download the binaries or get the source code of TDengine 2.6. In addition, the enterprise version of TDengine provides the DELETE function to delete data from a specified table or supertable. Please contact us for trial.
