Time-Series Functions
A function is a set of predefined operations used to compute or convert data.
Special Syntax Forms
The following syntax forms are recognized for compatibility with the SQL standard and PostgreSQL, but are equivalent to regular built-in functions.
Note
Avoid using the escape character (+) in the function.
| Special Form | Equivalent to |
|---|---|
AT TIME ZONE | timezone() |
CURRENT_CATALOG | current_database() |
COLLATION FOR | pg_collation_for() |
CURRENT_DATE | current_date() |
CURRENT_ROLE | current_user() |
CURRENT_SCHEMA | current_schema() |
CURRENT_TIMESTAMP | current_timestamp() |
CURRENT_TIME | current_time() |
CURRENT_USER | current_user() |
EXTRACT(<part> FROM <value>) | extract("<part>", <value>) |
EXTRACT_DURATION(<part>FROM <value>) | extract_duration("<part>",<value>) |
OVERLAY(<text1> PLACING <text2> FROM <int1> FOR <int2>) | overlay(<text1>,<text2>, <int1>, <int2>) |
OVERLAY(<text1> PLACING <text2> FROM <int>) | overlay(<text1>,<text2>, <int>) |
POSITION(<text1> IN <text2>) | strpos(<text2>, <text1>) |
SESSION_USER | current_user() |
SUBSTRING(<text>FOR<int1>FROM <int2>) | substring(<text>,<int2>, <int1>) |
SUBSTRING(<text> FOR <int>) | substring(<text>, 1, <int>) |
SUBSTRING(<text>FROM<int1>FOR<int2>) | substring(<text>,<int1>, <int2>) |
SUBSTRING(<text> FROM <int>) | substring(<text>, <int>) |
TRIM(<text1> FROM <text2>) | btrim(<text2>, <text1>) |
TRIM(<text1>, <text2>) | btrim(<text1>, <text2>) |
TRIM(FROM <text>) | btrim(<text>) |
TRIM(LEADING <text1> FROM <text2>) | ltrim(<text2>, <text1>) |
TRIM(LEADING FROM <text>) | ltrim(<text>) |
TRIM(TRAILING <text1> FROM <text2>) | rtrim(<text2>, <text1>) |
TRIM(TRAILING FROM <text>) | rtrim(<text>) |
USER | current_user() |
Conditional and Function-like Operators
This table lists the operators that look like built-in functions but have special evaluation rules.
| Operator | Description |
|---|---|
CAST(...) | Type cast Note - When creating a time-series table, if the data type of the TIMESTAMPT-typed column is set to TIMESTAMP, the system will automatically process it as TIMESTAMPTZ, and the conversion of this column will be done according to the time zone set by KWDB. - When using the CAST function to convert a CHAR, NCHAR or VARCHAR data type to a CHAR, NCHAR or VARCHAR data type with a different length, if the target length does not match the actual number of bytes in the original string, KWDB may return garbled code, and the client side (e.g., KaiwuDB JDBC or KaiwuDB Developer Center) will prompt an error. |
IFNULL(...) | Alias for COALESCE restricted to two operands Note - When both operands are explicitly declared, they must have the same type. Otherwise, the system cannot evaluate them. - When one operand is explicitly declared and the other operand is a constant, the system converts the constant to the specified data type and then evaluates them. If failed to convert, the system returns an error. - When both operands are constants, the system evaluates the operands if they have the same type (numeric or string type). If one operand is a numeric constant and the other one is a string-typed constant, the system converts the string-typed constant to the numeric-typed constant and then evaluates them. If failed to convert, the system returns an error. |
NULLIF(...) | Return NULL conditionally Note - When both operands are explicitly declared, they must have the same type. Otherwise, the system cannot evaluate them. - When one operand is explicitly declared and the other operand is a constant, the system converts the constant to the specified data type and then evaluates them. If failed to convert, the system returns an error. - When both operands are constants, the system evaluates the operands if they have the same type (numeric or string type). If one operand is a numeric constant and the other one is a string-typed constant, the system converts the string-typed constant to the numeric-typed constant and then evaluates them. If failed to convert, the system returns an error. |
Buit-in Functions
Date and Time Functions
Note
For all date and time functions supported by the time-series engine, their output supports a precision level of nacoseconds.
| Function → Returns | Description |
|---|---|
| age(end: timestamptz, begin: timestamptz) → interval | Calculate the interval between begin and end. |
| age(val: timestamptz) → interval | Calculate the interval between val and the current time. |
| current_timestamp() → timestamp | Return the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
| current_timestamp() → timestamptz | Return the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
| date_trunc(element: string, input: timestamp) → timestamp | Truncate input to precision element. Set all fields that are less significant than element to zero (or one, for day and month). Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. |
| date_trunc(element: string, input: timestamptz) → timestamptz | Truncate input to precision element. Set all fields that are less significant than element to zero (or one, for day and month). Compatible elements: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, millisecond, microsecond. |
| experimental_strftime(input: timestamp, extract_format: string) → string | From input, extract and format the time as identified in extract_format using standard strftime notation (though not all formatting is supported). |
| experimental_strftime(input: timestamptz, extract_format: string) → string | From input, extract and format the time as identified in extract_format using standard strftime notation (though not all formatting is supported). |
| extract(element: string, input: timestamp) → float8 | Extract element from input. Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch, timezone, timezone_hour, timezone_minute. |
| extract(element: string, input: timestamptz) → float8 | Extract element from input. Compatible elements: millennium, century, decade, year, isoyear, quarter, month, week, dayofweek, isodow, dayofyear, julian, hour, minute, second, millisecond, microsecond, epoch, timezone, timezone_hour, timezone_minute. |
| localtimestamp() → timestamp | Return the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
| localtimestamp() → timestamptz | Return the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
| localtimestamp(precision: int8) → timestamp | Return the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
| localtimestamp(precision: int8) → timestamptz | Return the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
| now() → timestamp | Return the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
| now() → timestamptz | Return the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
| statement_timestamp() → timestamp | Return the start time of the current statement. |
| statement_timestamp() → timestamptz | Return the start time of the current statement. |
| timeofday() → string | Return the current system time on one of the cluster nodes as a string. |
| time_bucket(timestamp_column: timestamp, interval: STRING) → timestamp | The time bucket function, which supports aligning the timestamp. Parameters: - timestamp_column: the TIMESTAMP-typed column. - interval: the interval, in unit of nacosecond, microsecond, millisecond, second, minute, hour, day, week, month, and year. KWDB does not support composite time formats, such as 1d1h. The following lists the input forms for each unit. - nacosecond: ns, nsec, nsecs, nanosecond, nanoseconds - miscrosecond: us, usec, usecs, microsecond, microseconds - millisecond: ms, msec, msecs, millisecond, milliseconds - second: s, sec, secs, second, seconds - minute: m, min, mins, minute, minutes - hour: h, hr, hrs, hour, hours- day: d, day, days - week: w, week, weeks - month: mon, mons, month, months - year: y, yr, yrs, year, years |
| time_bucket(timestamp_column: timestamptz, interval: STRING) → timestamptz | The time bucket function, which supports aligning the timestamp. Parameters: - timestamp_column: the TIMESTAMP-typed column. - interval: the interval, in unit of nacosecond, microsecond, millisecond, second, minute, hour, day, week, month, and year. KWDB does not support composite time formats, such as 1d1h. The following lists the input forms for each unit. - nacosecond: ns, nsec, nsecs, nanosecond, nanoseconds - miscrosecond: us, usec, usecs, microsecond, microseconds - millisecond: ms, msec, msecs, millisecond, milliseconds - second: s, sec, secs, second, seconds - minute: m, min, mins, minute, minutes - hour: h, hr, hrs, hour, hours- day: d, day, days - week: w, week, weeks - month: mon, mons, month, months - year: y, yr, yrs, year, years |
| time_bucket_gapfill(timestamp_column: timestamp, interval: STRING) → timestamp | The time bucket function, which supports aligning the timestamp based on the interval and padding the missed TIMESTAMP-typed column. This function must work with the GROUP BY clause. For details, see Interpolation Query. The time_bucket_gapfill() function can also work with the interpolate() function to pad the missed TIMESTAMP-typed column and add values to specified columns. Parameters: - timestamp_column: the TIMESTAMP-typed column. - interval: the interval, in unit of nacosecond, microsecond, millisecond, second, minute, hour, day, week, month, and year. KWDB does not support composite time formats, such as 1d1h. The following lists the input forms for each unit. - nacosecond: ns, nsec, nsecs, nanosecond, nanoseconds - miscrosecond: us, usec, usecs, microsecond, microseconds - millisecond: ms, msec, msecs, millisecond, milliseconds - second: s, sec, secs, second, seconds - minute: m, min, mins, minute, minutes - hour: h, hr, hrs, hour, hours- day: d, day, days - week: w, week, weeks - month: mon, mons, month, months - year: y, yr, yrs, year, years |
| time_bucket_gapfill(timestamp_column: timestamptz, interval: STRING) → timestamptz | The time bucket function, which supports aligning the timestamp based on the interval and padding the missed TIMESTAMP-typed column. This function must work with the GROUP BY clause. For details, see Interpolation Query. The time_bucket_gapfill() function can also work with the interpolate() function to pad the missed TIMESTAMP-typed column and add values to specified columns. Parameters: - timestamp_column: the TIMESTAMP-typed column. - interval: the interval, in unit of nacosecond, microsecond, millisecond, second, minute, hour, day, week, month, and year. KWDB does not support composite time formats, such as 1d1h. The following lists the input forms for each unit. - nacosecond: ns, nsec, nsecs, nanosecond, nanoseconds - miscrosecond: us, usec, usecs, microsecond, microseconds - millisecond: ms, msec, msecs, millisecond, milliseconds - second: s, sec, secs, second, seconds - minute: m, min, mins, minute, minutes - hour: h, hr, hrs, hour, hours- day: d, day, days - week: w, week, weeks - month: mon, mons, month, months - year: y, yr, yrs, year, years |
| time_bucket_gapfill(timestamp_column: timestamp, interval: int8) → timestamp | The time bucket function, which supports aligning the timestamp based on the interval and padding the missed TIMESTAMP-typed column. This function must work with the GROUP BY clause. For details, see Interpolation Query. The time_bucket_gapfill() function can also work with the interpolate() function to pad the missed TIMESTAMP-typed column and add values to specified columns. Parameters: - timestamp_column: the TIMESTAMP-typed column. - interval: the interval, in unit of nacosecond, microsecond, millisecond, second, minute, hour, day, week, month, and year. KWDB does not support composite time formats, such as 1d1h. The following lists the input forms for each unit. - nacosecond: ns, nsec, nsecs, nanosecond, nanoseconds - miscrosecond: us, usec, usecs, microsecond, microseconds - millisecond: ms, msec, msecs, millisecond, milliseconds - second: s, sec, secs, second, seconds - minute: m, min, mins, minute, minutes - hour: h, hr, hrs, hour, hours- day: d, day, days - week: w, week, weeks - month: mon, mons, month, months - year: y, yr, yrs, year, years |
| time_bucket_gapfill(timestamp_column: timestamptz, interval: int8) → timestamptz | The time bucket function, which supports aligning the timestamp based on the interval and padding the missed TIMESTAMP-typed column. This function must work with the GROUP BY clause. For details, see Interpolation Query. The time_bucket_gapfill() function can also work with the interpolate() function to pad the missed TIMESTAMP-typed column and add values to specified columns. Parameters: - timestamp_column: the TIMESTAMP-typed column. - interval: the interval, in unit of nacosecond, microsecond, millisecond, second, minute, hour, day, week, month, and year. KWDB does not support composite time formats, such as 1d1h. The following lists the input forms for each unit. - nacosecond: ns, nsec, nsecs, nanosecond, nanoseconds - miscrosecond: us, usec, usecs, microsecond, microseconds - millisecond: ms, msec, msecs, millisecond, milliseconds - second: s, sec, secs, second, seconds - minute: m, min, mins, minute, minutes - hour: h, hr, hrs, hour, hours- day: d, day, days - week: w, week, weeks - month: mon, mons, month, months - year: y, yr, yrs, year, years |
| transaction_timestamp() → timestamp | Return the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
| transaction_timestamp() → timestamptz | Return the time of the current transaction. The value is based on a timestamp picked when the transaction starts and which stays constant throughout the transaction. This timestamp has no relationship with the commit order of concurrent transactions. |
FLOAT and INT Functions
| Function → Returns | Description |
|---|---|
| abs(val: float4) → float4 | Calculate the absolute value of val. |
| abs(val: float8) → float8 | Calculate the absolute value of val. |
| abs(val: int2) → int2 | Calculate the absolute value of val. |
| abs(val: int4) → int4 | Calculate the absolute value of val. |
| abs(val: int8) → int8 | Calculate the absolute value of val. |
| acos(val: float4) → float8 | Calculate the inverse cosine of val. |
| acos(val: float8) → float8 | Calculate the inverse cosine of val. |
| asin(val: float4) → float8 | Calculate the inverse sine of val. |
| asin(val: float8) → float8 | Calculate the inverse sine of val. |
| atan(val: float4) → float8 | Calculate the inverse tangent of val. |
| atan(val: float8) → float8 | Calculate the inverse tangent of val. |
| atan2(x: float4, y: float4) → float8 | Calculate the inverse tangent of x/y. |
| atan2(x: float4, y: float8) → float8 | Calculate the inverse tangent of x/y. |
| atan2(x: float8, y: float4) → float8 | Calculate the inverse tangent of x/y. |
| atan2(x: float8, y: float8) → float8 | Calculate the inverse tangent of x/y. |
| cbrt(val: float4) → float8 | Calculate the cube root (∛) of val. |
| cbrt(val: float8) → float8 | Calculate the cube root (∛) of val. |
| ceil(val: float4) → float8 | Calculate the smallest integer not smaller than val. |
| ceil(val: float8) → float8 | Calculate the smallest integer not smaller than val. |
| ceil(val: int2) → float8 | Calculate the smallest integer not smaller than val. |
| ceil(val: int4) → float8 | Calculate the smallest integer not smaller than val. |
| ceil(val: int8) → float8 | Calculate the smallest integer not smaller than val. |
| ceiling(val: float4) → float8 | Calculate the smallest integer not smaller than val. |
| ceiling(val: float8) → float8 | Calculate the smallest integer not smaller than val. |
| ceiling(val: int2) → float8 | Calculate the smallest integer not smaller than val. |
| ceiling(val: int4) → float8 | Calculate the smallest integer not smaller than val. |
| ceiling(val: int8) → float8 | Calculate the smallest integer not smaller than val. |
| cos(val: float4) → float8 | Calculate the cosine of val. |
| cos(val: float8) → float8 | Calculate the cosine of val. |
| cot(val: float4) → float8 | Calculate the cotangent of val. |
| cot(val: float8) → float8 | Calculate the cotangent of val. |
| crc32c(string...) → int8 | Calculate the CRC-32 hash using the Castagnoli polynomial. |
| crc32ieee(string...) → int8 | Calculate the CRC-32 hash using the IEEE polynomial. |
| degrees(val: float4) → float8 | Convert val as a radian value to a degree value. |
| degrees(val: float8) → float8 | Convert val as a radian value to a degree value. |
| div(x: float4, y: float4) → float8 | Calculate the integer quotient of x/y. |
| div(x: float4, y: float8) → float8 | Calculate the integer quotient of x/y. |
| div(x: float8, y: float4) → float8 | Calculate the integer quotient of x/y. |
| div(x: float8, y: float8) → float8 | Calculate the integer quotient of x/y. |
| div(x: int2, y: int2) → int8 | Calculate the integer quotient of x/y. |
| div(x: int2, y: int4) → int8 | Calculate the integer quotient of x/y. |
| div(x: int2, y: int8) → int8 | Calculate the integer quotient of x/y. |
| div(x: int4, y: int2) → int8 | Calculate the integer quotient of x/y. |
| div(x: int4, y: int4) → int8 | Calculate the integer quotient of x/y. |
| div(x: int4, y: int8) → int8 | Calculate the integer quotient of x/y. |
| div(x: int8, y: int2) → int8 | Calculate the integer quotient of x/y. |
| div(x: int8, y: int4) → int8 | Calculate the integer quotient of x/y. |
| div(x: int8, y: int8) → int8 | Calculate the integer quotient of x/y. |
| exp(val: float4) → float8 | Calculate e ^ val. |
| exp(val: float8) → float8 | Calculate e ^ val. |
| floor(val: float4) → float8 | Calculate the largest integer not greater than val. |
| floor(val: float8) → float8 | Calculate the largest integer not greater than val. |
| floor(val: int2) → float8 | Calculate the largest integer not greater than val. |
| floor(val: int4) → float8 | Calculate the largest integer not greater than val. |
| floor(val: int8) → float8 | Calculate the largest integer not greater than val. |
| fnv32(string...) → int8 | Calculate the 32-bit FNV-1 hash value of a set of values. |
| fnv32a(string...) → int8 | Calculate the 32-bit FNV-1a hash value of a set of values. |
| fnv64(string...) → int8 | Calculate the 64-bit FNV-1 hash value of a set of values. |
| fnv64a(string...) → int8 | Calculate the 64-bit FNV-1a hash value of a set of values. |
| isnan(val: float4) → bool | Return true if val is NaN. Otherwise, return false. |
| isnan(val: float8) → bool | Return true if val is NaN. Otherwise, return false. |
| ln(val: float4) → float8 | Calculate the natural log of val. |
| ln(val: float8) → float8 | Calculate the natural log of val. |
| log(b: float4, x: float4) → float8 | Calculate the base b log of val. |
| log(b: float4, x: float8) → float8 | Calculate the base b log of val. |
| log(b: float8, x: float4) → float8 | Calculate the base b log of val. |
| log(b: float8, x: float8) → float8 | Calculate the base b log of val. |
| log(val: float4) → float8 | Calculate the base 10 log of val. |
| log(val: float8) → float8 | Calculate the base 10 log of val. |
| mod(x: float4, y: float4) → float8 | Calculate x%y. |
| mod(x: float4, y: float8) → float8 | Calculate x%y. |
| mod(x: float8, y: float4) → float8 | Calculate x%y. |
| mod(x: float8, y: float8) → float8 | Calculate x%y. |
| mod(x: int2, y: int2) → int8 | Calculate x%y. |
| mod(x: int2, y: int4) → int8 | Calculate x%y. |
| mod(x: int2, y: int8) → int8 | Calculate x%y. |
| mod(x: int4, y: int2) → int8 | Calculate x%y. |
| mod(x: int4, y: int4) → int8 | Calculate x%y. |
| mod(x: int4, y: int8) → int8 | Calculate x%y. |
| mod(x: int8, y: int2) → int8 | Calculate x%y. |
| mod(x: int8, y: int4) → int8 | Calculate x%y. |
| mod(x: int8, y: int8) → int8 | Calculate x%y. |
| pi() → float8 | Return the value for pi (3.141592653589793). |
| pow(x: float4, y: float4) → float8 | Calculate x^y. |
| pow(x: float4, y: float8) → float8 | Calculate x^y. |
| pow(x: float8, y: float4) → float8 | Calculate x^y. |
| pow(x: float8, y: float8) → float8 | Calculate x^y. |
| pow(x: int2, y: int2) → int8 | Calculate x^y. |
| pow(x: int2, y: int4) → int8 | Calculate x^y. |
| pow(x: int2, y: int8) → int8 | Calculate x^y. |
| pow(x: int4, y: int2) → int8 | Calculate x^y. |
| pow(x: int4, y: int4) → int8 | Calculate x^y. |
| pow(x: int4, y: int8) → int8 | Calculate x^y. |
| pow(x: int8, y: int2) → int8 | Calculate x^y. |
| pow(x: int8, y: int4) → int8 | Calculate x^y. |
| pow(x: int8, y: int8) → int8 | Calculate x^y. |
| power(x: float4, y: float4) → float8 | Calculate x^y. |
| power(x: float4, y: float8) → float8 | Calculate x^y. |
| power(x: float8, y: float4) → float8 | Calculate x^y. |
| power(x: float8, y: float8) → float8 | Calculate x^y. |
| power(x: int2, y: int2) → int8 | Calculate x^y. |
| power(x: int2, y: int4) → int8 | Calculate x^y. |
| power(x: int2, y: int8) → int8 | Calculate x^y. |
| power(x: int4, y: int2) → int8 | Calculate x^y. |
| power(x: int4, y: int4) → int8 | Calculate x^y. |
| power(x: int4, y: int8) → int8 | Calculate x^y. |
| power(x: int8, y: int2) → int8 | Calculate x^y. |
| power(x: int8, y: int4) → int8 | Calculate x^y. |
| power(x: int8, y: int8) → int8 | Calculate x^y. |
| radians(val: float4) → float8 | Convert val as a degree value to a radian value. |
| radians(val: float8) → float8 | Convert val as a degree value to a radian value. |
| random() → float8 | Return a random floating-point number between 0 (inclusive) and 1 (exclusive). |
| round(val: float4) → float8 | Round val to the nearest integer using half to even (banker's) rounding. |
| round(val: float8) → float8 | Round val to the nearest integer using half to even (banker's) rounding. |
| sign(val: float4) → float8 | Determine the sign of val. - 1: positive - 0: 0 - -1: negative |
| sign(val: float8) → float8 | Determine the sign of val. - 1: positive - 0: 0 - -1: negative |
| sign(val: int2) → int8 | Determine the sign of val. - 1: positive - 0: 0 - -1: negative |
| sign(val: int4) → int8 | Determine the sign of val. - 1: positive - 0: 0 - -1: negative |
| sign(val: int8) → int8 | Determine the sign of val. - 1: positive - 0: 0 - -1: negative |
| sin(val: float4) → float8 | Calculate the sine of val. |
| sin(val: float8) → float8 | Calculate the sine of val. |
| sqrt(val: float4) → float8 | Calculate the square root of val. |
| sqrt(val: float8) → float8 | Calculate the square root of val. |
| tan(val: float4) → float8 | Calculate the tangent of val. |
| tan(val: float8) → float8 | Calculate the tangent of val. |
| trunc(val: float4) → float8 | Truncate the decimal values of val. |
| trunc(val: float8) → float8 | Truncate the decimal values of val. |
| width_bucket(operand: int8, b1: int2, b2: int2, count: int8) → int8 | Return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range B1 to B2. |
| width_bucket(operand: int8, b1: int2, b2: int4, count: int8) → int8 | Return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range B1 to B2. |
| width_bucket(operand: int8, b1: int2, b2: int8, count: int8) → int8 | Return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range B1 to B2. |
| width_bucket(operand: int8, b1: int4, b2: int2, count: int8) → int8 | Return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range B1 to B2. |
| width_bucket(operand: int8, b1: int4, b2: int4, count: int8) → int8 | Return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range B1 to B2. |
| width_bucket(operand: int8, b1: int4, b2: int8, count: int8) → int8 | Return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range B1 to B2. |
| width_bucket(operand: int8, b1: int8, b2: int2, count: int8) → int8 | Return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range B1 to B2. |
| width_bucket(operand: int8, b1: int8, b2: int4, count: int8) → int8 | Return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range B1 to B2. |
| width_bucket(operand: int8, b1: int8, b2: int8, count: int8) → int8 | Return the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range B1 to B2. |
String and Byte Functions
| Function → Returns | Description |
|---|---|
| bit_length(val: string) → int8 | Calculate the number of bits used to represent val. |
| char_length(val: string) → int8 | Calculate the number of bytes in val. |
| character_length(val: string) → int8 | Calculate the number of bytes in val. |
| chr(val: int2) → string | Return the character with the code given in val. Inverse function of ascii(). |
| chr(val: int4) → string | Return the character with the code given in val. Inverse function of ascii(). |
| chr(val: int8) → string | Return the character with the code given in val. Inverse function of ascii(). |
| concat(string...) → string | Concatenate a comma-separated list of strings. |
| initcap(val: string) → string | Capitalize the first letter of val. |
| left(input: string, return_set: int8) → string | Return the first return_set characters from input. |
| length(val: string) → int8 | Calculate the number of characters in val. |
| lower(val: string) → string | Convert all characters in val to their lower-case equivalents. |
| lpad(string: string, length: int8) → string | Pad string to by adding the double quotation mark ("") to the left of string. If string is longer than length, it is truncated. |
| lpad(string: string, length: int8, fill: string) → string | Pad string to by adding fill to the left of string. If string is longer than length, it is truncated. |
| ltrim(input: string, trim_chars: string) → string | Remove any characters included in trim_chars from the beginning (left-hand side) of input (applies recursively). For example, ltrim('doggie', 'od') returns ggie. |
| ltrim(val: string) → string | Remove all spaces from the beginning (left-hand side) of val. |
| octet_length(val: string) → int8 | Calculate the number of bytes used to represent val. |
| right(input: string, return_set: int8) → string | Return the last return_set character from input. |
| rpad(string: string, length: int8) → string | Pad string to by adding the double quotation mark ("") to the right of string. If string is longer than length, it is truncated. |
| rpad(string: string, length: int8, fill: string) → string | Pad string to by adding fill to the right of string. If string is longer than length, it is truncated. |
| rtrim(input: string, trim_chars: string) → string | Remove any characters included in trim_chars from the end (right-hand side) of input (applies recursively). For example, ltrim('doggie', 'od') returns ggie. |
| rtrim(val: string) → string | Remove all spaces from the end (right-hand side) of val. |
| substr(input: string, regex: string) → string | Return a substring of input that matches the Regular Expression regex. Tip: Avoid using the escape character ( +) in the function. |
| substr(input: string, regex: string, escape_char: string) → string | Return a substring of input that matches the Regular Expression regex using escape_char as your escape character instead of </code>. Tip: Avoid using the escape character ( +) in the function. |
| substr(input: string, start_pos: int8) → string | Return a substring of input between sstart_pos and end_pos (count starts at 1). Tip: Avoid using the escape character ( +) in the function. |
| substr(input: string, start_pos: int8, length: int8) → string | Return a substring of input starting at substart_pos (count starts at 1). Tip: Avoid using the escape character ( +) in the function. |
| substring(input: string, regex: string) → string | Return a substring of input that matches the Regular Expression regex. Tip: Avoid using the escape character ( +) in the function. |
| substring(input: string, regex: string, escape_char: string) → string | Return a substring of input that matches the Regular Expression regex using escape_char as your escape character instead of </code>. Tip: Avoid using the escape character ( +) in the function. |
| substring(input: string, start_pos: int8) → string | Return a substring of input between sstart_pos and end_pos (count starts at 1). Tip: Avoid using the escape character ( +) in the function. |
| substring(input: string, start_pos: int8, length: int8) → string | Return a substring of input starting at substart_pos (count starts at 1). Tip: Avoid using the escape character ( +) in the function. |
| upper(val: string) → string | Convert all characters in val to their upper-case equivalents. |
Aggregate Functions
Note
- Avoid the calculation results of the
AVGandSUMfunctions exceeding the maximum range supported by the function. - When aggregate queries work with
GROUP BY, avoid excessive rows in the result set for theGROUP BYclause.
| Function → Returns | Description |
|---|---|
| avg(arg1: float4) → float8 | Calculate the average of the selected values. |
| avg(arg1: float8) → float8 | Calculate the average of the selected values. |
| avg(arg1: INT2) → DECIMAL | Calculate the average of the selected values. |
| avg(arg1: INT4) → DECIMAL | Calculate the average of the selected values. |
| avg(arg1: INT8) → DECIMAL | Calculate the average of the selected values. |
| count(arg1: anyelement) → INT8 | Calculate the number of selected elements. |
| diff(val: int2) → int2 | Calculate the difference between a column and a non-NULL value in the previous row in a format of diff (<expr>) OVER (PARTITION BY <column_list> ORDER BY <timestamp_column>), where the timestamp_column must be the first TIMESTAMP-typed column to ensure to correct order and calculation results. Support the +, -, *, /, %, //, |, ^ operators. If the current row has a NULL value or if the specified partition has only two rows and the first row is NULL, return NULL. If the previous row is NULL, then the nearest preceding non-NULL row is taken into account. Note - This function does not support nested functions. It can not be used with other aggregate functions on the same query level. - If the calculation result exceeds the INT64 range, it may result in a calculation error. Tip: When deploying KWDB in standalone mode, it helps to improve query performance if the columns after PARTITION BY are all primary tag columns. |
| diff(val: int4) → int4 | Calculate the difference between a column and a non-NULL value in the previous row in a format of diff (<expr>) OVER (PARTITION BY <column_list> ORDER BY <timestamp_column>), where the timestamp_column must be the first TIMESTAMP-typed column to ensure to correct order and calculation results. Support the +, -, *, /, %, //, |, ^ operators. If the current row has a NULL value or if the specified partition has only two rows and the first row is NULL, return NULL. If the previous row is NULL, then the nearest preceding non-NULL row is taken into account. Note - This function does not support nested functions. It can not be used with other aggregate functions on the same query level. - If the calculation result exceeds the INT64 range, it may result in a calculation error. Tip: When deploying KWDB in standalone mode, it helps to improve query performance if the columns after PARTITION BY are all primary tag columns. |
| diff(val: int8) → int8 | Calculate the difference between a column and a non-NULL value in the previous row in a format of diff (<expr>) OVER (PARTITION BY <column_list> ORDER BY <timestamp_column>), where the timestamp_column must be the first TIMESTAMP-typed column to ensure to correct order and calculation results. Support the +, -, *, /, %, //, |, ^ operators. If the current row has a NULL value or if the specified partition has only two rows and the first row is NULL, return NULL. If the previous row is NULL, then the nearest preceding non-NULL row is taken into account. Note - This function does not support nested functions. It can not be used with other aggregate functions on the same query level. - If the calculation result exceeds the INT64 range, it may result in a calculation error. Tip: When deploying KWDB in standalone mode, it helps to improve query performance if the columns after PARTITION BY are all primary tag columns. |
| diff(val: float4) → float4 | Calculate the difference between a column and a non-NULL value in the previous row in a format of diff (<expr>) OVER (PARTITION BY <column_list> ORDER BY <timestamp_column>), where the timestamp_column must be the first TIMESTAMP-typed column to ensure to correct order and calculation results. Support the +, -, *, /, %, //, |, ^ operators. If the current row has a NULL value or if the specified partition has only two rows and the first row is NULL, return NULL. If the previous row is NULL, then the nearest preceding non-NULL row is taken into account. Note - This function does not support nested functions. It can not be used with other aggregate functions on the same query level. - If the calculation result exceeds the INT64 range, it may result in a calculation error. Tip: When deploying KWDB in standalone mode, it helps to improve query performance if the columns after PARTITION BY are all primary tag columns. |
| diff(val: float8) → float8 | Calculate the difference between a column and a non-NULL value in the previous row in a format of diff (<expr>) OVER (PARTITION BY <column_list> ORDER BY <timestamp_column>), where the timestamp_column must be the first TIMESTAMP-typed column to ensure to correct order and calculation results. Support the +, -, *, /, %, //, |, ^ operators. If the current row has a NULL value or if the specified partition has only two rows and the first row is NULL, return NULL. If the previous row is NULL, then the nearest preceding non-NULL row is taken into account. Note - This function does not support nested functions. It can not be used with other aggregate functions on the same query level. - If the calculation result exceeds the INT64 range, it may result in a calculation error. Tip: When deploying KWDB in standalone mode, it helps to improve query performance if the columns after PARTITION BY are all primary tag columns. |
| ELAPSED(ts_primary_key [, time_unit]) → float8 | Calculate the continuous time length of valid data in a specified time range. When working with the TWA function, the system calculates the area under the calculation curve. When working with the time_bucket function, the system calculates the time length of valid data for each time window in the specified time range. Otherwise, the system calculates the time length of valid data in the specified time range. The absolute value of the time length returned by the ELAPSED function divides the time unit equals to the number of time units. Parameters: - ts_primary_key: the TIMESTAMP-typed column. It is must be the first TIMESTAMP-typed column, which is used to confirm the ranges of data to calculate. - time_unit: the interval, in unit of nanosecond (ns), microsecond (us), millisecond (ms), second (s), minute (m), hour (h), day (d), week (w), month (m), and year (y). If not specified, it is set to millisecond by default. |
| first(val: float4) → float4 | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first(val: float8) → float8 | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first(val: INT2) → INT2 | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first(val: INT4) → INT4 | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first(val: INT8) → INT8 | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first(val: STRING) → STRING | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first(val: timestamp) → timestamp | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first(val: timestamptz) → timestamptz | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first(val: varbytes) →varbytes | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first(val: varchar) →varchar | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first(*)→any element | Return the data with the minimum timestamp in the condition range (does not contain the null value). |
| first_row(val: float4) → float4 | Return the data with the minimum timestamp in the condition range (may be the null value). |
| first_row(val: float8) → float8 | Return the data with the minimum timestamp in the condition range (may be the null value). |
| first_row(val: INT2) → INT2 | Return the data with the minimum timestamp in the condition range (may be the null value). |
| first_row(val: INT4) → INT4 | Return the data with the minimum timestamp in the condition range (may be the null value). |
| first_row(val: INT8) → INT8 | Return the data with the minimum timestamp in the condition range (may be the null value). |
| first_row(val: STRING) → STRING | Return the data with the minimum timestamp in the condition range (may be the null value). |
| first_row(val: timestamp) → timestamp | Return the data with the minimum timestamp in the condition range (may be the null value). |
| first_row(val: timestamptz) → timestamptz | Return the data with the minimum timestamp in the condition range (may be the null value). |
| first_row(val: varbytes) →varbytes | Return the data with the minimum timestamp in the condition range (may be the null value). |
| first_row(val: varchar) →varchar | Return the data with the minimum timestamp in the condition range (may be the null value). |
| first_row(*) →any element | Return the data with the minimum timestamp in the condition range (may be the null value). |
| last(val: float4) → float4 | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last(val: float8) → float8 | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last(val: INT2) → INT2 | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last(val: INT4) → INT4 | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last(val: INT8) → INT8 | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last(val: STRING) → STRING | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last(val: timestamp) → timestamp | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last(val: timestamptz) → timestamptz | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last(val: varbytes) →varbytes | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last(val: varchar) →varchar | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last(*)→any element | Return the data with the maximum timestamp in the condition range (does not contain the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row (val: varchar) →varchar | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row(val: float4) → float4 | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row(val: float8) → float8 | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row(val: INT2) → INT2 | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row(val: INT4) → INT4 | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row(val: INT8) → INT8 | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row(val: STRING) → STRING | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row(val: timestamp) → timestamp | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row(val: timestamptz) → timestamptz | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row(val: varbytes) →varbytes | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| last_row(*) →any element | Return the data with the maximum timestamp in the condition range (may be the null value). This aggregate function is supported in multiple query scenarios, including subqueries, JOIN queries, nested queries ( FROM subqueries), queries with the WHERE, GROUP BY, HAVING, or ORDER BY clause. In addition, this function can work with other KWDB aggregate functions. Limitations: - It can only be used in a SELECT list but cannot be calculated as a common column. - It supports aggregating queries on a single column. KWDB returns an error if it works on inserted constants, operators, expressions, or functions. - This function is only used for aggregating queries on time-series tables, but not on temporary tables or relational tables. |
| max(arg1: float4) → float4 | Identify the maximum selected value. |
| max(arg1: float8) → float8 | Identify the maximum selected value. |
| max(arg1: INT2) → INT2 | Identify the maximum selected value. |
| max(arg1: INT4) → INT4 | Identify the maximum selected value. |
| max(arg1: INT8) → INT8 | Identify the maximum selected value. |
| max(arg1: STRING) → STRING | Identify the maximum selected value. |
| max(arg1: timestamp) → timestamp | Identify the maximum selected value. |
| max(arg1: timestamptz) → timestamptz | Identify the maximum selected value. |
| min(arg1: float4) → float4 | Identify the minimum selected value. |
| min(arg1: float8) → float8 | Identify the minimum selected value. |
| min(arg1: INT2) → INT2 | Identify the minimum selected value. |
| min(arg1: INT4) → INT4 | Identify the minimum selected value. |
| min(arg1: INT8) → INT8 | Identify the minimum selected value. |
| min(arg1: STRING) → STRING | Identify the minimum selected value. |
| min(arg1: timestamp) → timestamp | Identify the minimum selected value. |
| min(arg1: timestamptz) → timestamptz | Identify the minimum selected value. |
| sum(arg1: float4) → float8 | Calculate the sum of the selected values. |
| sum(arg1: float8) → float8 | Calculate the sum of the selected values. |
| sum(arg1: INT2) → DECIMAL | Calculate the sum of the selected values. |
| sum(arg1: INT4) → DECIMAL | Calculate the sum of the selected values. |
| sum(arg1: INT8) → DECIMAL | Calculate the sum of the selected values. |
| TWA(ts_primary_key, expr) → float8 | Calculate the Time-Weighted Averages (TWAs) of data points in a specified time range. When working with the time_bucket function, the system calculates the TWAs for each time window in the specified time range. Otherwise, the system calculates the TWAs in the whole time range. Parameters: - ts_primary_key: the TIMESTAMP-typed column. It is must be the first TIMESTAMP-typed column, which is used to confirm the ranges of data to calculate. If there are multiple rows of data with the same timestamp, the system returns an error. For details, see Time-Series Error Codes. - expr: the expr expression, whose calculation results is the value of the data points. The interval between data points is the weight of the data points. It is suitable for numeric data and and supports the following types: single colunms (numeric-typed), constants (numeric constants), operators (+, -, *, /, %, <<, >>, |, &), and functions whose return values are numeric values. |
Group Window Functions
| Function → Returns | Description |
|---|---|
| COUNT_WINDOW (val1: int8) | Group data into windows based on the fixed count. After being sorted by the timestamp, data are grouped into windows based on the row count. If the total number of rows is not evenly divisible by the specified row count, the last window will contain fewer rows than the specified size. For details, see Group Window Query. |
| COUNT_WINDOW (val1: int8, val2: int8) | Group data into windows based on the fixed count and sliding rows. After being sorted by the timestamp, data are grouped into windows based on the row count and sliding rows. If the total number of rows is not evenly divisible by the specified row count, the last window will contain fewer rows than the specified size. For details, see Group Window Query. |
| EVENT_WINDOW (val1: expr, val2: expr) | Group data into windows based on the start and end conditions. The window is open when data meets the start condition and is closed when the data meets the end condition. It can be any expression or involve different columns.For details, see Group Window Query. |
| STATE_WINDOW (val: int2) | Group data into windows based on the device state. Consecutive data with the same state will be grouped into one group. If the state changes, the window ends. For details, see Group Window Query. |
| STATE_WINDOW (val: int4) | Group data into windows based on the device state. Consecutive data with the same state will be grouped into one group. If the state changes, the window ends. For details, see Group Window Query. |
| STATE_WINDOW (val: int8) | Group data into windows based on the device state. Consecutive data with the same state will be grouped into one group. If the state changes, the window ends. For details, see Group Window Query. |
| STATE_WINDOW (val: bool) | Group data into windows based on the device state. Consecutive data with the same state will be grouped into one group. If the state changes, the window ends. For details, see Group Window Query. |
| STATE_WINDOW (val: char) | Group data into windows based on the device state. Consecutive data with the same state will be grouped into one group. If the state changes, the window ends. For details, see Group Window Query. |
| STATE_WINDOW (val: varchar) | Group data into windows based on the device state. Consecutive data with the same state will be grouped into one group. If the state changes, the window ends. For details, see Group Window Query. |
| SESSION_WINDOW (val1: timestamptz, val2: interval) | Group data into windows based on the timestamp-typed column and the maximum consecutive interval. If the interval between two adjacent rows exceeds the maximum consecutive interval, the data are assigned to different windows. For details, see Group Window Query. |
| SESSION_WINDOW (val1: timestamp, val2: interval) | Group data into windows based on the timestamp-typed column and the maximum consecutive interval. If the interval between two adjacent rows exceeds the maximum consecutive interval, the data are assigned to different windows. For details, see Group Window Query. |
| TIME_WINDOW (val1: timestamp, val2: interval) | Group data into windows based on the timestamp-typed column and the specified interval. After sorting the data by timestamp, the system calculates the window’s starting time based on the first timestamp and the specified interval. For details, see Group Window Query. |
| TIME_WINDOW (val1: timestamptz, val2: interval) | Group data into windows based on the timestamp-typed column and the specified interval. After sorting the data by timestamp, the system calculates the window’s starting time based on the first timestamp and the specified interval. For details, see Group Window Query. |
| TIME_WINDOW (val1: timestamp, val2: interval, val3: interval) | Group data into windows based on the timestamp-typed column, the specified interval and the sliding interval. After sorting the data by timestamp, the system calculates the window’s starting time based on the specified interval and the sliding interval. For details, see Group Window Query. |
| TIME_WINDOW (val1: timestamptz, val2: interval, val3: interval) | Group data into windows based on the timestamp-typed column, the specified interval and the sliding interval. After sorting the data by timestamp, the system calculates the window’s starting time based on the specified interval and the sliding interval. For details, see Group Window Query. |
Spatial Functions
| Function → Returns | Description |
|---|---|
| ST_Distance (a: geometry, b: geometry) → float8 | Return the distance in meters between geography_a and geography_b. |
| ST_DWithin (a: geometry, b: geometry, d: float8) → bool | Return true if any of geography_a is within distance meters of geography_b, inclusive. |
| ST_Contains (a: geometry, b: geometry) → bool | Return true if geometry_b intersects the interior of geometry_a but not the boundary or exterior of geometry_a. |
| ST_Intersects (a: geometry, b: geometry) → bool | Return true if geography_a shares any portion of space with geography_b. |
| ST_Equals (a: geometry, b: geometry) → bool | Return true if geometry_a is spatially equal to geometry_b. |
| ST_Touches (a: geometry, b: geometry) → bool | Return true if the only points in common between geometry_a and geometry_b are on the boundary. |
| ST_Covers (a: geometry, b: geometry) → bool | Return true if no point in geometry_b is outside geometry_a. |
| ST_Area (a: geometry) → float8 | Return the area of the given geography in meters^2. |