8.2. Mathematical Functions

8.2.1. ABS()

Absolute value

Result typeNumerical, matching input type

Syntax 

  |ABS (number)

Table 8.3. ABS Function Parameter
ParameterDescription

number

An expression of a numeric type

8.2.2. ACOS()

Arc cosine

Result typeDOUBLE PRECISION

Syntax 

  |ACOS (number)

Table 8.4. ACOS Function Parameter
ParameterDescription

number

An expression of a numeric type within the range [-1, 1]

  • The result is an angle in the range [0, pi].

See alsoSection 8.2.10, “COS()”, Section 8.2.4, “ASIN()”, Section 8.2.6, “ATAN()”

8.2.3. ACOSH()

Inverse hyperbolic cosine

Result typeDOUBLE PRECISION

Syntax 

  |ACOSH (number)

Table 8.5. ACOSH Function Parameter
ParameterDescription

number

Any non-NULL value in the range [1, INF].

The result is in the range [0, INF].

See alsoSection 8.2.11, “COSH()”, Section 8.2.5, “ASINH()”, Section 8.2.8, “ATANH()”

8.2.4. ASIN()

Arc sine

Result typeDOUBLE PRECISION

Syntax 

  |ASIN (number)

Table 8.6. ASIN Function Parameter
ParameterDescription

number

An expression of a numeric type within the range [-1, 1]

The result is an angle in the range [-pi/2, pi/2].

See alsoSection 8.2.24, “SIN()”, Section 8.2.2, “ACOS()”, Section 8.2.6, “ATAN()”

8.2.5. ASINH()

Inverse hyperbolic sine

Result typeDOUBLE PRECISION

Syntax 

  |ASINH (number)

Table 8.7. ASINH Function Parameter
ParameterDescription

number

Any non-NULL value in the range [-INF, INF].

The result is in the range [-INF, INF].

See alsoSection 8.2.25, “SINH()”, Section 8.2.3, “ACOSH()”, Section 8.2.8, “ATANH()”

8.2.6. ATAN()

Arc tangent

Result typeDOUBLE PRECISION

Syntax 

  |ATAN (number)

Table 8.8. ATAN Function Parameter
ParameterDescription

number

An expression of a numeric type

The result is an angle in the range <-pi/2, pi/2>.

See alsoSection 8.2.7, “ATAN2()”, Section 8.2.27, “TAN()”, Section 8.2.2, “ACOS()”, Section 8.2.4, “ASIN()”

8.2.7. ATAN2()

Two-argument arc tangent

Result typeDOUBLE PRECISION

Syntax 

  |ATAN2 (y, x)

Table 8.9. ATAN2 Function Parameters
ParameterDescription

y

An expression of a numeric type

x

An expression of a numeric type

Returns the angle whose sine-to-cosine ratio is given by the two arguments, and whose sine and cosine signs correspond to the signs of the arguments. This allows results across the entire circle, including the angles -pi/2 and pi/2.

  • The result is an angle in the range [-pi, pi].

  • If x is negative, the result is pi if y is 0, and -pi if y is -0.

  • If both y and x are 0, the result is meaningless. An error will be raised if both arguments are 0.

  • A fully equivalent description of this function is the following: ATAN2(y, x) is the angle between the positive X-axis and the line from the origin to the point (x, y). This also makes it obvious that ATAN2(0, 0) is undefined.

  • If x is greater than 0, ATAN2(y, x) is the same as ATAN(y/x).

  • If both sine and cosine of the angle are already known, ATAN2(sin, cos) gives the angle.

8.2.8. ATANH()

Inverse hyperbolic tangent

Result typeDOUBLE PRECISION

Syntax 

  |ATANH (number)

Table 8.10. ATANH Function Parameter
ParameterDescription

number

Any non-NULL value in the range <-1, 1>.

The result is a number in the range [-INF, INF].

See alsoSection 8.2.28, “TANH()”, Section 8.2.3, “ACOSH()”, Section 8.2.5, “ASINH()”

8.2.9. CEIL(), CEILING()

Ceiling of a number

Result typeBIGINT or INT128 for exact numeric number, or DOUBLE PRECISION or DECFLOAT for floating point number

Syntax 

  |CEIL[ING] (number)

Table 8.11. CEIL[ING] Function Parameters
ParameterDescription

number

An expression of a numeric type

Returns the smallest whole number greater than or equal to the argument.

See alsoSection 8.2.14, “FLOOR()”, Section 8.2.22, “ROUND()”, Section 8.2.29, “TRUNC()”

8.2.10. COS()

Cosine

Result typeDOUBLE PRECISION

Syntax 

  |COS (angle)

Table 8.12. COS Function Parameter
ParameterDescription

angle

An angle in radians

The result is in the range [-1, 1].

See alsoSection 8.2.2, “ACOS()”, Section 8.2.12, “COT()”, Section 8.2.24, “SIN()”, Section 8.2.27, “TAN()”

8.2.11. COSH()

