How to Format a MySQL DATETIME in a SELECT

The function for formatting a MySQL DATETIME in a SELECT is DATE_FORMAT(value, format_string), and it has been the same on every version since MySQL 4. The interesting parts are the format string itself (it uses percent-coded specifiers, like printf for dates), the few presets worth memorising, and CONVERT_TZ for timezone-correct output when the column is stored in UTC. I'll walk all of those on an events table, the kind of analytics-friendly schema where you constantly need to render the same timestamp three different ways for three different dashboards.

CREATE TABLE events (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  event_type    VARCHAR(60)     NOT NULL,
  actor_id      INT UNSIGNED    NOT NULL,
  occurred_at   DATETIME        NOT NULL,
  metadata      JSON            NULL
);

Jump to:

• DATE_FORMAT basics
• Common format presets
• The full specifier table
• Timezone conversion with CONVERT_TZ
• Relative dates: "3 days ago" output
• What about TIMESTAMP vs DATETIME?
• FAQ

DATE_FORMAT basics

The shortest correct form. Pass a DATE, DATETIME, or TIMESTAMP value and a format string of percent-coded specifiers.

SELECT id, event_type,
     DATE_FORMAT(occurred_at, '%Y-%m-%d %H:%i:%s') AS occurred_iso,
     DATE_FORMAT(occurred_at, '%W, %M %D, %Y')    AS occurred_readable
FROM events
ORDER BY occurred_at DESC
LIMIT 10;

The %Y-%m-%d %H:%i:%s form produces output like 2025-08-14 14:32:07, which is the ISO-8601-ish form most APIs and log systems want. The %W, %M %D, %Y form produces Thursday, August 14th, 2025, which is the human-readable form a marketing email or admin dashboard expects.

The function returns a string, not a date. If you need to do further date arithmetic on the result, you've gone too far — keep the column raw inside the query and only DATE_FORMAT it in the final SELECT list.

Works on MySQL 5.5, 5.6, 5.7, 8.0, and 8.4.

Common format presets

The five format patterns that cover 95% of real-world use cases:

SELECT
-- ISO 8601 with seconds (logs, APIs, JSON)
DATE_FORMAT(occurred_at, '%Y-%m-%dT%H:%i:%sZ')        AS iso_8601,

-- Date only (reports, daily aggregations)
DATE_FORMAT(occurred_at, '%Y-%m-%d')                  AS date_only,

-- US-style human readable
DATE_FORMAT(occurred_at, '%M %D, %Y at %h:%i %p')     AS us_human,

-- EU-style numeric
DATE_FORMAT(occurred_at, '%d/%m/%Y %H:%i')            AS eu_numeric,

-- RFC 2822 (email Date: headers)
DATE_FORMAT(occurred_at, '%a, %d %b %Y %H:%i:%s')     AS rfc_2822
FROM events LIMIT 5;

A few specifier rules worth knowing:

• %i is minutes, not %m (which is month). The two are commonly confused because most other formatting libraries use %M for month and %m for minutes; MySQL inverted them.
• %h is 12-hour clock, %H is 24-hour. Add %p (AM/PM) when using %h.
• %D is the day with English suffix (1st, 2nd, 3rd, 4th). %d is zero-padded numeric (01, 02).

The full specifier table

Reference table for the specifiers you'll actually reach for. There are a dozen more obscure ones for things like ISO week numbers; check the official docs for those.

Anything in the format string that is not a %-prefixed specifier passes through literally. So '%Y-%m-%d' produces 2025-08-14 because the dashes are literal characters between specifiers.

Timezone conversion with CONVERT_TZ

A common operational rule: store DATETIME columns in UTC, render in the user's local timezone for display. CONVERT_TZ(value, from_tz, to_tz) does the conversion inside the query.

SELECT id, event_type,
     occurred_at                                              AS utc_raw,
     CONVERT_TZ(occurred_at, 'UTC', 'America/New_York')       AS eastern,
     CONVERT_TZ(occurred_at, 'UTC', 'Europe/London')          AS london,
     CONVERT_TZ(occurred_at, 'UTC', 'Asia/Tokyo')             AS tokyo
