> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Math Functions and Operators
export const Ocient = "Ocient®";
## Math Operators
{Ocient} supports addition, subtraction, multiplication, and division between any two numeric types. Division involving two integral types always results in an integral type.
### Decimal Precision
These rules determine the precision `p` and scale `s` for the result of a specified expression that involves decimals `d1` and `d2`, with precisions `p1` and `p2`, and scales `s1` and `s2`:
| **Operation** | **Precision** | **Scale** |
| --------------------- | ----------------------------------------------- | ----------------------- |
| addition, subtraction | max(`s1`, `s2`) + max(`p1`-`s1`, `p2`-`s2`) + 1 | max(`s1`, `s2`) |
| multiplication | `p1` + `p2` | `s1` + `s2` |
| division | *See note.* | max(6, `s1` + `p2` + 1) |
For division, the resulting precision is the minimum necessary to hold the whole number portion of the result. If that portion does not exist because the absolute value of the result is less than one, the resulting precision is one greater than the scale. For other operations, the result might still have a lower precision (but the same scale) than specified by these rules when the result would otherwise have a leading insignificant zero.
If the resulting precision exceeds 31, the database decreases both precision and scale until the precision is 31, except in division, where the minimal scale is always six. If the resulting precision still exceeds 31, the database truncates it to 31. The database returns an overflow error when the result exceeds the available integral portion of the result. When the database decreases the resulting scale, it truncates the excess fractional digits in the result.
### Math Operators for Time Intervals
Ocient supports math operators for time interval values (hour, minute, second, etc.), including when used with time-series data types such as TIME, TIMESTAMP, and DATE. You can use addition, subtraction, multiplication, and division on time intervals like any other numeric value.
Division operations can produce inaccurate results because the system truncates the precision of the result to a whole number. For details, see [Decimal Precision](#decimal-precision).
**Examples**
**Add Interval Value to Timestamp**
This example uses the MINUTES cast function to add six minutes to the TIMESTAMP value returned from the TIMESTAMP cast function of the `'2023-04-05 12:15:05'` string.
```sql SQL theme={null}
SELECT TIMESTAMP('2023-04-05 12:15:05') + (MINUTES(6));
```
*Output*: `2023-04-05 12:21:05.000`
**Add Fractional Interval Value to Timestamp**
This example shows how division can produce an inaccurate result. The division operation in the example must produce a whole number. In other words, the system calculates two minutes as `5 / 2 = 2` instead of two and a half minutes as `5 / 2 = 2.5`.
```sql SQL theme={null}
SELECT TIMESTAMP('2023-04-05 12:15:05') + (MINUTES(5) / 2);
```
*Output*: `2023-04-05 12:17:05.000`
## Math Functions
These scalar math functions operate on a single value and return a single value. They can appear anywhere where an ordinary value or condition can appear.
For a list of common math symbols supported in Ocient, see [Math Operators](#math-operators).
### ABS
Returns the absolute value of a specified floating-point number.
**Syntax**
```sql SQL theme={null}
ABS(x)
```
**Example**
```sql SQL theme={null}
SELECT ABS(-250.5);
```
*Output*: `250.5`
### ACOS
Returns the inverse cosine of a specified floating-point number.
**Syntax**
```sql SQL theme={null}
ACOS(x)
```
**Example**
```sql SQL theme={null}
SELECT ACOS(0.25);
```
*Output*: `1.318116071652818`
### ACOSH
Returns the hyperbolic arc-cosine of a specified floating-point number.
**Syntax**
```sql SQL theme={null}
ACOSH(x)
```
**Example**
```sql SQL theme={null}
SELECT ACOSH(2);
```
*Output*: `1.3169578969248166`
### ASIN
Returns the inverse sine of a specified floating-point number.
**Syntax**
```sql SQL theme={null}
ASIN(x)
```
**Example**
```sql SQL theme={null}
SELECT ASIN(0.25);
```
*Output*: `0.25268025514207865`
### ASINH
Returns the hyperbolic arc-sine of a specified floating-point number.
**Syntax**
```sql SQL theme={null}
ASINH(x)
```
**Example**
```sql SQL theme={null}
SELECT ASINH(2);
```
*Output*: `1.4436354751788103`
### ATAN
Returns the inverse tangent of a specified floating-point number.
**Syntax**
```sql SQL theme={null}
ATAN(x)
```
**Example**
```sql SQL theme={null}
SELECT ATAN(0.25);
```
*Output*: `0.24497866312686414`
### ATAN2
Returns the inverse tangent of two numeric, floating-point values.
**Syntax**
```sql SQL theme={null}
ATAN2(x, y)
```
**Example**
```sql SQL theme={null}
SELECT ATAN2(0.50, 1);
```
*Output*: `0.4636476090008061 `
### ATANH
Returns the hyperbolic arc-tangent of a specified floating-point number.
**Syntax**
```sql SQL theme={null}
ATANH(x)
```
**Example**
```sql SQL theme={null}
SELECT ATANH(0.25);
```
*Output*: `0.25541281188299536 `
### BICDF
The cumulative distribution function of the standard bivariate normal distribution, which returns the probability that a random sample of (A, B) is less than `x` and less than `y` (`A <= x and B <= y`). The correlation is set to `0` in the case where you specify two arguments only.
You can specify an optional `rho` coefficient to calculate the correlation.
For details, see the [bivariate cumulative distribution function](https://en.wikipedia.org/wiki/Multivariate_normal_distribution#Cumulative_distribution_function).
**Syntax**
```sql SQL theme={null}
BICDF(x, y [, rho])
```
**Example**
```sql SQL theme={null}
SELECT BICDF(1,2);
```
*Output*: `0.8222040420815764`
### BIPDF
The probability density function of the standard bivariate normal distribution returns the relative likelihood that a random sample would equal the specified values. The correlation is set to `0` in the case where you specify two arguments only.
You can specify an optional `rho` coefficient to calculate the correlation.
For details, see the [bivariate probability density function](https://en.wikipedia.org/wiki/Multivariate_normal_distribution#Bivariate_case).
**Syntax**
```sql SQL theme={null}
BIPDF(x, y, [, rho])
```
**Example**
```sql SQL theme={null}
SELECT BIPDF(1,2);
```
*Output*: `0.013064233284684893`
### BITFUNC
Performs a variety of bit operations.
The `char_func` can be any of these string literals: `'AND'`, `'OR'`, or `'XOR'`. This argument determines the bitwise operation that is performed on arguments `x` and `y`.
**Syntax**
```sql SQL theme={null}
BITFUNC(char_func, x, y)
```
| **Argument** | **Data** **Type** | **Description** |
| ---------------- | ----------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `character_func` | `CHAR` | This argument determines the bitwise operation performed on arguments `x` and `y`.
Can be any of these string literals: `'AND'`, `'OR'`, or `'XOR'`.
To perform bit operations using a NOT operator, use the [BITNOT](#bitnot) function. |
| `x` | `INT` | A numeric value to be evaluated by the function. |
| `y` | `INT` | A numeric value to be evaluated by the function. |
**Example**
```sql SQL theme={null}
SELECT BITFUNC('AND',11,12);
```
*Output*: `8`
**Example**
```sql SQL theme={null}
SELECT BITFUNC('OR',11,12);
```
*Output*: `15`
**Example**
```sql SQL theme={null}
SELECT BITFUNC('XOR',11,12);
```
*Output*: `7`
### BITAND
Alias for the [BITFUNC](#bitfunc) syntax `BITFUNC('AND', x, y)`.
### BITNOT
Returns the bitwise negation of `integral_x`.
**Syntax**
```sql SQL theme={null}
BITNOT(integral_x)
```
**Example**
```sql SQL theme={null}
SELECT BITNOT(1);
```
*Output*: `-2`
### BITOR
Alias for the [BITFUNC](#bitfunc) syntax `BITFUNC('OR', x, y)`.
### BITXOR
Alias for the [BITFUNC](#bitfunc) syntax `BITFUNC('XOR', x, y)`.
### BOOLFUNC
Performs a Boolean logical evaluation on arguments `x` and `y`. Numbers equal to `0` evaluate to `false`, and numbers not equal to `0` evaluate to `true`.
**Syntax**
```sql SQL theme={null}
BOOLFUNC(char_func, x, y)
```
| **Argument** | **Data** **Type** | **Description** |
| ------------ | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `char_func` | `CHAR` | This argument determines the Boolean operation performed on arguments `x` and `y`.
Can be any of these string literals: `'AND'`, `'OR'`, or `'XOR'`.
To perform operations using a NOT operator, use the [BOOLNOT](#boolnot) function. |
| `x` | `INT` | A numeric value for evaluation by the function. |
| `y` | `INT` | A numeric value for evaluation by the function. |
**Example**
```sql SQL theme={null}
SELECT BOOLFUNC('AND',0,1);
```
*Output*: `false`
**Example**
```sql SQL theme={null}
SELECT BOOLFUNC('OR',0,1);
```
*Output*: `true`
**Example**
```sql SQL theme={null}
SELECT BOOLFUNC('XOR',0,1);
```
*Output*: `true`
### BOOLAND
Alias for the [BOOLFUNC](#boolfunc) syntax `BOOLFUNC('AND', x, y)`.
### BOOLNOT
Returns the logical negation of the [BOOLFUNC](#boolfunc) function. Numbers equal to `0` evaluate to `true`, and numbers not equal to `0` evaluate to `false`.
**Syntax**
```sql SQL theme={null}
BOOLNOT(numeric_x)
```
**Example**
```sql SQL theme={null}
SELECT BOOLNOT(0);
```
*Output*: `true`
### BOOLOR
Alias for the [BOOLFUNC](#boolfunc) syntax `BOOLFUNC('OR', x, y)`.
### BOOLXOR
Alias for the [BOOLFUNC](#boolfunc) syntax `BOOLFUNC('XOR', x, y)`.
### CBRT
Returns the cube root of the numeric value `x`.
**Syntax**
```sql SQL theme={null}
CBRT(x)
```
**Example**
```sql SQL theme={null}
SELECT CBRT(64);
```
*Output*: `4.0`
### CDF
The cumulative distribution function of the standard normal distribution. Returns the probability that a random sample is less than or equal to the specified value.
For details about the cumulative distribution function, see [Cumulative distribution function](https://en.wikipedia.org/wiki/Normal_distribution#Cumulative_distribution_function).
**Syntax**
```sql SQL theme={null}
CDF(x)
```
**Example**
```sql SQL theme={null}
SELECT CDF(1.5);
```
*Output*: `0.9331927987311419`
### CEIL
Returns the nearest integer greater than or equal to `x`.
**Syntax**
```sql SQL theme={null}
CEIL(x)
```
**Example**
```sql SQL theme={null}
SELECT CEIL(23.45);
```
*Output*: `24.0`
### CEILING
Alias for CEIL.
### COS
Returns the cosine of `x`.
**Syntax**
```sql SQL theme={null}
COS(x)
```
**Example**
```sql SQL theme={null}
SELECT COS(2);
```
*Output*: `-0.4161468365471424`
### COSH
Returns the hyperbolic cosine of `x`.
**Syntax**
```sql SQL theme={null}
COSH(x)
```
**Example**
```sql SQL theme={null}
SELECT COSH(2);
```
*Output*: `3.7621956910836314`
### COT
Returns the cotangent of `x`.
**Syntax**
```sql SQL theme={null}
COT(x)
```
**Example**
```sql SQL theme={null}
SELECT COT(2);
```
*Output*: `-0.45765755436028577`
### DEGREES
Returns the corresponding angle in degrees for `x` in radians.
**Syntax**
```sql SQL theme={null}
DEGREES(x)
```
**Example**
```sql SQL theme={null}
SELECT DEGREES(1.5);
```
*Output*: `85.94366926962348`
### DIV
Returns the result of `x` divided by `y`. If `y` is zero, returns NULL.
**Syntax**
```sql SQL theme={null}
DIV(x, y)
```
**Example**
```sql SQL theme={null}
SELECT DIV(45, 9);
```
*Output*: `5`
### ERF
The error function is used for measurements that follow a normal distribution. Use this function to find the probability that the error of a single measurement lies between \[-a, a]. For details about the error function, see [Error function](https://en.wikipedia.org/wiki/Error_function).
**Syntax**
```sql SQL theme={null}
ERF(x)
```
**Example**
```sql SQL theme={null}
SELECT ERF(8);
```
*Output*: `1.0`
### ERFC
The complement of the error function. `ERFC(x) = 1 - ERF(x)`.
**Syntax**
```sql SQL theme={null}
ERFC(x)
```
**Example**
```sql SQL theme={null}
SELECT ERFC(8);
```
*Output*: `1.1224297172982928E-29`
### EXP
Returns the exponential of `x` (e raised to the power of `x`).
**Syntax**
```sql SQL theme={null}
EXP(x)
```
**Example**
```sql SQL theme={null}
SELECT EXP(1);
```
*Output*: `2.718281828459045`
### FLOOR
Returns the nearest integer less than or equal to `x`.
**Syntax**
```sql SQL theme={null}
FLOOR(x)
```
**Example**
```sql SQL theme={null}
SELECT FLOOR(22.75);
```
*Output*: `22.0`
### GAMMA
Compute the gamma function of the specified value. For details about the gamma function, see the [Gamma function](https://en.wikipedia.org/wiki/Gamma_function).
**Syntax**
```sql SQL theme={null}
GAMMA(x)
```
**Example**
```sql SQL theme={null}
SELECT GAMMA(9.0);
```
*Output*: `40319.99999999999`
### HEXBINX
Returns the x-coordinate of the center of the nearest hexagonal bin to the point (`x`, `y`). Hexagonal bins have a side length of 1.
**Syntax**
```sql SQL theme={null}
HEXBINX(x, y)
```
**Example**
```sql SQL theme={null}
SELECT HEXBINX(5, 7);
```
*Output*: `4.5`
### HEXBINY
Returns the y-coordinate of the center of the nearest hexagonal bin to the point (`x`, `y`). Hexagonal bins have a side length of 1.
**Syntax**
```sql SQL theme={null}
HEXBINY(x, y)
```
**Example**
```sql SQL theme={null}
SELECT HEXBINY(5, 7);
```
*Output*: `7.794`
### IERF
The inverse of the ERF error function.
**Syntax**
```sql SQL theme={null}
IERF(x)
```
**Example**
```sql SQL theme={null}
SELECT IERF(0);
```
*Output*: `0.0`
### IERFC
The inverse of the complement of the error function.
**Syntax**
```sql SQL theme={null}
IERFC(x)
```
**Example**
```sql SQL theme={null}
SELECT IERFC(1);
```
*Output*: `0.0`
### LEAKYRELU
Returns the leaky rectified linear unit function of `x`.
If unspecified, the optional `numeric_value_slope` argument defaults to `0.01`.
**Syntax**
```sql SQL theme={null}
LEAKYRELU(x [, numeric_value_slope] )
```
**Example**
```sql SQL theme={null}
SELECT LEAKYRELU(-40);
```
*Output*: `-0.4`
### LEFT\_SHIFT
Returns `x` shifted to the left by `y` bits. If `y` is less than 0, then `x` shifts to the right by the absolute value. The function throws an out-of-range error if you attempt a shift into the sign bit.
**Syntax**
```sql SQL theme={null}
LEFT_SHIFT(x, y)
```
**Example**
```sql SQL theme={null}
SELECT LEFT_SHIFT(1,2);
```
*Output*: `4`
### LN
Returns the natural logarithm of `x` (base e or 2.171828…).
**Syntax**
```sql SQL theme={null}
LN(x)
```
**Example**
```sql SQL theme={null}
SELECT LN(2);
```
*Output*: `0.6931471805599453`
### LOG2
Returns the base 2 logarithm of `x`.
**Syntax**
```sql SQL theme={null}
LOG2(x)
```
**Example**
```sql SQL theme={null}
SELECT LOG2(2);
```
*Output*: `1.0`
### LOG
Returns the base 10 logarithm of `x`.
The optional `base` argument specifies the numeral system to use. If unspecified, the function defaults to base 10.
**Syntax**
```sql SQL theme={null}
LOG([ base,] x)
```
**Example**
```sql SQL theme={null}
SELECT LOG(2);
```
*Output*: `0.3010299956639812`
### LOG\_GAMMA
The natural logarithm of the absolute value of the gamma function.
**Syntax**
```sql SQL theme={null}
LOG_GAMMA(x)
```
**Example**
```sql SQL theme={null}
SELECT LOG_GAMMA(10);
```
*Output*: `12.80182748008147`
### MOD
Returns the remainder from `x` divided by `y`.
**Syntax**
```sql SQL theme={null}
MOD(x, y)
```
**Example**
```sql SQL theme={null}
SELECT MOD(12, 10);
```
*Output*: `2`
### PI
Returns the constant value of π.
**Syntax**
```sql SQL theme={null}
PI()
```
**Example**
```sql SQL theme={null}
SELECT PI();
```
*Output*: `3.141592653589793`
### PMOD
Returns the smallest non-negative equivalence class of `x` % `y`.
**Syntax**
```sql SQL theme={null}
PMOD(x, y)
```
**Example**
```sql SQL theme={null}
SELECT PMOD(12,10);
```
*Output*: `2`
### POWER
Returns `x` raised to the power of `y`. The return type `FLOAT` is returned for a `FLOAT` input, `DECIMAL` for `DOUBLE` input, and `BIGINT` otherwise.
**Syntax**
```sql SQL theme={null}
POWER(x, y)
```
**Example**
```sql SQL theme={null}
SELECT POWER(2, 4);
```
*Output*: `16`
### PROBIT
The inverse of the cumulative distribution function. The function accepts only decimal values between `0` and `1`.
For details about the probit function, see the [Probit model](https://en.wikipedia.org/wiki/Probit_model).
**Syntax**
```sql SQL theme={null}
PROBIT(x)
```
**Example**
```sql SQL theme={null}
SELECT PROBIT(0.4);
```
*Output*: `-0.2533471031357998`
### RADIANS
Returns the corresponding angle in radians for `x` in degrees.
**Syntax**
```sql SQL theme={null}
RADIANS(x)
```
**Example**
```sql SQL theme={null}
SELECT RADIANS(2);
```
*Output*: `0.03490658503988659`
### RAND
Takes no argument and returns a random `DOUBLE` value in the range \[0, 1).
**Syntax**
```sql SQL theme={null}
RAND()
```
**Example**
```sql SQL theme={null}
SELECT RAND();
```
*Output*: `0.6053105024272647`
### RELU
Returns the rectified linear unit function of `x`.
**Syntax**
```sql SQL theme={null}
RELU(x)
```
**Example**
```sql SQL theme={null}
SELECT RELU(-1);
```
*Output*: `0.0`
### RIGHT\_SHIFT
Returns `x` shifted to the right by `y` bits. If `y` is less than 0, then `x` shifts to the left by the absolute value of `y` in bits. The function throws an out-of-range error if you attempt a shift into the sign bit.
**Syntax**
```sql SQL theme={null}
RIGHT_SHIFT(x, y)
```
**Example**
```sql SQL theme={null}
SELECT RIGHT_SHIFT(5,1);
```
*Output*: `2`
### ROUND
Returns `x` rounded to the nearest integer.
**Syntax**
```sql SQL theme={null}
ROUND(x)
```
**Example**
```sql SQL theme={null}
SELECT ROUND(12.49);
```
*Output*: `12.0`
### SIGN
Returns the positive (+1), zero (0), or negative (-1) sign of `x`.
**Syntax**
```sql SQL theme={null}
SIGN(x)
```
**Example**
```sql SQL theme={null}
SELECT SIGN(+12);
```
*Output*: `1`
**Example**
```sql SQL theme={null}
select SIGN(0);
```
*Output*: `0`
**Example**
```sql SQL theme={null}
SELECT SIGN(-12);
```
*Output*: `-1`
### SIN
Returns the sine of `x`.
**Syntax**
```sql SQL theme={null}
SIN(x)
```
**Example**
```sql SQL theme={null}
SELECT SIN(2);
```
*Output*: `0.9092974268256817`
### SINH
Returns the hyperbolic sine of `x`.
**Syntax**
```sql SQL theme={null}
SINH(x)
```
**Example**
```sql SQL theme={null}
SELECT SINH(2);
```
*Output*: `3.626860407847019`
### SQRT
Returns the square root of `x`.
**Syntax**
```sql SQL theme={null}
SQRT(x)
```
**Example**
```sql SQL theme={null}
SELECT SQRT(81);
```
*Output*: `9`
### SQUARE
Returns the square of `x`.
**Syntax**
```sql SQL theme={null}
SQUARE(x)
```
**Example**
```sql SQL theme={null}
SELECT SQUARE(7);
```
*Output*: `49`
### TAN
Returns the tangent of `x`.
**Syntax**
```sql SQL theme={null}
TAN(x)
```
**Example**
```sql SQL theme={null}
SELECT TAN(1.75);
```
*Output*: `-5.52037992250933`
### TANH
Returns the hyperbolic tangent of `x`.
**Syntax**
```sql SQL theme={null}
TANH(x)
```
**Example**
```sql SQL theme={null}
SELECT TANH(1.75);
```
*Output*: `0.9413755384972874`
### TO\_BASE
Converts an integer value to its string representation in a specified base (radix).
**Syntax**
```sql SQL theme={null}
TO_BASE(value, base)
```
| **Argument** | **Data Type** | **Description** |
| ------------ | ----------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `value` | `BYTE`, `SMALLINT`, `INT`, `BIGINT` | The integer value to convert to the specified base. Can be positive or negative. |
| `base` | `BYTE`, `SMALLINT`, `INT`, `BIGINT` | The base (radix) for the conversion. Must be an integer between 2 and 36 (inclusive). For bases greater than 10, lowercase letters `(a-z)` represent digits 10 through 35 (e.g., `a` = 10, `b` = 11, and `z` = 35). |
**Examples**
**Convert to Binary (Base 2)**
This example converts the decimal value `7` to its binary representation.
```sql SQL theme={null}
SELECT TO_BASE(7, 2);
```
*Output*: `111`
**Convert to Octal (Base 8)**
This example converts the decimal value `64` to its octal representation.
```sql SQL theme={null}
SELECT TO_BASE(64, 8);
```
*Output*: `100`
### TRUNC
Returns `x` truncated to `y` decimal places.
**Syntax**
```sql SQL theme={null}
TRUNC(x,y)
```
**Example**
```sql SQL theme={null}
SELECT TRUNC(1.23456,3);
```
*Output*: `1.234`
### TRUNCATE
Alias for TRUNC.
## Related Links
[Array Functions and Operators](/array-functions-and-operators)
[Matrix Functions and Operators](/matrix-functions-and-operators)
[Tuple Functions and Operators](/tuple-functions-and-operators)
[Number Formatting Functions](/formatting-functions#number-formatting-functions)
[Data Types](/data-types)
[Query Ocient](/query-ocient)