Floor, Ceiling and Rounding with MySQL

I was trying to get the average solution time of issues in Redmine per tracker type. In order to get the average time, I first selected creation time of issue and extracted it from the date of solution of the issue (this doesn’t mean it is closed, it is to say status is updated with the id =X),  after that I listed the average of each day.

ROUND(AVG(DATEDIFF(journals.created_on,issues.created_on))) AverageSolutionTimeRounded,

Some mathematical functions of MySQL could be a useful. Here they are:

CEIL() and CEILING()

They both are the same function.
Returns the smallest integer value not less than our number.
SELECT CEILING (2.56) or SELECT CEIL(2,5)
-> 3

SELECT CEILING (-2.56);
-> -2

FLOOR()

Floor() returns the largest integer value not greater then our number
SELECT FLOOR(2.56)
->2
SELECT FLOOR(-2.56)
->-3

ROUND()

Round() can be used by providing one argument only or with number of decimal places. Here how it works:
SELECT ROUND(2.56)
->3
SELECT ROUND(2.49)
->2
SELECT ROUND(2.56987,3);
->2,570
SELECT ROUND(2.56987,4);
->2,5699
SELECT ROUND(2.56987,0);
->3

This is all.
Resource: http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html

Advertisements

#ceiling, #floor, #mysql, #rounding