Date and Time Functions

DATE, DATETIME and TIMESTAMP in MySQL 8.4, plus DATE_FORMAT, interval arithmetic, DATEDIFF and the time zone rules.

SyntaxDATE_ADD(date, INTERVAL expr unit) -- same as: date + INTERVAL expr unit

Three types, two behaviours

DATE holds '1000-01-01' to '9999-12-31'. DATETIME adds a time part over the same range. TIMESTAMP covers only '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. Both DATETIME and TIMESTAMP take a fractional seconds precision up to 6, as in DATETIME(3).

The real difference is time zones. MySQL converts TIMESTAMP values from the session time_zone to UTC on write and back on read; this does not happen for DATETIME, which stores exactly the wall-clock value you gave it. Named zones such as 'Europe/Helsinki' work only once the time zone tables in the mysql database are populated; offsets like '+00:00' always work.

Arithmetic and formatting

DATE_ADD(date, INTERVAL expr unit) and the operator form date + INTERVAL expr unit are the same thing; so are DATE_SUB() and - INTERVAL. DATEDIFF(expr1, expr2) returns expr1 − expr2 in whole days, using only the date parts. TIMESTAMPDIFF(unit, dt1, dt2) returns dt2 − dt1 in the unit you name.

DATE_FORMAT(date, format) builds a display string: %Y four-digit year, %m month, %d day, %H:%i:%s time, %M month name, %W weekday name. CONVERT_TZ(dt, from_tz, to_tz) shifts a value between zones and returns NULL if any argument is invalid.

Example

Example · sql
-- Subscription health report. created_at is DATETIME holding UTC;
-- the application sets session time_zone = '+00:00' on connect.
SELECT
  s.id,
  DATE_FORMAT(s.created_at, '%Y-%m-%d %H:%i')          AS started_utc,
  -- Display in the customer's zone; needs the mysql time zone tables loaded
  DATE_FORMAT(CONVERT_TZ(s.created_at, '+00:00', c.tz),
              '%W %e %M %Y')                            AS started_local,
  -- Longhand and shorthand for the same renewal date
  DATE_ADD(s.created_at, INTERVAL 1 YEAR)               AS renews_at,
  s.created_at + INTERVAL 1 YEAR                        AS renews_at_same,
  DATEDIFF(CURDATE(), DATE(s.created_at))               AS days_old,
  TIMESTAMPDIFF(MONTH, s.created_at, NOW())             AS months_old
FROM subscriptions s
JOIN customers c ON c.id = s.customer_id
-- Sargable: the column is left bare so an index on created_at can be used
WHERE s.created_at >= NOW() - INTERVAL 90 DAY
ORDER BY s.created_at DESC;

When to use it

  • Bucket rows into months or weeks for a revenue report
  • Find records created in the last 30 days without defeating the index on the timestamp column
  • Compute a renewal or expiry date one year or 14 days out from a stored date
  • Display a stored UTC timestamp in each customer's local time zone

More examples

Month buckets that sort correctly

Group on the zero-padded %Y-%m form, which orders lexically the same way it orders chronologically, and carry the human label along only for display. Grouping on '%M %Y' alone puts April before January in every report.

Example · sql
SELECT
  DATE_FORMAT(paid_at, '%Y-%m')     AS month,   -- '2026-09', sorts as text
  DATE_FORMAT(paid_at, '%M %Y')     AS label,   -- 'September 2026', does not sort
  SUM(amount)                       AS revenue
FROM payments
WHERE paid_at >= '2026-01-01'
GROUP BY DATE_FORMAT(paid_at, '%Y-%m'), DATE_FORMAT(paid_at, '%M %Y')
ORDER BY month;

Keep the WHERE clause indexable

Wrapping a column in DATE() or DATE_FORMAT() forces a full scan because the index is on the raw value. Rewriting as a half-open interval keeps the same result and lets MySQL seek. Use this pattern any time a date predicate appears in a slow query.

Example · sql
-- Not indexable: the function wraps the column
SELECT COUNT(*) FROM events
WHERE DATE(occurred_at) = '2026-09-17';

-- Indexable: a half-open range on the bare column
SELECT COUNT(*) FROM events
WHERE occurred_at >= '2026-09-17'
  AND occurred_at <  '2026-09-17' + INTERVAL 1 DAY;

DATETIME in UTC versus TIMESTAMP

TIMESTAMP is convenient because MySQL handles the conversion, but it stops at 2038-01-19 and its displayed value depends on the session time zone. DATETIME plus an application convention of always writing UTC is the usual choice for long-lived records; pick one and apply it to the whole schema.

Example · sql
CREATE TABLE audit_log (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  -- Converted to UTC on write, back to session time_zone on read; ends in 2038
  seen_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- Stored verbatim, no conversion, range to the year 9999
  recorded_at DATETIME(3) NOT NULL
);

SET time_zone = '+00:00';          -- pin the session before writing
INSERT INTO audit_log (recorded_at) VALUES (UTC_TIMESTAMP(3));

Related

sql data typessql castsql sargablesql group bysql printf

Discussion

  • Be the first to comment on this lesson.