SQLite floor() Function

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

Summary: in this tutorial, you will learn how to use the SQLite floor() function to return the first integer less than or equal to the input number.

Introduction to SQLite floor() function #

The floor() function rounds a number to the first integer less than or equal to the input number.

Here’s the syntax of the floor() function:

floor(x)Copy

In this syntax:

• x is the number that you want to round. x can be a number, an expression, or a table column.

If x is positive, the function rounds it toward zero. If x is negative, the function rounds it away from zero.

The floor() function returns NULL if the input number (x) is NULL.

SQLite floor() function examples #

Let’s explore some examples of using the floor() function.

1) Using the floor() function with a positive number #

The following example uses the floor() function to round a positive number away from zero:

SELECT floor(2.6) result;Copy

Output:

result
------
2.0Copy

2) Using the floor() function with a negative number #

The following statement uses the floor() function to round a negative number toward zero:

SELECT floor(-2.6) result;Copy

Output:

result
------
-3.0Copy

3) Using the floor() function with table data #

We’ll use the tracks table from the sample database for the demonstration.

The following statement uses the floor() function to round the average length of tracks in minutes:

SELECT
  floor(avg(milliseconds / 60000)) average_length
FROM
  tracks;Copy

Output:

average_length
--------------
6.0Copy

Summary #

• Use the floor() function to return the first integer less than or equal to the input number.