Mathematics Functions

Mathematical functions and operators

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.

+addition2 + 35
-subtraction2 - 3-1
*multiplication2 * 36
/division (integer division truncates the result)5 / 22
/division (on decimals or floating point)5.0 / 22.5
%modulo (remainder)5 % 41
&bitwise AND91 & 1511
|bitwise OR32 | 335
^bitwise XOR17 ^ 520
~bitwise NOT~1-2
<<bitwise shift left1 << 416
>>bitwise shift right8 >> 22

Mathematical functions

FunctionReturn typeDescriptionExampleResult
abs(x)same as inputabsolute value of a numberabs(-17.4)17.4
bit_and(x, y)int or decimalbitwise ANDbit_and(91, 15)11
bit_not(x)int or decimalbitwise NOTbit_not(1)-2
bit_or(x, y)int or decimalbitwise ORbit_or(32, 3)35
bit_xor(x, y)int or decimalbitwise XORbit_xor(17, 5)20
cbrt(x)doublecube rootcbrt(2.0)1.2599210498948734
ceil(x)doublenearest integer greater than or equal to argument (same as ceiling(x))ceil(-42.8)-42
ceiling(x)doublenearest integer greater than or equal to argument (same as ceil(x))ceiling(-95.3)-95
floor(x)real or doublenearest integer less than or equal to argumentfloor(-42.8)-43
get_value_for(schema_nm, seq_nm)bigintget current value of a sequenceget_value_for('dwh2', 'seq_9201')1234
greatest(x, y)same as inputgreatest (highest) value of x and y (same as sql_max(x,y))greatest(1.2, 5)5
least(x, y)same as inputleast (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 argbitwise shift left i bitsleft_shift(1, 4)16
ln(x)doublenatural logarithmln(2.0)0.6931471805599453
log(x)doublenatural logarithm, same as ln(x)log(2.0)0.6931471805599453
log(b, x)doublelogarithm 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)doublebase 10 logarithmlog10(100.0)2.0
log2(x)doublebase 2 logarithmlog2(64.0)6.0
mod(x, y)same as inputmodulo (remainder)mod(5.0, 2.1)0.8
next_value_for(schema_nm, seq_nm)bigintget current value of a sequence and increment itnext_value_for('dwh2', 'seq_9201')1234
power(x, y)doublex raised to the power of ypower(2, 5)32
rand()intrandom integer value between 0 and 2147483648rand()917632440
rand(seed)intrandom integer value with seed setrand(-5)1345532277
right_shift(x, i)same as first argbitwise shift right i bitsright_shift(16, 2)4
round(x, d)same as first arground x to d decimal placesround(42.4382, 2)42.44
scale_down(x, y)same as first arg??? divide x by y and round to integerscale_down(100, 3)33
scale_up(x, y)same as first arg??? multiply x by yscale_up(13, 3)39
sign(x)tinyintsign of the argument: -1 when x is negative, 0 when x is zero, 1 when x is positivesign(-8.4)-1
sql_add(x, y)same as inputx plus ysql_add(2, 3.4)5.4
sql_div(x, y)same as inputx divided by ysql_div(3.4, 2)1.7
sql_max(x, y)same as inputgreatest (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 inputleast (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 inputx multplied by ysql_mul(1.2, 5)6.0
sql_neg(x)same as inputx times -1sql_neg(-2.5)2.5
sql_sub(x, y)same as inputsubtract y from xsql_sub(5, 7)-2
sqrt(x)doublesquare rootsqrt(2.0)1.4142135623730951
sys.alpha(pdec double, pradius double)doublecompute alpha 'expansion' of theta for a given declination (used by SkyServer)sys.alpha(5.0, 1.2)1.2045844792437546
sys.ms_round(x double, prc int, trunc int)doubleround to prc decimal places and ?? truncate to trunc decimal placessys.ms_round(1.2359, 2, 0)1.24
sys.ms_trunc(num double, prc int)doubletruncate to prc decimal placessys.ms_trunc(1.2359, 2)1.23

Trigonometric and Hyperbolic functions

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

FunctionReturn typeDescriptionExampleResult
acos(x)doublearc cosine of angle x in radiansacos(0.54)1.0003592173949747
asin(x)doublearc sine of angle x in radiansasin(1.0)1.5707963267948966
atan(x)doublearc tangent of angle x in radiansatan(1.0)0.7853981633974483
atan(x, y)doublearc tangent of angle x/y in radiansatan(1.0, 2.0)0.4636476090008061
cos(x)doublecosine of angle x in radianscos(12.2)0.9336336440746373
cosh(x)doublehyperbolic cosine of angle x in radianscosh(3.0)10.067661995777765
cot(x)doublecotangent of angle x in radianscot(16.0)3.326323195635449
degrees(rad)doubleconvert 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π constantpi()3.141592653589793
radians(degr)doubleconvert 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)doublesine of angle x in radianssin(1.4)0.9854497299884601
sinh(x)doublehyperbolic sine of angle x in radianssinh(1.4)1.9043015014515339
tan(x)doubletangent of angle x in radianstan(1.4)5.797883715482887
tanh(x)doublehyperbolic tangent of angle x in radianstanh(1.4)0.8853516482022625