Supported TSQL functions with syntax and examples - Time Series Database

TSQL supports the following categories of built-in functions for querying time series data.

Aggregate functions
Mathematical functions
Trigonometric functions
String functions
Timestamp functions
Data type conversion functions

Aggregate functions

Function	Supported data types	Return type	Description
`avg(expression)`	SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE	Same as input	Average of the expression values
`count(*)`	N/A	BIGINT	Total number of rows
`count(expression)`	All types	BIGINT	Number of non-null values
`count(distinct expression)`	All types	N/A	Number of unique non-null values
`max(expression)`	SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, VARCHAR	Same as input	Maximum value
`min(expression)`	SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, VARCHAR	Same as input	Minimum value
`ts_last(expression, timestamp)`	expression: DOUBLE, VARCHAR, BOOLEAN; timestamp: TIMESTAMP	Same as expression type	Value at the maximum timestamp
`ts_first(expression, timestamp)`	expression: DOUBLE, VARCHAR, BOOLEAN; timestamp: TIMESTAMP	Same as expression type	Value at the minimum timestamp

ts_first and ts_last

ts_first and ts_last are time-series-specific aggregate functions. When working with time series data grouped by a time window, use these functions to retrieve the value at the earliest or latest timestamp within each group — useful for getting the opening or closing value of a metric in a window.

ts_first(expression, timestamp) returns the value of expression at the row with the smallest timestamp in the group.
ts_last(expression, timestamp) returns the value of expression at the row with the largest timestamp in the group.

Mathematical functions

Most mathematical functions accept input values of the following data types: INTEGER, BIGINT, FLOAT, DOUBLE, SMALLINT. See Trigonometric functions for the full list of supported trig functions, which follow the same input type rules.

Function	Return type	Description
`ABS(x)`	Same as input	Absolute value of `x`
`CBRT(x)`	FLOAT8	Cube root of `x`
`CEIL(x)`	Same as input	Smallest integer >= `x`
`CEILING(x)`	Same as input	Smallest integer >= `x` (alias for CEIL)
`DEGREES(x)`	FLOAT8	Converts `x` from radians to degrees
`E()`	FLOAT8	Returns Euler's number: 2.718281828459045
`EXP(x)`	FLOAT8	e raised to the power of `x`
`FLOOR(x)`	Same as input	Largest integer <= `x`
`LOG(x)`	FLOAT8	Natural logarithm (base e) of `x`
`LOG(x, y)`	FLOAT8	Logarithm of `y` to base `x`
`LOG10(x)`	FLOAT8	Base-10 logarithm of `x`
`LSHIFT(x, y)`	Same as input	`x` left-shifted by `y` bits
`MOD(x, y)`	FLOAT8	Remainder of `x` divided by `y`
`NEGATIVE(x)`	Same as input	Negation of `x`
`PI`	FLOAT8	Mathematical constant pi
`POW(x, y)`	FLOAT8	`x` raised to the power of `y`
`RADIANS(x)`	FLOAT8	Converts `x` from degrees to radians
`RAND`	FLOAT8	Random number in [0, 1)
`ROUND(x)`	Same as input	`x` rounded to the nearest integer
`RSHIFT(x, y)`	Same as input	`x` right-shifted by `y` bits
`SIGN(x)`	INT	Sign of `x`: returns -1, 0, or 1
`SQRT(x)`	Same as input	Square root of `x`
`TRUNC(x, y)`	DOUBLE	`x` truncated to `y` decimal places (default: 0)

Examples

The following examples use the math_func_demo table:

SELECT * FROM math_func_demo;

+---------------+-----------------------+
|    integer    |         float         |
+---------------+-----------------------+
| 2010          | 17.4                  |
| -2002         | -1.2                  |
| 2001          | 1.2                   |
| 6005          | 1.2                   |
+---------------+-----------------------+

ABS

SELECT ABS(`integer`) FROM math_func_demo;

+------------+
|   EXPR$0   |
+------------+
| 2010       |
| 2002       |
| 2001       |
| 6005       |
+------------+
4 rows selected (0.357 seconds)

CEIL

SELECT CEIL(`float`) FROM math_func_demo;

