MariaDB FLOOR() vs TRUNCATE()

Posted on by Ian

MariaDB has a FLOOR() function and a TRUNCATE() function that can return the same results or different results, depending on the exact value of their arguments.

Below is a quick rundown on the difference between FLOOR() and TRUNCATE() in MariaDB.

The Difference

Here’s the difference between each function in a nutshell:

  • FLOOR() returns the largest integer value not greater than its argument.
  • TRUNCATE() truncates its argument to a specified number of decimal places.

Syntax

First, here are the syntaxes of each function.

FLOOR()

The syntax for FLOOR() goes like this:

FLOOR(X)

It returns the largest integer value not greater than X.

TRUNCATE()

The syntax for TRUNCATE() goes like this:

TRUNCATE(X,D)

TRUNCATE() returns the number X, truncated to D decimal places.

Example

Here’s a comparison to demonstrate the difference between FLOOR() and TRUNCATE():

SELECT 
    FLOOR(-3.6789),
    TRUNCATE(-3.6789, 0);

Result:

+----------------+----------------------+
| FLOOR(-3.6789) | TRUNCATE(-3.6789, 0) |
+----------------+----------------------+
|             -4 |                   -3 |
+----------------+----------------------+

In this case, the number is a negative value and the result from each function is different.

  • FLOOR() returned the largest integer (-4) value not greater than its argument.
  • The TRUNCATE() function on the other hand, simply truncated the number at the specified decimal place.

Same Result

Both functions can sometimes return the same result. It all depends on the value of the arguments being passed.

If we change the numbers to positive values, both functions return the same result:

SELECT 
    FLOOR(3.6789),
    TRUNCATE(3.6789, 0);

Result:

+---------------+---------------------+
| FLOOR(3.6789) | TRUNCATE(3.6789, 0) |
+---------------+---------------------+
|             3 |                   3 |
+---------------+---------------------+

The Second Argument

Another obvious difference between the two functions is that TRUNCATE() accepts/requires a second argument. This can result in a result that contains a fractional part.

FLOOR() however, only returns an integer, so no fractional part is ever returned.

Example

SELECT 
    FLOOR(3.6789),
    TRUNCATE(3.6789, 2);

Result:

+---------------+---------------------+
| FLOOR(3.6789) | TRUNCATE(3.6789, 2) |
+---------------+---------------------+
|             3 |                3.67 |
+---------------+---------------------+

The ROUND() Function

Both functions are different to the ROUND() function, which rounds its argument to a specified number of decimal places.