All Products
Search
Document Center

Time Series Database:TSQL functions

Last Updated:May 19, 2022

This topic describes TSQL functions.

Aggregate functions

Aggregate function

Supported data type

Return type

Return value

avg(expression)

SMALLINT, INTEGER, BIGINT, FLOAT, and DOUBLE.

Same as the data type of the values that are specified by the expression parameter

The average value of the calculated results of a specified expression.

count(*)

N/A

BigInt

The total number of rows.

count(expression)

All data types.

BigInt

The number of non-null values of a specified expression.

count(distinct expression)

All data types.

N/A

The number of unique non-null values of a specified expression.

max(expression)

SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, and VARCHAR.

Same as the data type of the values that are specified by the expression parameter

The maximum value of a specified expression.

min(expression)

SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, and VARCHAR.

Same as the data type of the values that are specified by the expression parameter

The minimum value of a specified expression.

ts_last(expression, timestamp)

The data types that are supported by the expression parameter are DOUBLE, VARCHAR, and BOOLEAN. The data type that is supported by the timestamp parameter is TIMESTAM

Same as the data type of the values that are specified by the expression parameter

The value that corresponds to the maximum timestamp after the calculated results of the expression are sorted by timestamp.

ts_first(expression, timestamp)

The data types that are supported by the expression parameter are DOUBLE, VARCHAR, and BOOLEAN. The data type that is supported by the timestamp parameter is TIMESTAMP

Same as the data type of the values that are specified by the expression parameter

The value that corresponds to the minimum timestamp after the calculated results of the expression are sorted by timestamp.

Mathematical functions

TSQL supports the mathematical functions that are described in the following table and the trigonometric functions that are listed in the "Trigonometric functions" section. Most mathematical functions and all trigonometric functions support input values of the following data types:

  • INTEGER

  • BIGINT

  • FLOAT

  • DOUBLE

  • SMALLINT

Function

Return type

Description

ABS(x)

Same as input

Returns the absolute value of a number. The x parameter specifies the number.

CBRT(x)

FLOAT8

Returns the cube root of a number. The x parameter specifies the number.

CEIL(x)

Same as input

Returns the smallest integer that is greater than or equal to a number. The x parameter specifies the number.

CEILING(x)

Same as input

Returns the smallest integer that is greater than or equal to a number. The x parameter specifies the number.

DEGREES(x)

FLOAT8

Converts an angle that is expressed in radians to degrees. The x parameter specifies the angle in radians.

E()

FLOAT8

Returns 2.718281828459045.

EXP(x)

FLOAT8

Returns the result of the constant e raised to the power of a number. The x parameter specifies the number. The constant e is known as Euler's number.

FLOOR(x)

Same as input

Returns the largest integer that is less than or equal to a number. The x parameter specifies the number.

LOG(x)

FLOAT8

Returns the base e logarithm of a number. The x parameter specifies the number.

LOG(x, y)

FLOAT8

Returns the logarithm of a number to a given base. The x parameter specifies the base. The y parameter specifies the number.

LOG10(x)

FLOAT8

Returns the logarithm of a number to the base of 10. The x parameter specifies the number.

LSHIFT(x, y)

Same as input

Returns the result after a binary number is left shifted by a given number of bits. The x parameter specifies the binary number. The y parameter specifies the number of bits.

MOD(x, y)

FLOAT8

Returns the remainder of a number that is divided by another number. The x parameter specifies the dividend. The y parameter specifies the divisor.

NEGATIVE(x)

Same as input

Returns the opposite value of a number. The x parameter specifies the number.

PI

FLOAT8

Returns the mathematical constant pi.

POW(x, y)

FLOAT8

Returns the value of a number raised to the power of another number. The value of the y parameter specifies the exponent to which the value of the x parameter is raised.

RADIANS(x)

FLOAT8

Converts an angle that is expressed in degrees to radians. The x parameter specifies the angle in degrees.

RAND

FLOAT8

Returns a random number that is greater than or equal to 0 and less than 1.

ROUND(x)

Same as input

Returns the value of a number rounded to the nearest integer. The x parameter specifies the number.

RSHIFT(x, y)

Same as input

Returns the result after a binary number is right shifted by a given number of bits. The x parameter specifies the binary number. The y parameter specifies the number of bits.

SIGN(x)

INT

Returns a value that indicates the sign of a number. The x parameter specifies the number.

