Mathematical functions and operators

Mathematical functions and operators dinther Thu, 01/23/2020 - 14:48

These apply to MonetDB numeric SQL types: tinyint, smallint, int, bigint, hugeint, decimal, double, float and real.
Some of these functions and operators also work on SQL types: interval month and interval second.

Mathematical operators

Operator Description Example Result
+ addition 2 + 3 5
- subtraction 2 - 3 -1
* multiplication 2 * 3 6
/ division (integer division truncates the result) 5 / 2 2
/ division (on decimals or floating point) 5.0 / 2 2.5
% modulo (remainder) 5 % 4 1
& bitwise AND 91 & 15 11
| bitwise OR 32 | 3 35
^ bitwise XOR 17 ^ 5 20
~ bitwise NOT ~1 -2
<< bitwise shift left 1 << 4 16
>> bitwise shift right 8 >> 2 2


 


 


 


 


 


 

 

Mathematical functions

Function Return type Description Example Result
abs(x) same as input absolute value of a number abs(-17.4) 17.4
bit_and(x, y) int or decimal bitwise AND bit_and(91, 15) 11
bit_not(x) int or decimal bitwise NOT bit_not(1) -2
bit_or(x, y) int or decimal bitwise OR bit_or(32, 3) 35
bit_xor(x, y) int or decimal bitwise XOR bit_xor(17, 5) 20
cbrt(x) double cube root cbrt(2.0) 1.2599210498948734
ceil(x) double nearest integer greater than or equal to argument
(same as ceiling(x))
ceil(-42.8) -42
ceiling(x) double nearest integer greater than or equal to argument
(same as ceil(x))
ceiling(-95.3) -95
exp(x) double exponential exp(1) 2.718281828459045
floor(x) real or double nearest integer less than or equal to argument floor(-42.8) -43
get_value_for(schema_nm, seq_nm) bigint get current value of a sequence get_value_for('dwh2', 'seq_9201') 1234
greatest(x, y) same as input greatest (highest) value of x and y
(same as sql_max(x,y))
greatest(1.2, 5) 5
least(x, y) same as input least (lowest) value of x and y
(same as sql_min(x,y))
least(1.2, 5) 1.2
left_shift(x, i) same as first arg bitwise shift left i bits left_shift(1, 4) 16
ln(x) double natural logarithm ln(2.0) 0.6931471805599453
log(x) double natural logarithm, same as ln(x) log(2.0) 0.6931471805599453
log(b, x) double logarithm to base b.
Note: before Oct2020 release (11.39.5) the arguments were switched, so log(x, b).
log(2, 64.0) 6.0
log10(x) double base 10 logarithm log10(100.0) 2.0
log2(x) double base 2 logarithm log2(64.0) 6.0
mod(x, y) same as input modulo (remainder) mod(5.0, 2.1) 0.8
next_value_for(schema_nm, seq_nm) bigint get current value of a sequence and increment it next_value_for('dwh2', 'seq_9201') 1234
power(x, y) double x raised to the power of y power(2, 5) 32
rand() int random integer value between 0 and 2147483648 rand() 917632440
rand(seed) int random integer value with seed set rand(-5) 1345532277
right_shift(x, i) same as first arg bitwise shift right i bits right_shift(16, 2) 4
round(x, d) same as first arg round x to d decimal places round(42.4382, 2) 42.44
scale_down(x, y) same as first arg ??? divide x by y and round to integer scale_down(100, 3) 33
scale_up(x, y) same as first arg ??? multiply x by y scale_up(13, 3) 39
sign(x) tinyint sign of the argument: -1 when x is negative, 0 when x is zero, 1 when x is positive sign(-8.4) -1
sql_add(x, y) same as input x plus y sql_add(2, 3.4) 5.4
sql_div(x, y) same as input x divided by y sql_div(3.4, 2) 1.7
sql_max(x, y) same as input greatest (highest) value of x and y
(same as greatest(x,y))
sql_max(1.2, sql_max(3.3, 5)) 5
sql_min(x, y) same as input least (lowest) value of x and y
(same as least(x,y))
sql_min(1.2, sql_min(3.3, 5)) 1.2
sql_mul(x, y) same as input x multplied by y sql_mul(1.2, 5) 6.0
sql_neg(x) same as input x times -1 sql_neg(-2.5) 2.5
sql_sub(x, y) same as input subtract y from x sql_sub(5, 7) -2
sqrt(x) double square root sqrt(2.0) 1.4142135623730951
sys.alpha(pdec double, pradius double) double compute alpha 'expansion' of theta for a given declination (used by SkyServer) sys.alpha(5.0, 1.2) 1.2045844792437546
sys.fuse(smallint, smallint) integer combine the bits of two integers into a larger integer type sys.fuse(2, 6) 518
sys.ms_round(x double, prc int, trunc int) double round to prc decimal places and ?? truncate to trunc decimal places sys.ms_round(1.2359, 2, 0) 1.24
sys.ms_trunc(num double, prc int) double truncate to prc decimal places sys.ms_trunc(1.2359, 2) 1.23

 

Trigonometric and Hyperbolic functions

All these functions (except pi()) take argument(s) of type double.

Function Return type Description Example Result
acos(x) double arc cosine of angle x in radians acos(0.54) 1.0003592173949747
asin(x) double arc sine of angle x in radians asin(1.0) 1.5707963267948966
atan(x) double arc tangent of angle x in radians atan(1.0) 0.7853981633974483
atan(x, y) double arc tangent of angle x/y in radians atan(1.0, 2.0) 0.4636476090008061
cos(x) double cosine of angle x in radians cos(12.2) 0.9336336440746373
cosh(x) double hyperbolic cosine of angle x in radians cosh(3.0) 10.067661995777765
cot(x) double cotangent of angle x in radians cot(16.0) 3.326323195635449
degrees(rad) double convert radians angle value to degrees: r*180/pi()
Note: before Oct2020 release (11.39.5) you need to include the sys schema prefix, so sys.degrees(rad).
degrees(0.5) 28.64788975654116
pi() double π constant pi() 3.141592653589793
radians(degr) double convert degrees angle value to radians: d*pi()/180
Note: before Oct2020 release (11.39.5) you need to include the sys schema prefix, so sys.radians(degr).
radians(45) 0.7853981633974483
sin(x) double sine of angle x in radians sin(1.4) 0.9854497299884601
sinh(x) double hyperbolic sine of angle x in radians sinh(1.4) 1.9043015014515339
tan(x) double tangent of angle x in radians tan(1.4) 5.797883715482887
tanh(x) double hyperbolic tangent of angle x in radians tanh(1.4) 0.8853516482022625