MariaDB Tutorial / Calculate Date Intervals, Differences, and Display Formats

DATE_ADD adds an interval, DATEDIFF and TIMESTAMPDIFF calculate different kinds of differences, and DATE_FORMAT produces display text. Distinguish calendar-day differences from elapsed units, and use a start-inclusive, next-boundary-exclusive range when selecting a complete period.

Define the unit and meaning before choosing a function

A one-month expiry, the count of calendar boundaries crossed and the number of complete elapsed hours are different questions. Use date types for calculations instead of round-tripping through locale-dependent display strings. Verify stored types and time zones using Store Dates and Times with DATE, DATETIME, and TIMESTAMP; Build and Reconcile Monthly Sales, Customer Totals, and Unsold Products applies matching month boundaries to a sales report.

Check the MariaDB documentation for the syntax and conditions used here: date_add, date_format, datediff.

Compare fixed inputs in the same connection

Select shopcourse and inspect SELECT VERSION(), DATABASE(), @@SESSION.time_zone;. Fixed dates make expected results independent of the day the example is run. The DATE, DATETIME and TIMESTAMP guide explains storage and time-zone conversion; write date literals in unambiguous year-month-day order.

SELECT DATE_ADD('2026-01-31',INTERVAL 1 MONTH);
SELECT DATE_ADD('2026-01-31',INTERVAL 30 DAY);

One month after January 31 is 2026-02-28, while thirty days later is 2026-03-02. A calendar month is not always thirty days. When the target month lacks the original day number, the calculation adjusts to its last valid day. Verify that this matches a subscription’s intended month-end policy.

Compare argument order and ignored time components

SELECT DATEDIFF('2026-02-02 01:00:00','2026-02-01 23:00:00'),
 TIMESTAMPDIFF(HOUR,'2026-02-01 23:00:00','2026-02-02 01:00:00');

The results are one calendar day and two complete hours. DATEDIFF ignores the time portions and subtracts the second date from the first. TIMESTAMPDIFF takes the unit first, then the starting value and ending value. Copying argument order mechanically between the functions can reverse the sign.

An integer-unit difference may omit remaining minutes or seconds. For time-zone or daylight-saving-sensitive durations, define whether stored values are local wall-clock fields or normalized instants. Two DATETIME values do not automatically encode every region’s actual elapsed time.

Use DATE_FORMAT for output rather than storage changes

SELECT DATE_FORMAT('2026-02-05 12:34:56','%Y-%m'),
 DATE_FORMAT('2026-02-05 12:34:56','%Y-%m-%d %H:%i:%s');

The output strings are 2026-02 and 2026-02-05 12:34:56. The minute token is %i; %m denotes the month. Formatting leaves the stored date unchanged and returns a string for display. Formats containing localized month names can depend on session language settings.

Select the entire month with an exclusive upper boundary

Check that dates_21_lab does not already exist, then run this complete setup and query in shopcourse. It does not depend on earlier product tables.

CREATE TABLE dates_21_lab (event_id INT PRIMARY KEY, occurred_at DATETIME NOT NULL)
 ENGINE=InnoDB;
INSERT INTO dates_21_lab VALUES
 (1,'2026-02-01 00:00:00'),(2,'2026-02-28 23:59:59'),(3,'2026-03-01 00:00:00');
SELECT event_id FROM dates_21_lab
WHERE occurred_at >= '2026-02-01' AND occurred_at < '2026-03-01'
ORDER BY event_id;

IDs 1 and 2 qualify; ID 3 at exactly March 1 midnight does not. A boundary at the next month’s start remains clear when fractional-second precision changes, whereas hard-coding the previous day’s final second can miss finer timestamps. Direct range comparisons also make the intended access condition explicit when considering indexes.

Check invalid dates and repeat the small example

For NULL results or warnings, inspect the format, month end, leap-year rules and SQL mode. Do not silently treat incomplete or zero dates as valid business dates because conversion happened to accept them. Decide what missing input means before substituting the current date.

Inspect the table and preserve anything needed before removing only this exercise’s dates_21_lab. DROP TABLE cannot be reversed with an ordinary ROLLBACK.

DROP TABLE shopcourse.dates_21_lab;