Hyperbolic cosine

Result typeDOUBLE PRECISION

Syntax 

  |COSH (number)

Table 8.13. COSH Function Parameter
ParameterDescription

number

A number of a numeric type

The result is in the range [1, INF].

See alsoSection 8.2.3, “ACOSH()”, Section 8.2.25, “SINH()”, Section 8.2.28, “TANH()”

8.2.12. COT()

Cotangent

Result typeDOUBLE PRECISION

Syntax 

  |COT (angle)

Table 8.14. COT Function Parameter
ParameterDescription

angle

An angle in radians

See alsoSection 8.2.10, “COS()”, Section 8.2.24, “SIN()”, Section 8.2.27, “TAN()”

8.2.13. EXP()

Natural exponent

Result typeDOUBLE PRECISION

Syntax 

  |EXP (number)

Table 8.15. EXP Function Parameter
ParameterDescription

number

A number of a numeric type

Returns the natural exponential, enumber

See alsoSection 8.2.15, “LN()”

8.2.14. FLOOR()

Floor of a number

Result typeBIGINT or INT128 for exact numeric number, or DOUBLE PRECISION or DECFLOAT for floating point number

Syntax 

  |FLOOR (number)

Table 8.16. FLOOR Function Parameter
ParameterDescription

number

An expression of a numeric type

Returns the largest whole number smaller than or equal to the argument.

See alsoSection 8.2.9, “CEIL(), CEILING()”, Section 8.2.22, “ROUND()”, Section 8.2.29, “TRUNC()”

8.2.15. LN()

Natural logarithm

Result typeDOUBLE PRECISION

Syntax 

  |LN (number)

Table 8.17. LN Function Parameter
ParameterDescription

number

An expression of a numeric type

An error is raised if the argument is negative or 0.

See alsoSection 8.2.13, “EXP()”, Section 8.2.16, “LOG()”, Section 8.2.17, “LOG10()”

8.2.16. LOG()

Logarithm with variable base

Result typeDOUBLE PRECISION

Syntax 

  |LOG (x, y)

Table 8.18. LOG Function Parameters
ParameterDescription

x

Base. An expression of a numeric type

y

An expression of a numeric type

Returns the x-based logarithm of y.

  • If either argument is 0 or below, an error is raised.

  • If both arguments are 1, NaN is returned.

  • If x = 1 and y < 1, -INF is returned.

  • If x = 1 and y > 1, INF is returned.

See alsoSection 8.2.20, “POWER()”, Section 8.2.15, “LN()”, Section 8.2.17, “LOG10()”

8.2.17. LOG10()

Decimal (base-10) logarithm

Result typeDOUBLE PRECISION

Syntax 

  |LOG10 (number)

Table 8.19. LOG10 Function Parameter
ParameterDescription

number

An expression of a numeric type

An error is raised if the argument is negative or 0.

See alsoSection 8.2.20, “POWER()”, Section 8.2.15, “LN()”, Section 8.2.16, “LOG()”

8.2.18. MOD()

Remainder

Result typeSMALLINT, INTEGER or BIGINT depending on the type of a. If a is a floating-point type, the result is a BIGINT.

Syntax 

  |MOD (a, b)

Table 8.20. MOD Function Parameters
ParameterDescription

a

An expression of a numeric type

b

An expression of a numeric type

Returns the remainder of an integer division.

  • Non-integer arguments are rounded before the division takes place. So, mod(7.5, 2.5) gives 2 (mod(8, 3)), not 0.

  • Do not confuse MOD() with the mathematical modulus operator; e.g. mathematically, -21 mod 4 is 3, while Firebird’s MOD(-21, 4) is -1. In other words, MOD() behaves as % in languages like C and Java.

8.2.19. PI()

Approximation of pi.

Result typeDOUBLE PRECISION

Syntax 

  |PI ()

8.2.20. POWER()

Power

Result typeDOUBLE PRECISION

Syntax 

  |POWER (x, y)

Table 8.21. POWER Function Parameters
ParameterDescription

x

An expression of a numeric type

y

An expression of a numeric type

Returns x to the power of y (xy).

See alsoSection 8.2.13, “EXP()”, Section 8.2.16, “LOG()”, Section 8.2.17, “LOG10()”, Section 8.2.26, “SQRT()”

8.2.21. RAND()

Generates a random number

Result typeDOUBLE PRECISION

Syntax 

  |RAND ()

Returns a random number between 0 and 1.

8.2.22. ROUND()

Result typesingle argument: integer type, DOUBLE PRECISION or DECFLOAT; two arguments: numerical, matching first argument

Syntax 

  |ROUND (number [, scale])

Table 8.22. ROUND Function Parameters
ParameterDescription

number

An expression of a numeric type

scale

An integer specifying the number of decimal places toward which rounding is to be performed, e.g.:

  •  2 for rounding to the nearest multiple of 0.01

  •  1 for rounding to the nearest multiple of 0.1

  •  0 for rounding to the nearest whole number

  • -1 for rounding to the nearest multiple of 10

  • -2 for rounding to the nearest multiple of 100