FROM events
ORDER BY occurred_at DESC LIMIT 5;

Note CONVERT_TZ requires the MySQL timezone tables to be populated. Verify with SELECT NOW() = CONVERT_TZ(NOW(), 'UTC', 'UTC'); — if it returns 1, the function works; if it returns NULL, the tables are empty.

To populate the timezone tables on Linux:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

After loading, restart the MySQL service. On managed databases like RDS the tables are usually pre-loaded; if they are not, the cloud provider's docs explain how to enable them.

Numeric-offset conversions like CONVERT_TZ(occurred_at, '+00:00', '-05:00') work without the timezone tables, but they don't respect daylight-saving transitions. Use named zones for anything user-facing.

Relative dates: "3 days ago" output

MySQL does not have a built-in "humanize" function for relative-time output, but TIMESTAMPDIFF makes it a one-liner.

SELECT id, event_type, occurred_at,
CASE
  WHEN TIMESTAMPDIFF(MINUTE, occurred_at, NOW()) < 1   THEN 'just now'
  WHEN TIMESTAMPDIFF(MINUTE, occurred_at, NOW()) < 60  THEN CONCAT(TIMESTAMPDIFF(MINUTE, occurred_at, NOW()), ' minutes ago')
  WHEN TIMESTAMPDIFF(HOUR,   occurred_at, NOW()) < 24  THEN CONCAT(TIMESTAMPDIFF(HOUR,   occurred_at, NOW()), ' hours ago')
  WHEN TIMESTAMPDIFF(DAY,    occurred_at, NOW()) < 30  THEN CONCAT(TIMESTAMPDIFF(DAY,    occurred_at, NOW()), ' days ago')
  WHEN TIMESTAMPDIFF(MONTH,  occurred_at, NOW()) < 12  THEN CONCAT(TIMESTAMPDIFF(MONTH,  occurred_at, NOW()), ' months ago')
  ELSE CONCAT(TIMESTAMPDIFF(YEAR, occurred_at, NOW()), ' years ago')
END AS relative
FROM events
ORDER BY occurred_at DESC LIMIT 10;

For dashboard display this is fine. For anything user-facing that needs precise pluralisation ("1 minute ago" not "1 minutes ago") or i18n, do the humanise step in the application layer — MySQL's CASE syntax is not the right tool for that level of detail.

What about TIMESTAMP vs DATETIME?

Both store the same kinds of values; the storage and behaviour differ in two important ways:

• TIMESTAMP is stored in UTC and converted to the connection's session timezone on read/write. If your session is +05:00, INSERT NOW() stores UTC but SELECT col returns the value in +05:00. This is convenient if every client uses the same timezone, terrible otherwise.
• DATETIME is stored as-is with no timezone awareness. Whatever you put in is what you get out, byte-for-byte. The application layer is responsible for knowing what timezone the value is in.

For new tables, the practitioner consensus is: store everything as DATETIME in UTC, and convert in the query (with CONVERT_TZ) or in the application layer. TIMESTAMP has narrow valid ranges (1970-2038) and the implicit conversion bites you when you connect with the wrong session timezone.

For DATE_FORMAT purposes, both types work identically.

What to do next

If your reporting query also needs to group by formatted dates (daily aggregates, monthly summaries), the same DATE_FORMAT works inside GROUP BY. Wrapping the column in a function disables index use on it, though — for large tables, prefer grouping by the raw DATE(:column) and then DATE_FORMAT the result in the outer SELECT.

The events table here is also a natural place for duplicate detection if your application sometimes double-logs (webhook retries, retry storms): How to Find Duplicate Rows in MySQL covers the three patterns for catching duplicate event rows by hash, by ID, or by composite key.

For the wider reference covering date arithmetic, TIMESTAMPDIFF, STR_TO_DATE, and the rest of the date/time syntax in one place, the MySQL Cheat Sheet keeps it all on a single searchable page.

External reference: official documentation on DATE_FORMAT specifiers and CONVERT_TZ and timezone support.

FAQ