+------------+
|   EXPR$0   |
+------------+
| 18.0       |
| -1.0       |
| 2.0        |
| 2.0        |
+------------+
4 rows selected (0.647 seconds)

FLOOR

SELECT FLOOR(`float`) FROM math_func_demo;

+------------+
|   EXPR$0   |
+------------+
| 17.0       |
| -2.0       |
| 1.0        |
| 1.0        |
+------------+
4 rows selected (0.11 seconds)

ROUND

ROUND(x) returns the nearest integer; ROUND(x, y) rounds to y decimal places.

SELECT ROUND(`float`) FROM math_func_demo;

+------------+
| EXPR$0     |
+------------+
| 3.0        |
| -1.0       |
| 1.0        |
| 1.0        |
+------------+
4 rows selected (0.061 seconds)

SELECT ROUND(`float`, 4) FROM math_func_demo;

+------------+
| EXPR$0     |
+------------+
| 3.1416     |
| -1.2       |
| 1.2        |
| 1.2        |
+------------+
4 rows selected (0.059 seconds)

LOG

LOG(x, y) returns the logarithm of y to base x. LOG10(x) uses base 10. LOG(x) is the natural logarithm.

SELECT LOG(2, 64) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| 6.0        |
+------------+
1 row selected (0.069 seconds)

SELECT LOG10(100) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| 2.0        |
+------------+
1 row selected (0.203 seconds)

SELECT LOG(7.5) FROM (VALUES(1));

+---------------------+
|       EXPR$0        |
+---------------------+
| 2.0149030205422647  |
+---------------------+
1 row selected (0.139 seconds)

Trigonometric functions

All trigonometric functions return FLOAT8. They accept the same input types as mathematical functions: INTEGER, BIGINT, FLOAT, DOUBLE, SMALLINT.

Function	Description
`SIN(x)`	Sine of `x`
`COS(x)`	Cosine of `x`
`TAN(x)`	Tangent of `x`
`ASIN(x)`	Arcsine of `x`
`ACOS(x)`	Arccosine of `x`
`ATAN(x)`	Arctangent of `x`
`SINH(x)`	Hyperbolic sine of `x`
`COSH(x)`	Hyperbolic cosine of `x`
`TANH(x)`	Hyperbolic tangent of `x`

Examples

Use RADIANS to convert degrees before passing to a trig function:

SELECT RADIANS(30) AS Degrees FROM (VALUES(1));

+------------+
| Degrees    |
+------------+
| 0.7853981633974483 |
+------------+
1 row selected (0.045 seconds)

SELECT SIN(0.7853981633974483) AS `Sine of 30 degrees` FROM (VALUES(1));

+-----------------------+
| Sine of 45 degrees    |
+-----------------------+
| 0.7071067811865475    |
+-----------------------+
1 row selected (0.059 seconds)

SELECT TAN(0.7853981633974483) AS `Tangent of 30 degrees` FROM (VALUES(1));

+-----------------------+
| Tangent of 45 degrees |
+-----------------------+
| 0.9999999999999999    |
+-----------------------+

String functions

All string functions return VARCHAR unless noted otherwise.

Function	Return type	Syntax
CONCAT	VARCHAR	`CONCAT(string [, string [, ...]])`
INITCAP	VARCHAR	`INITCAP(string)`
LENGTH	INTEGER	`LENGTH(string [, encoding])`
LOWER	VARCHAR	`LOWER(string)`
LPAD	VARCHAR	`LPAD(string, length [, fill text])`
LTRIM	VARCHAR	`LTRIM(string1, string2)`
REGEXP_REPLACE	VARCHAR	`REGEXP_REPLACE(source_char, pattern, replacement)`
RPAD	VARCHAR	`RPAD(string, length [, fill text])`
RTRIM	VARCHAR	`RTRIM(string1, string2)`
STRPOS	INTEGER	`STRPOS(string, substring)`
SUBSTR	VARCHAR	`SUBSTR(string, x [, y])`
TRIM	VARCHAR	`TRIM([leading \| trailing \| both] [string1] FROM string2)`
UPPER	VARCHAR	`UPPER(string)`

CONCAT

Concatenates two or more strings.

