numeric
FLOOR
Returns the largest integer less than or equal to the value — always rounds toward negative infinity. The mirror of CEIL.
FLOOR(number)Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| TRUNC(n) | Postgres: truncate toward zero (different from FLOOR on negatives) |
| ::numeric cast | Cast to numeric or float before dividing to avoid integer truncation |
Examples
SELECT FLOOR(3.9);Returns 3
SELECT FLOOR(-3.1);Returns -4, not -3
SELECT FLOOR(EXTRACT(EPOCH FROM (NOW() - created_at)) / 60) FROM logs;Minutes since creation, discarding sub-minute remainder
SELECT FLOOR(RANDOM() * 100);Postgres: integer in [0, 99]
Dialect notes / Gotcha
Integer division in MySQL/Postgres already truncates toward zero, which is not the same as FLOOR for negative numbers. Cast to numeric/float when in doubt.
Related functions
CEIL / CEILING
Returns the smallest integer greater than or equal to the value — rounding always toward positive infinity. CEIL and CEILING are aliases in every major dialect.
ROUND
Rounds a numeric value to the specified number of decimal places (default 0). Uses banker's rounding (half-to-even) on Postgres numeric; half-away-from-zero on MySQL and float types.
AVG
Computes the arithmetic mean of non-NULL numeric values. NULL rows are excluded from both the sum and the divisor, so AVG is not equivalent to SUM/COUNT(*) when NULLs are present.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet