Help Center/ Log Tank Service/ User Guide/ Log Search and Analysis (SQL)/ Using SQL Analysis Syntax/ SQL Mathematical Functions
Updated on 2025-07-28 GMT+08:00
View PDF
Share

SQL Mathematical Functions

Description

Mathematical functions are one type of scalar functions. They support only numeric fields and can be used to round values, obtain absolute values, and perform modulo operations. For details, see Table 1.

In mathematical operations, if the operands involved in an expression are all integers, SQL uses integer operations. Otherwise, SQL switches to floating-point operations. You can force the switch by converting one of the operands to float type, and the runtime SQL extends the 32-bit floats in most expressions to 64-bit floats.

Syntax

SELECT ABS(fieldname1) AS fieldname1_abs

Mathematical Function Statements

Table 1 Mathematical function statements

Statement

Description

Example

ABS(expr)

Returns the absolute value.

SELECT ABS(fieldname1)

CEIL(expr)

Rounds up to the nearest integer.

SELECT CEIL(fieldname1)

FLOOR(expr)

Rounds down to the nearest integer.

SELECT FLOOR(fieldname1)

TRUNCATE(expr, digits)

Truncates expr to a specified number of digits. If digits is set to a negative number, places to the left of the decimal point are truncated. If digits is not specified, the default value 0 is used.

SELECT TRUNCATE(fieldname1, 2)

ROUND(expr, digits)

Rounds the value of expr to a specified number of decimal places. The result has the same type as expr. expr can be an integer or a floating point number, while digits must be an integer. If digits is not specified, the default value 0 is used. If digits is a negative number, the value of expr is rounded off. If expr is a non-numeric value, it will be converted to 0. If expr is an infinite number, it is converted to the closest finite number of the double type.

SELECT ROUND(fieldname1, 2)

x + y

Addition.

SELECT fieldname1 + fieldname2

x - y

Subtraction.

SELECT fieldname1 - fieldname2

x * y

Multiplication.

SELECT fieldname1 * fieldname2

x / y

Division.

SELECT fieldname1 / fieldname2

MOD(x, y)

Modulo. It returns a remainder obtained after x is divided by y.

SELECT MOD(fieldname1, fieldname2)

LN(expr)

Logarithm (base: e).

SELECT ln(expr)

LOG10(expr)

Logarithm (base 10).

SELECT LOG10(expr)

POWER(expr,power)

Power of expr.

SELECT POWER(expr ,2)

SQRT(expr)

Square root of expr.

SELECT SQRT(expr)

SIN(expr)

Sine.

SELECT SIN(expr)

COS(expr)

Cosine.

SELECT COS(expr)

TAN(expr)

Tangent.

SELECT TAN(expr)

COT(expr)

Cotangent.

SELECT COT(expr)

ASIN(expr)

Arcsine.

SELECT ASIN(expr)

ACOS(expr)

Arc cosine.

SELECT ACOS(expr)

ATAN(expr)

Inverse tangent.

SELECT ATAN(expr)

Examples

ACOS(expr)

Calculates the arccosine of a parameter value. y = arccosx, where x is within [-1,1].

  1. Example field

    x:0.5

  2. Query and analysis statement 
    select  ACOS(x)
  3. Query and analysis result
    Table 2 Query and analysis result

    x

    EXPR$1

    0.5

    1.0471975511965979

ATAN(expr) function

Calculates the arctangent of a parameter value. y = arctanx, where x is R.

  1. Example field

    x:0.5

  2. Query and analysis statement 
    select  ATAN(X)
  3. Query and analysis result
    Table 3 Query and analysis result

    x

    EXPR$1

    0.5

    1.0471975511965979

ATAN2(expr)

Returns the angle θ from rectangular coordinates (x, y) to polar coordinates (r, θ).

  1. Example field

    x:3; y:4

  2. Query and analysis statement 
    SELECT x , y, ATAN2(x,y)
  3. Query and analysis result
    Table 4 Query and analysis result

    x

    y

    EXPR$0

    3

    4

    0.6435011087932844