SELECT CONCAT('Drill', ' ', 1.0, ' ', 'release') FROM (VALUES(1));

+--------------------+
|       EXPR$0       |
+--------------------+
| Drill 1.0 release  |
+--------------------+
1 row selected (0.134 seconds)

INITCAP

Returns the string with the first letter of each word in uppercase and all remaining letters in lowercase.

SELECT INITCAP('china beijing') FROM (VALUES(1));

+---------------------------+
|          EXPR$0           |
+---------------------------+
|ChinaBeijing|
+---------------------------+
1 row selected (0.106 seconds)

LENGTH

Returns the number of characters in a string.

SELECT LENGTH('Hangzhou') FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| 8          |
+------------+
1 row selected (0.127 seconds)

LOWER and UPPER

LOWER converts all characters to lowercase; UPPER converts all to uppercase.

SELECT LOWER('China Beijing') FROM (VALUES(1));

+---------------+
|    EXPR$0     |
+---------------+
| china beijing |
+---------------+
1 row selected (0.103 seconds)

SELECT UPPER('china beijing') FROM (VALUES(1));

+---------------+
|    EXPR$0     |
+---------------+
| CHINA BEIJING |
+---------------+
1 row selected (0.081 seconds)

LPAD

Left-pads string to length using fill text (default: space). If the result exceeds length, it is truncated.

SELECT LPAD('hi', 5, 'xy') FROM (VALUES(1));

+------------------------------+
|            EXPR$0            |
+------------------------------+
| xyxhi                        |
+------------------------------+
1 row selected (0.132 seconds)

RPAD

Right-pads string to length using fill text (default: space). If the result exceeds length, it is truncated.

SELECT RPAD('hi', 5, 'xy') FROM (VALUES(1));

+-------------------------+
|         EXPR$0          |
+-------------------------+
| hixyx                   |
+-------------------------+
1 row selected (0.107 seconds)

LTRIM and RTRIM

LTRIM(string1, string2) removes characters in string2 from the left of string1. RTRIM does the same from the right. Related functions: TRIM.

SELECT LTRIM('zzzytest', 'xyz') FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| test       |
+------------+
1 row selected (0.131 seconds)

SELECT RTRIM('testxxzx', 'xyz') FROM (VALUES(1));

+--------------------+
|       EXPR$0       |
+--------------------+
| tes                |
+--------------------+
1 row selected (0.102 seconds)

REGEXP_REPLACE

Searches source_char for each occurrence of the Java regular expression pattern and replaces it with replacement.

SELECT REGEXP_REPLACE('abc, acd, ade, aef', 'a', 'b') FROM (VALUES(1));

+---------------------+
|       EXPR$0        |
+---------------------+
| bbc, bcd, bde, bef  |
+---------------------+
1 row selected (0.105 seconds)

SELECT REGEXP_REPLACE('abc, acd, ade, aef', 'a.', 'b') FROM (VALUES(1));

+-----------------+
|     EXPR$0      |
+-----------------+
| bc, bd, be, bf  |
+-----------------+
1 row selected (0.113 seconds)

STRPOS

Returns the position of substring within string. Positions are 1-indexed.

SELECT STRPOS('high', 'ig') FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| 2          |
+------------+
1 row selected (0.22 seconds)

SUBSTR

Returns a substring starting at index x. The optional y parameter specifies the end index.

SELECT SUBSTR('China Beijing', 7) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| Beijing    |
+------------+
1 row selected (0.134 seconds)

SELECT SUBSTR('China Beijing', 3, 2) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| in         |
+------------+
1 row selected (0.129 seconds)

TRIM

Removes string1 characters from the leading, trailing, or both sides of string2. Related functions: LTRIM, RTRIM.

SELECT TRIM(trailing 'A' FROM 'AABBAA') FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| AABB       |
+------------+
1 row selected (0.172 seconds)

SELECT TRIM(both 'A' FROM 'AABBAA') FROM (VALUES(1));

+---------------+
|    EXPR$0     |
+---------------+
| BB            |
+---------------+
1 row selected (0.104 seconds)

SELECT TRIM(leading 'A' FROM 'AABBAA') FROM (VALUES(1));

