Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 40 ยท Functions and Window Functions

Numeric and Date Functions in MariaDB

Master MariaDB numeric and date functions: ROUND, FLOOR, MOD, NOW, DATE_ADD, DATEDIFF, TIMESTAMPDIFF and DATE_FORMAT, with portable runnable examples.

Reports are mostly arithmetic and calendars: totals rounded to cents, orders from the last 30 days, ages, invoice due dates, "first day of the month". MariaDB's numeric and date functions let the database do that work next to the data.

This lesson shows the functions in MariaDB syntax, explains the type rules that cause rounding surprises, and gives runnable portable equivalents for the in-browser engine (which uses strftime, date() and julianday() where MariaDB uses DATE_FORMAT, DATE_ADD and DATEDIFF).

Numeric functions

SQL
SELECT ROUND(2.567, 2);      -- 2.57
SELECT ROUND(1234.5, -2);    -- 1200  (negative digits round to the left)
SELECT CEIL(4.1), FLOOR(4.9), TRUNCATE(4.987, 1);  -- 5, 4, 4.9
SELECT ABS(-12), SIGN(-3), MOD(17, 5), 17 % 5, 17 DIV 5;  -- 12, -1, 2, 2, 3
SELECT POWER(2, 10), SQRT(81), LOG10(1000), EXP(0), PI();
SELECT GREATEST(3, 9, 5), LEAST(3, 9, 5);  -- 9, 3
SELECT RAND(), FLOOR(1 + RAND() * 6);      -- a die roll

Things to remember:

  • / always gives a decimal result (7 / 2 is 3.5000); DIV is integer division.
  • ROUND on DECIMAL rounds half away from zero (2.5 becomes 3), but on DOUBLE it follows the C library, so ROUND(2.5) may give 2. Store money in DECIMAL, never in FLOAT: see data types.
  • GREATEST and LEAST return NULL if any argument is NULL.
  • RAND(seed) is deterministic for a fixed seed, so use it when you need repeatable test data.
SQL Rounding and integer division ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

In the portable engine MAX(a, b) with two arguments plays the role of MariaDB's GREATEST (and MIN(a, b) that of LEAST), and / on two integers is an integer division. In MariaDB write salary DIV 12 for that.

Current date and time

SQL
SELECT NOW();            -- 2026-03-15 14:07:31   (statement start)
SELECT CURDATE();        -- 2026-03-15
SELECT CURTIME();        -- 14:07:31
SELECT UTC_TIMESTAMP();  -- always UTC
SELECT SYSDATE();        -- the moment the function runs (changes inside a statement)

NOW() returns the same value everywhere in one statement, which is what you want for consistent inserts. The result depends on the session time_zone. A TIMESTAMP column stores UTC and converts to the session zone on read; a DATETIME column stores exactly what you give it. For new applications many teams store UTC in DATETIME or TIMESTAMP and convert in the user interface.

Date arithmetic

SQL
SELECT DATE_ADD('2026-01-31', INTERVAL 1 MONTH);   -- 2026-02-28 (clamped to month end)
SELECT '2026-03-15' - INTERVAL 7 DAY;              -- 2026-03-08
SELECT DATEDIFF('2026-12-25', '2026-03-15');       -- 285 (days; time ignored)
SELECT TIMESTAMPDIFF(YEAR, '1990-06-15', CURDATE());   -- age in whole years
SELECT TIMESTAMPDIFF(MINUTE, '2026-03-15 09:00', '2026-03-15 10:45'); -- 105
SELECT LAST_DAY('2026-02-10');                     -- 2026-02-28

DATE_ADD and DATE_SUB accept the units MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER and YEAR. TIMESTAMPDIFF(unit, earlier, later) counts complete units, which is why it is the right tool for ages.

Portable runnable version, using the company data set:

SQL Tenure and due dates ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Extracting parts and formatting

SQL
SELECT YEAR('2026-03-15'), MONTH('2026-03-15'), DAY('2026-03-15');  -- 2026, 3, 15
SELECT DAYOFWEEK('2026-03-15');   -- 1 = Sunday ... 7 = Saturday
SELECT WEEKDAY('2026-03-15');     -- 0 = Monday ... 6 = Sunday
SELECT EXTRACT(QUARTER FROM '2026-03-15');          -- 1
SELECT DATE_FORMAT('2026-03-15 14:07', '%W, %d %M %Y %H:%i');  -- Sunday, 15 March 2026 14:07
SELECT DATE_FORMAT(NOW(), '%Y-%m');                 -- 2026-03
SELECT STR_TO_DATE('15/03/2026', '%d/%m/%Y');       -- 2026-03-15

Format specifiers: %Y four-digit year, %y two-digit, %m month number, %M month name, %d day, %H 24-hour, %i minutes (not %m!), %s seconds, %W weekday name. STR_TO_DATE is the reverse of DATE_FORMAT and is the standard way to import dates that are not in ISO form.

Group by month for a classic monthly report:

SQL Hires per year and month ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Writing sargable date filters

An index on ordered_on helps this query:

SQL
SELECT * FROM orders
WHERE ordered_on >= '2026-03-01' AND ordered_on < '2026-04-01';

but not this one, because the column is wrapped in a function so MariaDB must evaluate it for every row:

SQL
SELECT * FROM orders WHERE MONTH(ordered_on) = 3 AND YEAR(ordered_on) = 2026;

Use a half-open range (>= start, < next start) instead of BETWEEN for date-times, so a value like 2026-03-31 18:30 is not lost.

Common mistakes

  • Comparing a DATETIME with a plain date using =: '2026-03-15' means midnight, so noon rows do not match. Use DATE(col) = ... only on small tables, or a range.
  • Expecting DATEDIFF to look at the time part. It uses only the date parts.
  • Using %m for minutes in DATE_FORMAT.
  • Storing dates as VARCHAR: no validation, no date functions without conversion, and wrong sorting for non-ISO formats.
  • Ignoring the year 2038 limit of TIMESTAMP on older versions (extended in MariaDB 11.5+ to 2106); use DATETIME for far-future dates.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does 17 DIV 5 return in MariaDB?

    DIV is integer division and discards the remainder; 17 % 5 (or MOD) gives the remainder 2.
  2. 2. Which function returns the age in whole years between a birth date and today?

    TIMESTAMPDIFF counts complete units. DATEDIFF returns days.
  3. 3. Which DATE_FORMAT specifier prints minutes?

    %i is minutes; %m is the month number and %M the month name.
  4. 4. Why is WHERE MONTH(ordered_on) = 3 a poor filter on a large table?

    Compare the bare column to a range instead, for example ordered_on >= '2026-03-01' AND ordered_on < '2026-04-01'.

Summary

  • ROUND, FLOOR, CEIL, MOD, DIV and POWER cover numeric work; keep money in DECIMAL.
  • NOW(), DATE_ADD, DATEDIFF, TIMESTAMPDIFF, DATE_FORMAT and STR_TO_DATE cover most date work.
  • Filter dates with half-open ranges on the bare column so indexes stay usable.

Next: Aggregate Functions and GROUP BY