> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://ocient.ferndocs.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://ocient.ferndocs.com/_mcp/server.

# Math Functions and Operators

> Access math functions and operators in Ocient for comprehensive numerical analysis and calculations.

## 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) |

> **Note**
>
> 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.

> **Note**
>
> 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
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
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
ABS(x)
```

**Example**

```sql
SELECT ABS(-250.5);
```

*Output*: `250.5`

### ACOS

Returns the inverse cosine of a specified floating-point number.

**Syntax**

```sql
ACOS(x)
```

**Example**

```sql
SELECT ACOS(0.25);
```

*Output*: `1.318116071652818`

### ACOSH

Returns the hyperbolic arc-cosine of a specified floating-point number.

**Syntax**

```sql
ACOSH(x)
```

**Example**

```sql
SELECT ACOSH(2);
```

*Output*: `1.3169578969248166`

### ASIN

Returns the inverse sine of a specified floating-point number.

**Syntax**

```sql
ASIN(x)
```

**Example**

```sql
SELECT ASIN(0.25);
```

*Output*: `0.25268025514207865`

### ASINH

Returns the hyperbolic arc-sine of a specified floating-point number.

**Syntax**

```sql
ASINH(x)
```

**Example**

```sql
SELECT ASINH(2);
```

*Output*: `1.4436354751788103`

### ATAN

Returns the inverse tangent of a specified floating-point number.

**Syntax**

```sql
ATAN(x)
```

**Example**

```sql
SELECT ATAN(0.25);
```

*Output*: `0.24497866312686414`

### ATAN2

Returns the inverse tangent of two numeric, floating-point values.

**Syntax**

```sql
ATAN2(x, y)
```

**Example**

```sql
SELECT ATAN2(0.50, 1);
```

*Output*: `0.4636476090008061 `

### ATANH

Returns the hyperbolic arc-tangent of a specified floating-point number.

**Syntax**

```sql
ATANH(x)
```

**Example**

```sql
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
BICDF(x, y [, rho])
```

**Example**

```sql
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
BIPDF(x, y, [, rho])
```

**Example**

```sql
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
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
SELECT BITFUNC('AND',11,12);
```

*Output*: `8`

**Example**

```sql
SELECT BITFUNC('OR',11,12);
```

*Output*: `15`

**Example**

```sql
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
BITNOT(integral_x)
```

**Example**

```sql
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
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
SELECT BOOLFUNC('AND',0,1);
```

*Output*: `false`

**Example**

```sql
SELECT BOOLFUNC('OR',0,1);
```

*Output*: `true`

**Example**

```sql
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
BOOLNOT(numeric_x)
```

**Example**

```sql
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
CBRT(x)
```

**Example**

```sql
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
CDF(x)
```

**Example**

```sql
SELECT CDF(1.5);
```

*Output*: `0.9331927987311419`

### CEIL

Returns the nearest integer greater than or equal to `x`.

**Syntax**

```sql
CEIL(x)
```

**Example**

```sql
SELECT CEIL(23.45);
```

*Output*: `24.0`

### CEILING

Alias for CEIL.

### COS

Returns the cosine of `x`.

**Syntax**

```sql
COS(x)
```

**Example**

```sql
SELECT COS(2);
```

*Output*: `-0.4161468365471424`

### COSH

Returns the hyperbolic cosine of `x`.

**Syntax**

```sql
COSH(x)
```

**Example**

```sql
SELECT COSH(2);
```

*Output*: `3.7621956910836314`

### COT

Returns the cotangent of `x`.

**Syntax**

```sql
COT(x)
```

**Example**

```sql
SELECT COT(2);
```

*Output*: `-0.45765755436028577`

### DEGREES

Returns the corresponding angle in degrees for `x` in radians.

**Syntax**

```sql
DEGREES(x)
```

**Example**

```sql
SELECT DEGREES(1.5);
```

*Output*: `85.94366926962348`

### DIV

Returns the result of `x` divided by `y`. If `y` is zero, returns NULL.

**Syntax**

```sql
DIV(x, y)
```

**Example**

```sql
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
ERF(x)
```

**Example**

```sql
SELECT ERF(8);
```

*Output*: `1.0`

### ERFC

The complement of the error function. `ERFC(x) = 1 - ERF(x)`.

**Syntax**

```sql
ERFC(x)
```

**Example**

```sql
SELECT ERFC(8);
```

*Output*: `1.1224297172982928E-29`

### EXP

Returns the exponential of `x` (e raised to the power of `x`).

**Syntax**

```sql
EXP(x)
```

**Example**

```sql
SELECT EXP(1);
```

*Output*: `2.718281828459045`

### FLOOR

Returns the nearest integer less than or equal to `x`.

**Syntax**

```sql
FLOOR(x)
```

**Example**

```sql
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
GAMMA(x)
```

**Example**

```sql
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
HEXBINX(x, y)
```

**Example**

```sql
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
HEXBINY(x, y)
```

**Example**

```sql
SELECT HEXBINY(5, 7);
```

*Output*: `7.794`

### IERF

The inverse of the ERF error function.

**Syntax**

```sql
IERF(x)
```

**Example**

```sql
SELECT IERF(0);
```

*Output*: `0.0`

### IERFC

The inverse of the complement of the error function.

**Syntax**

```sql
IERFC(x)
```

**Example**

```sql
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
LEAKYRELU(x [, numeric_value_slope] )
```

**Example**

```sql
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
LEFT_SHIFT(x, y)
```

**Example**

```sql
SELECT LEFT_SHIFT(1,2);
```

*Output*: `4`

### LN

Returns the natural logarithm of `x` (base e or 2.171828…).

**Syntax**

```sql
LN(x)
```

**Example**

```sql
SELECT LN(2);
```

*Output*:  `0.6931471805599453`

### LOG2

Returns the base 2 logarithm of `x`.

**Syntax**

```sql
LOG2(x)
```

**Example**

```sql
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
LOG([ base,] x)
```

**Example**

```sql
SELECT LOG(2);
```

*Output*: `0.3010299956639812`

### LOG_GAMMA

The natural logarithm of the absolute value of the gamma function.

**Syntax**

```sql
LOG_GAMMA(x)
```

**Example**

```sql
SELECT LOG_GAMMA(10);
```

*Output*: `12.80182748008147`

### MOD

Returns the remainder from `x` divided by `y`.

**Syntax**

```sql
MOD(x, y)
```

**Example**

```sql
SELECT MOD(12, 10);
```

*Output*: `2`

### PI

Returns the constant value of π.

**Syntax**

```sql
PI()
```

**Example**

```sql
SELECT PI();
```

*Output*: `3.141592653589793`

### PMOD

Returns the smallest non-negative equivalence class of `x` % `y`.

**Syntax**

```sql
PMOD(x, y)
```

**Example**

```sql
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
POWER(x, y)
```

**Example**

```sql
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
PROBIT(x)
```

**Example**

```sql
SELECT PROBIT(0.4);
```

*Output*: `-0.2533471031357998`

### RADIANS

Returns the corresponding angle in radians for `x` in degrees.

**Syntax**

```sql
RADIANS(x)
```

**Example**

```sql
SELECT RADIANS(2);
```

*Output*: `0.03490658503988659`

### RAND

Takes no argument and returns a random `DOUBLE` value in the range \[0, 1).

**Syntax**

```sql
RAND()
```

**Example**

```sql
SELECT RAND();
```

*Output*: `0.6053105024272647`

### RELU

Returns the rectified linear unit function of `x`.

**Syntax**

```sql
RELU(x)
```

**Example**

```sql
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
RIGHT_SHIFT(x, y)
```

**Example**

```sql
SELECT RIGHT_SHIFT(5,1);
```

*Output*: `2`

### ROUND

Returns `x` rounded to the nearest integer.

**Syntax**

```sql
ROUND(x)
```

**Example**

```sql
SELECT ROUND(12.49);
```

*Output*: `12.0`

### SIGN

Returns the positive (+1), zero (0), or negative (-1) sign of `x`.

**Syntax**

```sql
SIGN(x)
```

**Example**

```sql
SELECT SIGN(+12);
```

*Output*: `1`

**Example**

```sql
select SIGN(0);
```

*Output*: `0`

**Example**

```sql
SELECT SIGN(-12);
```

*Output*: `-1`

### SIN

Returns the sine of `x`.

**Syntax**

```sql
SIN(x)
```

**Example**

```sql
SELECT SIN(2);
```

*Output*: `0.9092974268256817`

### SINH

Returns the hyperbolic sine of `x`.

**Syntax**

```sql
SINH(x)
```

**Example**

```sql
SELECT SINH(2);
```

*Output*: `3.626860407847019`

### SQRT

Returns the square root of `x`.

**Syntax**

```sql
SQRT(x)
```

**Example**

```sql
SELECT SQRT(81);
```

*Output*: `9`

### SQUARE

Returns the square of `x`.

**Syntax**

```sql
SQUARE(x)
```

**Example**

```sql
SELECT SQUARE(7);
```

*Output*: `49`

### TAN

Returns the tangent of `x`.

**Syntax**

```sql
TAN(x)
```

**Example**

```sql
SELECT TAN(1.75);
```

*Output*: `-5.52037992250933`

### TANH

Returns the hyperbolic tangent of `x`.

**Syntax**

```sql
TANH(x)
```

**Example**

```sql
SELECT TANH(1.75);
```

*Output*: `0.9413755384972874`

### TO_BASE

Converts an integer value to its string representation in a specified base (radix).

**Syntax**

```sql
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
SELECT TO_BASE(7, 2);
```

*Output*: `111`

**Convert to Octal (Base 8)**

This example converts the decimal value `64` to its octal representation.

```sql
SELECT TO_BASE(64, 8);
```

*Output*: `100`

### TRUNC

Returns `x` truncated to `y` decimal places.

**Syntax**

```sql
TRUNC(x,y)
```

**Example**

```sql
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**](/guides/sql-reference/matrix-functions-and-operators)

\[**Tuple Functions and Operators**]\(Tuple Functions and Operators)

[**Number Formatting Functions**](/guides/sql-reference/formatting-functions)

[**Data Types**](/guides/sql-reference/data-types)

[**Query Ocient**](/guides/query-ocient)