What is the cleanest method to find the ciel and floor of a number in SQLite? Unfortunately SQLite only has ROUND() function.
asked Jan 28, 2019 at 10:27
Add a comment
|
3 Answers
Formulas
Ceil : cast ( x as int ) + ( x > cast ( x as int ))
Take integer part of x and add 1 if decimal value is greater than 0
Floor : cast ( x as int ) - ( x < cast ( x as int ))
Take integer part of x and subtract 1 if decimal value is less than 0
Examples
Ceil :
SELECT (cast ( amount as int ) + ( amount > cast ( amount as int ))) AS amount
FROM SALES WHERE id = 128;
Floor :
SELECT (cast ( amount as int ) - ( amount < cast ( amount as int ))) AS amount
FROM SALES WHERE id = 128;
I have checked all the corner cases including negative number with MySQL ceil() and floor() functions.
answered Jan 28, 2019 at 10:27
-
One thing to look out for is, that the number ("amount" or "x") must be of type REAL. I had a month from a database and divided it by 3 to get the quarter. Division with 2 integer operands results in an integer in SQLite. So when the month is 2, it evaluates to
0 + (0 > 0), so it returns 0 instead of 1.– Alien426Commented Dec 22, 2021 at 8:57
You can use ROUND() to the effect of CEIL and FLOOR if you add or subtract 0.5 from the number on hand. I like this more, because it's can be more readable.
Expanding on Anees' example :
Ceil : SELECT ROUND(amount+0.5, 0) AS amount FROM SALES WHERE id = 128;
Floor : SELECT ROUND(amount-0.5, 0) AS amount FROM SALES WHERE id = 128;
Thanks Anees for the comment below, I didn't think of that corner case either. His solution is more robust.
-
2Wow! I didn't think of it. But, note that It won't work always. Take for example,
CEIL(1) = 1whereROUND(1+0.5, 0) = 2. It was better though, if the value would not be an integer. Commented Jan 31, 2019 at 15:35 -
2Ceil and floor with real numbers ≥ 0, try 0.4999999999 instead of 0.5. Commented Sep 5, 2020 at 9:37
See https://www.sqlite.org/lang_mathfunc.html#ceil
Apparently SQLite does provide ceiling:
ceil(X)
ceiling(X)
Return the first representable integer value greater than or equal to X. For positive values of X, this routine rounds away from zero. For negative values of X, this routine rounds toward zero.
But - sadly, it is not compiled by default - and is only active if the amalgamation is compiled using the -DSQLITE_ENABLE_MATH_FUNCTIONS compile-time option.