SQRT(x)

Same as input

Returns the square root of a number. The x parameter specifies the number.

TRUNC(x, y)

DOUBLE

Shortens a numeric value to a specified number of decimal places. The x parameter specifies the numeric value that you want to truncate. The y parameter specifies the number of decimal places. The y parameter is optional. The default value of the y parameter is 0.

Examples

The math_func_demo table is used in the following examples of mathematical functions. The math_func_demo table contains the following data:

select * from math_func_demo;

+---------------+-----------------------+
|    integer    |         float         |
+---------------+-----------------------+
|2010|17.4|
|-2002|-1.2|
|2001|1.2|
|6005|1.2|
+---------------+-----------------------+
  • ABS(x)

    SELECT ABS(`integer`) FROM math_func_demo;
    +------------+
    |   EXPR$0   |
    +------------+
    | 2010       |
    | 2002       |
    | 2001       |
    | 6005       |
    +------------+
    4 rows selected (0.357 seconds)
  • CEIL(x)

    SELECT CEIL(`float`) FROM math_func_demo;
    +------------+
    |   EXPR$0   |
    +------------+
    | 18.0       |
    | -1.0       |
    | 2.0        |
    | 2.0        |
    +------------+
    4 rows selected (0.647 seconds)
  • FLOOR(x)

    SELECT FLOOR(`float`) FROM math_func_demo;
    +------------+
    |   EXPR$0   |
    +------------+
    | 17.0       |
    | -2.0       |
    | 1.0        |
    | 1.0        |
    +------------+
    4 rows selected (0.11 seconds)
  • ROUND(x) and ROUND(x, y): The ROUND(x) function example returns the nearest integer. The Round (x, y) function example returns an approximate number that is rounded to four decimal places.

    ``bash
    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(x, y), LOG10(x), and LOG(x): The LOG(x, y) function example returns the logarithm of 64 to a base of 2. The LOG10(x) function example returns the logarithm of 100 to the base of 10. The LOG(x) function example returns the base e logarithm of 7.5.

    ```bash
    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

TSQL supports the following trigonometric functions. These functions return results of the FLOAT8 data type.

  • SIN(x) returns the sine of a number. The x parameter specifies the number.

  • COS(x) returns the cosine of a number. The x parameter specifies the number.

  • TAN(x) returns the tangent of a number. The x parameter specifies the number.

  • ASIN(x) returns the arcsine of a number. The x parameter specifies the number.

  • ACOS(x) returns the arccosine of a number. The x parameter specifies the number.

  • ATAN(x) returns the arctangent of a number. The x parameter specifies the number.

  • SINH(x) returns the hyperbolic sine of a number. The x parameter specifies the number.

  • COSH(x) returns the hyperbolic cosine of a number. The x parameter specifies the number.

  • TANH(x) returns the hyperbolic tangent of a number. The x parameter specifies the number.

Examples

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

The following table lists the string functions that are supported by TSQL.

Function

Return type

CONCAT

VARCHAR

INITCAP

VARCHAR

LENGTH

INTEGER

LOWER

VARCHAR

LPAD

VARCHAR

LTRIM

VARCHAR

REGEXP_REPLACE

VARCHAR

RPAD

VARCHAR

RTRIM

VARCHAR

STRPOS

INTEGER

SUBSTR

VARCHAR

TRIM

VARCHAR

UPPER

VARCHAR

Examples

  • CONCAT

    Syntax: CONCAT(string [,string [, ...]]). This function concatenates strings.

    SELECT CONCAT('Drill',' ',1.0,' ','release') FROM (VALUES(1));
    
    +--------------------+
    |       EXPR$0       |
    +--------------------+
    |Drill1.0 release  |
    +--------------------+
    1 row selected (0.134 seconds)
  • INITCAP

    Syntax: INITCAP(string). This function returns a string in which the first letter of each word is uppercase and all other letters are lowercase.

    SELECT INITCAP('china beijing') FROM (VALUES(1));
    
    +---------------------------+
    |          EXPR$0           |
    +---------------------------+
    |ChinaBeijing|
    +---------------------------+
    1 row selected (0.106 seconds)
  • LENGTH

    Syntax: LENGTH(string [, encoding]). This function returns the number of characters in a string.

    SELECT LENGTH('Hangzhou') FROM (VALUES(1));
    
    +------------+
    |   EXPR$0   |
    +------------+
    |8|
    +------------+
    1 row selected (0.127 seconds)
  • LOWER

    Syntax: LOWER(string). This function converts uppercase characters in a string to lowercase characters.

    SELECT LOWER('China Beijing') FROM (VALUES(1));
    
    +---------------+
    |    EXPR$0     |
    +---------------+
    | china beijing  |
    +---------------+
    1 row selected (0.103 seconds)
  • LPAD

    Syntax: LPAD(string,length [, fill text]). This function left pads a string to a specified length by using spaces or a specified fill text. If the length of the result string after padding exceeds the specified length, the result string is truncated to the specified length.

    SELECT LPAD('hi',5,'xy') FROM (VALUES(1));
    
    +------------------------------+
    |            EXPR$0            |
    +------------------------------+
    | xyxhi                        |
    +------------------------------+
    1 row selected (0.132 seconds)
  • LTRIM

    Syntax: LTRIM(string1, string2). This function removes specified characters from the left side of a string. The string1 parameter specifies the source string from which you want to remove characters. The string2 parameter specifies the characters that you want to remove.

    SELECT LTRIM('zzzytest','xyz') FROM (VALUES(1));
    
    +------------+
    |   EXPR$0   |
    +------------+
    | test       |
    +------------+
    1 row selected (0.131 seconds)
  • REGEXP_REPLACE

    Syntax: REGEXP_REPLACE(source_char, pattern, replacement). This function searches a string for a Java regular expression pattern and replaces each occurrence of the pattern with specified characters.

    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)
  • RPAD

    Syntax: RPAD (string, length [, fill text]). This function right pads a string to a specified length by using spaces or a fill text. If you do not specify the fill text or the fill text cannot pad the string to the specified length, spaces are added to the end of the source string or the specified fill text to the specified length. If the length of the result string after padding exceeds the specified length, the result string is truncated to the specified length.

    SELECT RPAD('hi',5,'xy') FROM (VALUES(1));
    +-------------------------+
    |         EXPR$0          |
    +-------------------------+
    | hixyx                   |
    +-------------------------+
    1 row selected (0.107 seconds)
  • RTRIM

    Syntax:RTRIM(string1, string2). This function removes specified characters from the right side of a string. The string1 parameter specifies the source string from which you want to remove characters. The string2 parameter specifies the characters that you want to remove.

    SELECT RTRIM('testxxzx','xyz')from(VALUES(1));
    +--------------------+
    |       EXPR$0       |
    +--------------------+
    | tes                |
    +--------------------+
    1 row selected (0.102 seconds)
  • STRPOS

    Syntax: STRPOS(string, substring). This function returns the position of a substring in a string.

    SELECT STRPOS('high','ig') FROM (VALUES(1));
    
    +------------+
    |   EXPR$0   |
    +------------+
    |2|
    +------------+
    1 row selected (0.22 seconds)
  • SUBSTR

    Syntax: SUBSTR(string, x, y). This function returns a substring of a string. The x parameter specifies the index of the first character that you want to include in the returned substring. The y parameter specifies the index of the last character that you want to include in the returned substring. The y parameter is optional.

    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

    Syntax: TRIM ([leading | trailing | both] [string1] from string2). This function removes specified characters from the left side, the right side, or both sides of a string. The string1 parameter specifies the string of characters that you want to remove. The string2 parameter specifies the source string.

    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)
  • UPPER

    Syntax: UPPER(string). This function converts lowercase characters in a string to uppercase characters.

    SELECT UPPER('china beijing') FROM (VALUES(1));
    
    +---------------+
    |    EXPR$0     |
    +---------------+
    | CHINA BEIJING |
    +---------------+
    1 row selected (0.081 seconds)

Timestamp functions

The following table lists the timestamp functions that are supported by TSQL.

Function

Return type

Description

Example

now()

timestamp

Returns the current timestamp.

now()

CURRENT_TIMESTAMP

timestamp

Returns the current timestamp.

CURRENT_TIMESTAMP

CURRENT_DATE

date

Returns the current date.

CURRENT_DATE

CURRENT_TIME

time

Returns the current time. The returned value excludes the date information.

None

EXTRACT(component FROM timestamp/date/time)

integer

Extracts the year, month, day, hour, minute, or second component from a timestamp, date, or time.

EXTRACT(day from 'timestamp')

tumble(timestamp, interval)

timestamp

Returns the lower boundary of a tumbling time window that contains the specified timestamp.

tumble('timestamp', interval'5'minute)

date_diff(timestamp, interval)

timestamp

Returns a new timestamp after the specified interval is subtracted from a timestamp.

date_diff(timestamp, interval'5'minute)

date_add(timestamp, interval)

timestamp

Returns a new timestamp after the specified interval is added to a timestamp.

date_add(timestamp, interval'5'minute)

The following statements provide examples on how to use the timestamp functions:

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-1620:33:40.0|
+------------------------+
SELECT DATE_ADD(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) FROM (VALUES(1));
+------------------------+
|         EXPR$0         |
+------------------------+
|2001-02-1620:43:40.0|
+------------------------+
SELECT tumble(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) FROM (VALUES(1));
+------------------------+
|         EXPR$0         |
+------------------------+
|2001-02-1620:35:00.0|
+------------------------+

Data type conversion functions

CAST

The CAST function converts the evaluated values of an expression from one data type to another data type.

  • Syntax

    CAST (<expression> AS <data type>)
  • expression

    Specifies one or more values whose data types you want to convert. An expression is a combination of operators and SQL functions that you can evaluate.

  • data type

    Specifies the data type to which the specified values are converted. For example, you can specify INTEGER or DATE.

    The following statements provide examples on how to convert a string to a number, a number to a string, and a number of one numeric type to a number of another numeric type.

    SELECT CAST(456as VARCHAR(3)) FROM (VALUES(1));
    
    +------------+
    |   EXPR$0   |
    +------------+
    |456|
    +------------+
    1 row selected (0.08 seconds)
    
    SELECT CAST(456as CHAR(3)) FROM (VALUES(1));
    
    +------------+
    |   EXPR$0   |
    +------------+
    |456|
    +------------+
    1 row selected (0.093 seconds)

Functions to convert the data types of timestamps, dates, and times

TSQL supports the date and time formats that are shown in the following examples:

  • 2008-12-15

  • 22:55:55.123…

If your date and time use other formats, use the functions for data type conversion to perform the following operations:

  • Convert values of the TIMESTAMP, DATE, TIME, INTEGER, FLOAT, or DOUBLE data type to strings of the VARCHAR data type.

  • Convert strings to values of the DATE data type.

  • Convert strings to values of the NUMBER data type.

The following table lists the functions for data type conversion that you can use in TSQL queries.

Function

Return type

TO_CHAR(expression, format)

VARCHAR

TO_DATE(expression, format)

DATE

TO_TIMESTAMP(VARCHAR, format)

TIMESTAMP

TO_TIMESTAMP(DOUBLE)

TIMESTAMP

Format specifiers for date and time conversion

The following table lists the Joda-Time format specifiers that can be used for date and time conversion.

Symbol

Meaning

Presentation

Examples

G

era

text

AD

C

century of era (>=0)

number

20

Y

year of era (>=0)

year

1996

x

weekyear

year

1996

w

week of weekyear

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

halfday of day

text

PM

K

hour of halfday (0~11)

number

0

h

clockhour of halfday (1~12) number

12

None

H

hour of day (0~23)

number

0

k

clockhour 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

'

single quotation mark, escape for text delimiter

literal

None

  • TO_CHAR examples

    Convert a number, date, time, or timestamp expression to a string.

    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)
    
    SELECT EXTRACT(year from mydate)`extracted year` FROM (SELECT TO_DATE('2015-FEB-23','yyyy-MMM-dd') AS mydate FROM (VALUES(1)));
    
    +------------+
    |   myyear   |
    +------------+
    |2015|
    +------------+
    1 row selected (0.128 seconds)

    Convert a UNIX timestamp to a date.

    SELECT TO_DATE(1427849046000) FROM (VALUES(1));
    
    +------------+
    |   EXPR$0   |
    +------------+
    |2015-04-01|
    +------------+
    1 row selected (0.082 seconds)
  • TO_TIME examples

    Convert a string to a timestamp.

    SELECT to_time(82855000) FROM (VALUES(1));
    +------------+
    | EXPR$0     |
    +------------+
    | 23:00:55   |
    +------------+
    1 row selected (0.086 seconds)
  • TO_TIMESTAMP examples

    Convert a date to a timestamp.

    ```sql
    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 time value 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 date to a timestamp that includes a time zone offset in the value.

    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)