Rounds a number to the nearest integer. If the fractional part is exactly 0.5, rounding is upward for positive numbers and downward for negative numbers. With the optional scale argument, the number can be rounded to powers-of-ten multiples (tens, hundreds, tenths, hundredths, etc.).

☝
Important

If you are used to the behaviour of the external function ROUND, please notice that the internal function always rounds halves away from zero, i.e. downward for negative numbers.

8.2.22.1. ROUND Examples

If the scale argument is present, the result usually has the same scale as the first argument:

  |ROUND(123.654, 1) -- returns 123.700 (not 123.7)
  |ROUND(8341.7, -3) -- returns 8000.0 (not 8000)
  |ROUND(45.1212, 0) -- returns 45.0000 (not 45)

Otherwise, the result scale is 0:

  |ROUND(45.1212) -- returns 45

See alsoSection 8.2.9, “CEIL(), CEILING()”, Section 8.2.14, “FLOOR()”, Section 8.2.29, “TRUNC()”

8.2.23. SIGN()

Sign or signum

Result typeSMALLINT

Syntax 

  |SIGN (number)

Table 8.23. SIGN Function Parameter
ParameterDescription

number

An expression of a numeric type

Returns the sign of the argument: -1, 0 or 1

  • number < 0 → -1

  • number = 0 → 0

  • number > 0 → 1

8.2.24. SIN()

Sine

Result typeDOUBLE PRECISION

Syntax 

  |SIN (angle)

Table 8.24. SIN Function Parameter
ParameterDescription

angle

An angle, in radians

The result is in the range [-1, 1].

See alsoSection 8.2.4, “ASIN()”, Section 8.2.10, “COS()”, Section 8.2.12, “COT()”, Section 8.2.27, “TAN()”

8.2.25. SINH()

Hyperbolic sine

Result typeDOUBLE PRECISION

Syntax 

  |SINH (number)

Table 8.25. SINH Function Parameter
ParameterDescription

number

An expression of a numeric type

See alsoSection 8.2.5, “ASINH()”, Section 8.2.11, “COSH()”, Section 8.2.28, “TANH()”

8.2.26. SQRT()

Square root

Result typeDOUBLE PRECISION

Syntax 

  |SQRT (number)

Table 8.26. SQRT Function Parameter
ParameterDescription

number

An expression of a numeric type

If number is negative, an error is raised.

See alsoSection 8.2.20, “POWER()”

8.2.27. TAN()

Tangent

Result typeDOUBLE PRECISION

Syntax 

  |TAN (angle)

Table 8.27. TAN Function Parameter
ParameterDescription

angle

An angle, in radians

See alsoSection 8.2.6, “ATAN()”, Section 8.2.7, “ATAN2()”, Section 8.2.10, “COS()”, Section 8.2.12, “COT()”, Section 8.2.24, “SIN()”, Section 8.2.27, “TAN()”

8.2.28. TANH()

Hyperbolic tangent

Result typeDOUBLE PRECISION

Syntax 

  |TANH (number)

Table 8.28. TANH Function Parameters
ParameterDescription

number

An expression of a numeric type

Due to rounding, the result is in the range [-1, 1] (mathematically, it’s <-1, 1>).

See alsoSection 8.2.8, “ATANH()”, Section 8.2.11, “COSH()”, Section 8.2.28, “TANH()”

8.2.29. TRUNC()

Truncate number

Result typesingle argument: integer type, DOUBLE PRECISION or DECFLOAT; two arguments: numerical, matching first argument

Syntax 

  |TRUNC (number [, scale])

Table 8.29. TRUNC Function Parameters
ParameterDescription

number

An expression of a numeric type

scale

An integer specifying the number of decimal places toward which truncating is to be performed, e.g.:

  •  2 for truncating to the nearest multiple of 0.01

  •  1 for truncating to the nearest multiple of 0.1

  •  0 for truncating to the nearest whole number

  • -1 for truncating to the nearest multiple of 10

  • -2 for truncating to the nearest multiple of 100

The single argument variant returns the integer part of a number. With the optional scale argument, the number can be truncated to powers-of-ten multiples (tens, hundreds, tenths, hundredths, etc.).

  • If the scale argument is present, the result usually has the same scale as the first argument, e.g.

    • TRUNC(789.2225, 2) returns 789.2200 (not 789.22)

    • TRUNC(345.4, -2) returns 300.0 (not 300)

    • TRUNC(-163.41, 0) returns -163.00 (not -163)

  • Otherwise, the result scale is 0:

    • TRUNC(-163.41) returns -163

☝
Important

If you are used to the behaviour of the external function TRUNCATE, please notice that the internal function TRUNC always truncates toward zero, i.e. upward for negative numbers.

See alsoSection 8.2.9, “CEIL(), CEILING()”, Section 8.2.14, “FLOOR()”, Section 8.2.22, “ROUND()”