Formatting dates in MySQL

The built-in set holds 21 specifiers, from %Y (the 4 digit year) to %f (microseconds, which MySQL 5.6 added in 2013), and the two functions run in opposite directions: DATE_FORMAT turns a date into a string, and STR_TO_DATE turns a string into a date. Everything below assumes a server on MySQL 8.0, where the default time zone comes from the system setting, and it follows the stack the way it runs in production: Apache in front of a PHP app, the app issuing SQL, and the format string deciding what the user finally sees.

DATE_FORMAT: the full list of specifiers

DATE_FORMAT works by reading your format string left to right and replacing every % code with the matching piece of the date value, so the specifier list is the entire tool. The table below groups the 21 codes MySQL 8.0 ships, with the meaning of each one and a sample rendered from 2026-09-05 14:30:05.123456.

Three pairs deserve attention because they trip people up. %i is minutes, not months, and it collides in the eye with %m, the month. %p returns AM or PM, not percent. And %h and %H differ only in the 12 hour versus 24 hour clock, with %h and %I both covering the 01 to 12 range and %H and %k covering 0 to 23. Any character you leave in the string that is not a % code prints verbatim, which is why '2026-09-05 14:30:05' is the default display: those hyphens and colons are plain text, not specifiers.

STR_TO_DATE: parsing text back into dates

STR_TO_DATE runs the format string in reverse: it reads the input text, matches it against the pattern you supply, and returns a DATE or DATETIME value, or NULL where the text does not match. The pattern and the text must agree exactly on the separators, because a dash that is plain text in DATE_FORMAT is a required literal in STR_TO_DATE. The three examples below all return 2026-09-05 on MySQL 8.0.

• STR_TO_DATE('05/09/2026', '%d/%m/%Y') treats the first group as the day and the second as the month, the European reading
• STR_TO_DATE('09/05/2026', '%m/%d/%Y') treats the first group as the month, the American reading, and yields the same date from different text
• STR_TO_DATE('September 5, 2026', '%M %e, %Y') handles a full month name, a 1 digit day, and a comma as literal text

Where the text fails to match, you get NULL instead of an error, which is the detail that hides data loss in ETL scripts. A log file mixing 24 hour and 12 hour times, or a feed that switched its separator from a space to a T in a single release, will hand you rows of NULL that a SELECT COUNT(*) reports as rows that were never imported. Run the parser against a sample of the real data before the full load, and check the NULL count per column as part of the same pass. For the daily drills this kind of parsing belongs to, the MySQL in practice pages in the section cover the query, the index, and the log side of the same job, and the MySQL Workbench walkthrough shows how to paste a format string into the query tab and inspect the result grid before it touches production data. If the parser needs to read a feed that only PostgreSQL would decode natively, the Choosing between MySQL and PostgreSQL page weighs that one edge case against the rest of the stack before you reach for a second database. And when the feed arrives from a process that has stopped, the Kill a Process on Linux page is the 5 minute procedure for finding the stuck writer and clearing the queue before the next parse round.

Time zones and the UTC boundary

Every DATE_FORMAT call reads the column in the time zone the session is using, so the output you get is only as right as the session time zone is. MySQL 8.0 defaults the server to the system time zone, the session inherits it at connect time, and PHP passes it along only if the connection layer sets it. The boundary is UTC: a DATETIME column stores wall time as written, a TIMESTAMP column converts on the way in and back out, and the two disagree by the session offset the moment you leave UTC. The fix in the right order is 3 steps: store TIMESTAMP where the value is a point in time, set the session offset explicitly on connect, and format only at the presentation layer. Concretely, the PHP line that keeps the app honest is date_default_timezone_set('UTC'), paired with a SELECT that carries the offset, and the format string that renders it for a reader in London is DATE_FORMAT(CONVERT_TZ(orders.created_at, 'UTC', 'Europe/London'), '%d/%m/%Y %H:%i'), which prints the local wall time rather than the stored one.

Common offset mistakes

The 4 mistakes that recur in production, and the check for each, line up like this.

• A session left on the server local time: verify with SELECT @@session.time_zone before any report runs
• A TIMESTAMP column round-tripped through a daylight saving boundary: the value shifts by 1 hour, which CONVERT_TZ shows if you name both zones
• A format string that assumes a 12 hour clock: %h and %p together, or %H alone, and nothing mixed
• A parsed input that was local time but got stored as UTC: the offset error is invisible until the next DST switch, which is the 2 times a year it jumps

Writing queries against dates

The rule that separates a fast date query from a slow one is where the function sits: wrap the column and the index goes away, wrap the constant and the index survives. The three forms below rank a WHERE clause on created_at from worst to best on MySQL 8.0 with an index on that column.

1. WHERE DATE_FORMAT(created_at, '%Y-%m') = '2026-09' applies a function to the column and forces a full scan, the form that turns a 2 millisecond index lookup into a 900 millisecond table read
2. WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01' uses the half-open range the index was built for and returns the same rows
3. WHERE DATE(created_at) = '2026-09-05' is the middle trap: it looks simple, but the function on the column still kills the index, so prefer created_at >= '2026-09-05' AND created_at < '2026-09-06' for a single day

The same principle reaches the ORDER BY and the GROUP BY. A GROUP BY on the raw column uses the index order; a GROUP BY on DATE_FORMAT(created_at, '%Y-%m-%d') builds a temporary table for the grouping key, and on a table past 10 million rows the temporary table is where the query time goes. Keep the formatting for the SELECT list, where the index has already done its work, and leave the filtering and grouping on the bare column.

Choosing a format for a given output

The output you need decides the string, and the 4 targets below cover most of what a LAMP site actually renders. Each row pairs the target, the string that produces it, and the gotcha that version has.

One more convention worth keeping: the DATE type carries no time part at all, so a DATE column formatted with %H:%i:%s prints 00:00:00, and a DATETIME column formatted without any time specifier hides the time instead of nulling it. The column type is the first decision, the format string is the last, and everything in between is the session time zone doing its quiet work on every row.

Where to go next

MYSQLMySQL in practiceThe Relational Database for Server-Side Web DevelopmentMYSQLMySQL Workbench walkthroughInstall, Configure, and Use the GUIMYSQLChoosing between MySQL and PostgreSQLWhich One Fits Your StackSYSADMINKill a Process on LinuxSignals, PID, and Stuck MySQL