Functions
You can use various types of functions in your SQL queries to perform operations on your data.
Math and Trigonometry
Node supports the math functions shown in the following table of math functions plus trigonometry functions listed at the end of this section. Most math functions and all trig functions take these input types:
- INTEGER
- BIGINT
- FLOAT
- DOUBLE
- SMALLINT
- DECIMAL
Table of Math Functions
| Function | Return Type | Description |
|---|---|---|
| ABS(x) | Same as input | Returns the absolute value of the input argument x. |
| CBRT(x) | FLOAT8 | Returns the cubic root of x. |
| CEIL(x) | Same as input | Returns the smallest integer not less than x. |
| CEILING(x) | Same as input | Same as CEIL. |
| DEGREES(x) | FLOAT8 | Converts x radians to degrees. |
| E() | FLOAT8 | Returns 2.718281828459045. |
| EXP(x) | FLOAT8 | Returns e (Euler's number) to the power of x. |
| FLOOR(x) | Same as input | Returns the largest integer not greater than x. |
| LOG(x) | FLOAT8 | Returns the natural log (base e) of x. |
| LOG(x, y) | FLOAT8 | Returns log base x to the y power. |
| LOG10(x) | FLOAT8 | Returns the common log of x. |
| LSHIFT(x, y) | Same as input | Shifts the binary x by y times to the left. |
| MOD(x, y) | FLOAT8 | Returns the remainder of x divided by y. |
| NEGATIVE(x) | Same as input | Returns x as a negative number. |
| PI | FLOAT8 | Returns pi. |
| POW(x, y) | FLOAT8 | Returns the value of x to the y power. |
| RADIANS(x) | FLOAT8 | Converts x degrees to radians. |
| RAND | FLOAT8 | Returns a random number from 0-1. |
| ROUND(x) | Same as input | Rounds to the nearest integer. |
| ROUND(x, y) | DECIMAL | Rounds x to y decimal places. |
| RSHIFT(x, y) | Same as input | Shifts the binary x by y times to the right. |
| SIGN(x) | INT | Returns the sign of x. |
| SQRT(x) | Same as input | Returns the square root of x. |
| TRUNC(x, y) | DOUBLE | Truncates x to y decimal places. Specifying y is optional. Default is 0. |
Trigonometry Functions
Node supports the following trigonometryfunctions, which return a FLOAT8 result.
- SIN(x)
Sine of angle x in radians - COS(x)
Cosine of angle x in radians - TAN(x)
Tangent of angle x in radians - ASIN(x)
Inverse sine of angle x in radians - ACOS(x)
Inverse cosine of angle x in radians - ATAN(x)
Inverse tangent of angle x in radians - SINH()
Hyperbolic sine of hyperbolic angle x in radians - COSH()
Hyperbolic cosine of hyperbolic angle x in radians - TANH()
Hyperbolic tangent of hyperbolic angle x in radians
Date/Time Functions and Arithmetic
In addition to the TO_DATE, TO_TIME, and TO_TIMESTAMP functions, Node supports a number of other date/time functions and arithmetic operators for use with dates, times, and intervals. Node supports time functions based on the Gregorian calendar and in the range 1971 to 2037.
| Function | Return Type |
|---|---|
| AGE(TIMESTAMP) | INTERVALDAY or INTERVALYEAR |
| EXTRACT(field from time_expression) | DOUBLE |
| CURRENT_DATE | DATE |
| CURRENT_TIME | TIME |
| CURRENT_TIMESTAMP | TIMESTAMP |
| DATE_ADD | DATE, TIMESTAMP |
| DATE_DIFF | DATE, TIMESTAMP |
| DATE_PART | DOUBLE |
| DATE_SUB | DATE, TIMESTAMP |
| LOCALTIME | TIME |
| LOCALTIMESTAMP | TIMESTAMP |
| NOW | TIMESTAMP |
| TIMEOFDAY | VARCHAR |
| UNIX_TIMESTAMP | BIGINT |
| NEARESTDATE | TIMESTAMP |
| TIMESTAMPADD | Inferred based on unit of time |
| TIMESTAMPDIFF | Inferred based on unit of time |
- NEARESTDATE
This function rounds the TIMESTAMP object passed to it to the nearest unit also passed to the function. For example SELECT * NEARESTDATE(COLUMN,'MONTH') will return all of the TIMESTAMPs in a given column rounded to the nearest month.
String Manipulation
You can use the following string functions in SQL queries:
| Function | Return Type |
|---|---|
| BYTE_SUBSTR | BINARY or VARCHAR |
| CHAR_LENGTH | INTEGER |
| CON | VARCHAR |
| CONTAINS | VARCHAR |
| ILIKE | BOOLEAN |
| INITCAP | VARCHAR |
| LENGTH | INTEGER |
| LIKE | BOOLEAN |
| LOWER | VARCHAR |
| LPAD | VARCHAR |
| LTRIM | VARCHAR |
| MASK | VARCHAR |
| POSITION | INTEGER |
| REGEXP_MATCHES | BOOLEAN |
| REGEXP_REPLACE | VARCHAR |
| REPEAT | VARCHAR |
| REPLACE | VARCHAR |
| REVERSE | VARCHAR |
| RPAD | VARCHAR |
| RTRIM | VARCHAR |
| SPLIT | INTEGER |
| SPLIT_PART | VARCHAR |
| STRPOS | INTEGER |
| SUBSTR | VARCHAR |
| TOASCII | VARCHAR |
| TRIM | VARCHAR |
| UPPER | VARCHAR |