DBMS Mathematical Functions

This table lists the trigonometrical and mathematical functions available in the proposed Astronomical Data Query Language (ADQL) and compares them with the corresponding functions of JDBC, and in the SQL dialects of several major DBMS. This analysis is based on the draft of V0.7 of ADQL as downloadable from http://skyservice.pha.jhu.edu/develop/vo/adql/.

ADQL	JDBC	DB2	MySQL	Oracle	Postgres	SQL Server	Sybase
abs	abs	abs	abs	abs	abs	abs	abs
acos	acos	acos	acos	acos	acos	acos	acos
asin	asin	asin	asin	asin	asin	asin	asin
atan	atan	atan	atan	atan	atan	atan	atan
atan2	atan2	atan2	atan2	atan2	atan2	atn2	atn2
ceiling	ceiling	ceiling	ceiling	ceil	ceil	ceiling	ceiling
cos	cos	cos	cos	cos	cos	cos	cos
cot	cot	cot	cot	-	cot	cot	cot
degrees	degrees	degrees	degrees	-	degrees	degrees	degrees
exp	exp	exp	exp	exp	exp	exp	exp
floor	floor	floor	floor	floor	floor	floor	floor
log	log	ln	log	ln	ln	log	log
log10	log10	log	log10	log	log	log10	log10
mod	mod	mod	mod	mod	mod	-	-
pi	pi	-	pi	-	pi	pi	pi
power	power	power	power	power	pow	power	power
radians	radians	radians	radians	-	radians	radians	radians
sin	sin	sin	sin	sin	sin	sin	sin
sqrt	sqrt	sqrt	sqrt	sqrt	sqrt	sqrt	sqrt
tan	tan	tan	tan	tan	tan	tan	tan
rand	rand	rand	rand	-	random	rand	rand
round	round	round	round	round	round	round	round
truncate	truncate	truncate	truncate	trunc	trunc	-	-

Note that the numerical functions supported by ADQL now coincide with those of JDBC as listed in section 1 of appendix C of the JDBC-3.0 specification, which can be found linked from http://java.sun.com/products/jdbc/index.jsp

If anyone notices any mistakes, or can tell me where to get corresponding information for other DBMS, I will be happy to update this table.

I am told that JDBC was based on Microsoft's ODBC, but I have been unable to find a reference to the mathematical functions which it supports.

-- ClivePage - 15 Jan 2004

-- ClivePage - 15 March 2005 - Removed reference to rand from Oracle as it does not seem to exist. Thanks to Troels Arvin for pointing this out.