+-----------------+
|     EXPR$0      |
+-----------------+
| BBAA            |
+-----------------+
1 row selected (0.101 seconds)

Timestamp functions

Function	Return type	Description	Example
`now()`	TIMESTAMP	Current timestamp	`now()`
`CURRENT_TIMESTAMP`	TIMESTAMP	Current timestamp	`CURRENT_TIMESTAMP`
`CURRENT_DATE`	DATE	Current date	`CURRENT_DATE`
`CURRENT_TIME`	TIME	Current time (no date component)	`CURRENT_TIME`
`EXTRACT(component FROM timestamp/date/time)`	INTEGER	Extracts year, month, day, hour, minute, or second	`EXTRACT(day FROM 'timestamp')`
`tumble(timestamp, interval)`	TIMESTAMP	Lower boundary of the tumbling window containing `timestamp`	`tumble('timestamp', interval '5' minute)`
`date_diff(timestamp, interval)`	TIMESTAMP	New timestamp after subtracting `interval`	`date_diff(timestamp, interval '5' minute)`
`date_add(timestamp, interval)`	TIMESTAMP	New timestamp after adding `interval`	`date_add(timestamp, interval '5' minute)`

tumble

tumble(timestamp, interval) maps a timestamp to the start of the fixed-size time window that contains it. Use this function to downsample time series data by grouping rows into non-overlapping time windows and applying an aggregate function to each window.

For example, tumble(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) returns 2001-02-16 20:35:00.0 — the start of the 5-minute window that contains 20:38:40.

Examples

SELECT CURRENT_DATE FROM (VALUES(1));

+---------------+
| CURRENT_DATE  |
+---------------+
| 2019-11-27    |
+---------------+


SELECT EXTRACT(hour FROM TIME '17:12:28.5') FROM (VALUES(1));

+---------+
| EXPR$0  |
+---------+
| 17      |
+---------+


SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') FROM (VALUES(1));

+---------+
| EXPR$0  |
+---------+
| 40.0    |
+---------+


SELECT DATE_DIFF(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) FROM (VALUES(1));

+------------------------+
|         EXPR$0         |
+------------------------+
| 2001-02-16 20:33:40.0  |
+------------------------+


SELECT DATE_ADD(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) FROM (VALUES(1));

+------------------------+
|         EXPR$0         |
+------------------------+
| 2001-02-16 20:43:40.0  |
+------------------------+


SELECT tumble(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) FROM (VALUES(1));

+------------------------+
|         EXPR$0         |
+------------------------+
| 2001-02-16 20:35:00.0  |
+------------------------+

Data type conversion functions

CAST

Converts an expression from one data type to another.

Syntax

CAST (<expression> AS <data type>)

Parameters

Parameter	Description
`expression`	A value, column reference, or combination of operators and functions to evaluate
`data type`	The target data type, such as INTEGER or DATE

Examples

SELECT CAST(456 AS VARCHAR(3)) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| 456        |
+------------+
1 row selected (0.08 seconds)

SELECT CAST(456 AS CHAR(3)) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| 456        |
+------------+
1 row selected (0.093 seconds)

Timestamp, date, and time conversion functions

TSQL natively recognizes the following date and time formats:

2008-12-15
22:55:55.123...

For other formats, use the following conversion functions:

Function	Return type	Description
`TO_CHAR(expression, format)`	VARCHAR	Converts a timestamp, date, time, or number to a string
`TO_DATE(expression, format)`	DATE	Converts a string or UNIX timestamp (in milliseconds) to a date
`TO_TIME(expression)`	TIME	Converts a UNIX timestamp (in milliseconds) to a time
`TO_TIMESTAMP(VARCHAR, format)`	TIMESTAMP	Converts a formatted string to a timestamp
`TO_TIMESTAMP(DOUBLE)`	TIMESTAMP	Converts a UNIX timestamp (in seconds) to a timestamp

Joda-Time format specifiers

The format parameter in TO_CHAR, TO_DATE, and TO_TIMESTAMP uses Joda-Time symbols:

Symbol	Meaning	Presentation	Example
G	Era	Text	AD
C	Century of era (>=0)	Number	20
Y	Year of era (>=0)	Year	1996
x	Week year	Year	1996
w	Week of week year	Number	27
e	Day of week	Number	2
E	Day of week	Text	Tuesday; Tue
y	Year	Year	1996
D	Day of year	Number	189
M	Month of year	Month	July; Jul; 07
d	Day of month	Number	10
a	Half-day	Text	PM
K	Hour of half-day (0-11)	Number	0
h	Clock hour of half-day (1-12)	Number	12
H	Hour of day (0-23)	Number	0
k	Clock hour of day (1-24)	Number	24
m	Minute of hour	Number	30
s	Second of minute	Number	55
S	Fraction of second	Number	978
z	Time zone	Text	Pacific Standard Time; PST
Z	Time zone offset/ID	Zone	-0800; -08:00; America/Los_Angeles
'	Escape for text delimiter	Literal	-

TO_CHAR examples

SELECT TO_CHAR(1256.789383, '#,###.###') FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| 1,256.789  |
+------------+
1 row selected (1.767 seconds)

SELECT TO_CHAR(125677.4567, '#,###.###') FROM (VALUES(1));

+--------------+
|    EXPR$0    |
+--------------+
| 125,677.457  |
+--------------+
1 row selected (0.083 seconds)

SELECT TO_CHAR((CAST('2008-2-23' AS DATE)), 'yyyy-MMM-dd') FROM (VALUES(1));

+--------------+
|    EXPR$0    |
+--------------+
| 2008-Feb-23  |
+--------------+
1 row selected (0.166 seconds)

SELECT TO_CHAR(CAST('12:20:30' AS TIME), 'HH mm ss') FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
|122030|
+------------+
1 row selected (0.07 seconds)

SELECT TO_CHAR(CAST('2015-2-23 12:00:00' AS TIMESTAMP), 'yyyy MMM dd HH:mm:ss') FROM (VALUES(1));

+-----------------------+
|        EXPR$0         |
+-----------------------+
|2015Feb2312:00:00|
+-----------------------+
1 row selected (0.142 seconds)

TO_DATE examples

-- Convert a string to a date
SELECT TO_DATE('2015-FEB-23', 'yyyy-MMM-dd') FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| 2015-02-23 |
+------------+
1 row selected (0.077 seconds)

-- Extract year from a converted date
SELECT EXTRACT(year FROM mydate) AS `extracted year`
FROM (SELECT TO_DATE('2015-FEB-23', 'yyyy-MMM-dd') AS mydate FROM (VALUES(1)));

+----------------+
| extracted year |
+----------------+
| 2015           |
+----------------+
1 row selected (0.128 seconds)

-- Convert a UNIX timestamp (milliseconds) to a date
SELECT TO_DATE(1427849046000) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| 2015-04-01 |
+------------+
1 row selected (0.082 seconds)

TO_TIME example

-- Convert a UNIX timestamp (milliseconds) to a time
SELECT to_time(82855000) FROM (VALUES(1));

+------------+
| EXPR$0     |
+------------+
| 23:00:55   |
+------------+
1 row selected (0.086 seconds)

TO_TIMESTAMP examples

-- Convert a formatted string to a timestamp
SELECT TO_TIMESTAMP('2008-2-23 12:00:00', 'yyyy-MM-dd HH:mm:ss') FROM (VALUES(1));

+------------------------+
|         EXPR$0         |
+------------------------+
| 2008-02-23 12:00:00.0  |
+------------------------+
1 row selected (0.126 seconds)

-- Convert a UNIX timestamp (seconds) to a timestamp
SELECT TO_TIMESTAMP(1427936330) FROM (VALUES(1));

+------------------------+
|         EXPR$0         |
+------------------------+
| 2015-04-01 17:58:50.0  |
+------------------------+
1 row selected (0.114 seconds)

-- Convert a UTC string to a timestamp and extract the time zone
SELECT TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z') AS Original,
       TO_CHAR(TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z'), 'z') AS New_TZ
FROM (VALUES(1));

+------------------------+---------+
|        Original        | New_TZ  |
+------------------------+---------+
| 2015-03-30 20:49:00.0  | UTC     |
+------------------------+---------+
1 row selected (0.148 seconds)