SQLite Numeric Functions: ROUND, ABS, CEIL, FLOOR, and Math

SQLite Has More Math Than You'd Expect

SQLite is famously minimal, but it ships with a full set of numeric functions: rounding, absolute value, ceiling and floor, powers, roots, logs, trig, random numbers. Most of the math functions were added in SQLite 3.35 (2021), so any reasonably modern install — the one bundled with Python, Node, your browser's WebSQL ancestor, or the official CLI — has them ready to go.

Here's a quick sampler before we dig in:

SELECT
    ROUND(3.14159, 2)   AS rounded,
    ABS(-42)            AS absolute,
    CEIL(2.1)           AS ceiling,
    FLOOR(2.9)          AS floor_val,
    POWER(2, 10)        AS two_to_ten,
    SQRT(144)           AS root;

Six functions, one row of results. The rest of this page walks through what each family is for and the gotchas worth knowing.

ROUND: The One You'll Use Most

ROUND(value, digits) rounds to a given number of decimal places. The second argument is optional — leave it off and you get rounding to the nearest integer (but still as a floating-point value):

SELECT
    ROUND(3.14159)       AS no_digits,
    ROUND(3.14159, 2)    AS two_places,
    ROUND(3.14159, 4)    AS four_places,
    ROUND(2.5)           AS half_up,
    ROUND(-2.5)          AS half_down;

A few things to notice:

• ROUND(3.14159) returns 3.0, not 3. If you want an integer, use CAST(ROUND(x) AS INTEGER) or just CAST(x AS INTEGER) for truncation.
• SQLite uses "round half away from zero" — 2.5 rounds to 3, -2.5 rounds to -3. Some databases use banker's rounding (round half to even); SQLite does not.
• The digits argument can be negative: ROUND(1234.5, -2) rounds to the nearest 100, giving 1200.

In practice you'll write ROUND(price, 2) for money displays more than anything else.

ROUND vs CAST: They're Different

People reach for CAST(x AS INTEGER) when they mean to round, and get bitten:

SELECT
    CAST(2.9 AS INTEGER)   AS cast_truncates,
    ROUND(2.9)             AS round_rounds,
    CAST(-2.9 AS INTEGER)  AS cast_negative,
    ROUND(-2.9)            AS round_negative;

CAST truncates toward zero — it just throws the fractional part away. ROUND rounds to the nearest integer. For 2.9 they differ by a whole unit. Pick the one whose behavior you actually want.

ABS, SIGN, and the Sign of a Number

ABS(x) returns the absolute value. SIGN(x) returns -1, 0, or 1 depending on the sign:

SELECT
    ABS(-17)    AS abs_neg,
    ABS(17)     AS abs_pos,
    ABS(0)      AS abs_zero,
    SIGN(-17)   AS sign_neg,
    SIGN(0)     AS sign_zero,
    SIGN(42.5)  AS sign_pos;

ABS is the workhorse — handy for "how far apart are these two values" queries. SIGN is less common, but useful when you want to bucket rows by direction (debit vs credit, gain vs loss) without an explicit CASE.

CEIL, FLOOR, and TRUNC

These give you integer-ish values without rounding to the nearest. CEIL always goes up, FLOOR always goes down, TRUNC always goes toward zero:

SELECT
    CEIL(2.1)    AS ceil_pos,
    CEIL(-2.1)   AS ceil_neg,
    FLOOR(2.9)   AS floor_pos,
    FLOOR(-2.9)  AS floor_neg,
    TRUNC(2.9)   AS trunc_pos,
    TRUNC(-2.9)  AS trunc_neg;

Watch the negative cases. FLOOR(-2.9) is -3 (further from zero), but TRUNC(-2.9) is -2 (toward zero). When dealing with negative numbers, FLOOR and TRUNC disagree, and choosing the wrong one is a classic off-by-one bug.

CEILING is an alias for CEIL. Use whichever spelling reads better to you.

Integer Division Is the Real Gotcha

