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
This entry was posted in MySQL, Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s