This isn't a function — it's the / operator — but it trips up newcomers more than any of the actual math functions:

SELECT
    5 / 2           AS int_div,
    5 / 2.0         AS real_div,
    5.0 / 2         AS also_real,
    CAST(5 AS REAL) / 2  AS explicit_cast;

When both sides are integers, SQLite does integer division and truncates. The moment one side is a REAL, the whole expression becomes real. The fix is to make sure at least one operand is a float — either by writing 2.0 instead of 2, or by casting.

This bites hardest with column references: total_cents / 100 returns an integer. total_cents / 100.0 returns the dollar amount you actually wanted.

MOD and the % Operator

MOD(x, y) returns the remainder of x / y. The % operator does the same thing:

SELECT
    MOD(17, 5)   AS mod_func,
    17 % 5       AS mod_op,
    MOD(-17, 5)  AS mod_neg,
    MOD(17, 0)   AS mod_zero;

MOD(17, 5) and 17 % 5 both return 2. Modulo by zero returns NULL in SQLite — it doesn't raise an error, which is unusual compared to most languages. If you care about that, check the divisor first or wrap the call in CASE WHEN y = 0 THEN ... END.

The function and operator forms are interchangeable. Most people reach for % because it's shorter.

POWER, SQRT, EXP, LOG

For exponents and roots:

SELECT
    POWER(2, 10)    AS two_to_the_ten,
    POW(2, 0.5)     AS sqrt_via_power,
    SQRT(2)         AS sqrt_two,
    EXP(1)          AS e,
    LOG(100)        AS log10_100,
    LN(EXP(1))      AS ln_of_e;

A few notes that catch people:

• POW is an alias for POWER.
• LOG(x) in SQLite is base-10. LN(x) is natural log. LOG(b, x) with two arguments is log base b. (This differs from many languages where log is the natural log — the SQL convention won.)
• SQRT of a negative number returns NULL, not an error.
• POWER(0, 0) returns 1 by convention.

These are useful for compounding interest, normalizing to decibels, computing distances — anywhere geometric or exponential math shows up.

RANDOM and RANDOMBLOB

RANDOM() returns a signed 64-bit integer, anywhere in its full range:

SELECT
    RANDOM()                          AS raw_random,
    ABS(RANDOM()) % 100               AS zero_to_99,
    ABS(RANDOM()) % 100 + 1           AS one_to_100,
    ABS(RANDOM() / 9223372036854775807.0) AS unit_interval;

To get a number in a range, wrap with ABS (since RANDOM() is signed) and use %. To get a real number between 0 and 1, divide by the maximum 64-bit integer. SQLite doesn't have a built-in RAND() that returns 0–1 — you build it yourself.

RANDOMBLOB(n) returns n bytes of random data, useful for generating session tokens or test fixtures. Combine with HEX() for a printable string:

SELECT HEX(RANDOMBLOB(8)) AS token;

Each call produces a fresh value. Don't expect RANDOM() to return the same number twice in the same row — even within one expression, each invocation is independent.

Putting It Together

A small worked example: compute distances and round prices for a product table.

CREATE TABLE products (id INTEGER, name TEXT, price_cents INTEGER, weight_g REAL);
INSERT INTO products VALUES
    (1, 'Mug',     1299, 350.5),
    (2, 'Notebook', 850, 180.2),
    (3, 'Pen',      199,  12.0);

SELECT
    name,
    ROUND(price_cents / 100.0, 2)  AS price_dollars,
    CEIL(weight_g / 100)           AS weight_buckets_100g,
    POWER(weight_g, 0.5)           AS sqrt_weight
FROM products;

The price_cents / 100.0 is the key bit — that .0 turns the division real, then ROUND formats it to two decimal places. Without it, 1299 / 100 would give you 12, not 12.99.

Next: Date and Time

Numeric functions handle the math. Dates and times need their own toolkit — SQLite stores them as text, real, or integer, and gives you a small but capable set of functions for parsing, formatting, and arithmetic on them. That's what's coming up next.