MySQL – DATE_FORMAT and Date Functions

DATE_FORMAT turns a date into a string in whatever shape you want. Around it sits the rest of MySQL's date toolkit — arithmetic, differences, and pulling a date apart. This lesson covers those, and ends with the one that costs the most: grouping a report by month in a way that quietly disables your index.

Which type to store is a separate question, answered in date and time types.

DATE_FORMAT

SELECT DATE_FORMAT('2024-03-15 14:30:45', '%W, %d %M %Y')   AS long_form,
       DATE_FORMAT('2024-03-15 14:30:45', '%Y-%m-%d')       AS iso,
       DATE_FORMAT('2024-03-15 14:30:45', '%d/%m/%Y %H:%i') AS uk,
       DATE_FORMAT('2024-03-15 14:30:45', '%b %e, %Y %l:%i %p') AS us;

+-----------------------+------------+------------------+----------------------+
| long_form             | iso        | uk               | us                   |
+-----------------------+------------+------------------+----------------------+
| Friday, 15 March 2024 | 2024-03-15 | 15/03/2024 14:30 | Mar 15, 2024 2:30 PM |
+-----------------------+------------+------------------+----------------------+

The specifiers you will actually reach for:

The trap is %m is month and %i is minutes — %M is the month name and %s is seconds. Writing '%Y-%m-%d %H:%m' puts the month where the minutes should be, produces a plausible-looking string, and is easy to miss.

Day and month names are English unless you change lc_time_names. In general, prefer formatting for display in the application, where you have the user's locale and time zone; use DATE_FORMAT for grouping keys and for output that is genuinely SQL's job.

Arithmetic

SELECT DATE_ADD('2024-03-15', INTERVAL 10 DAY)     AS plus_10_days,
       DATE_SUB('2024-03-15', INTERVAL 2 MONTH)   AS minus_2_months,
       DATE_ADD('2024-01-31', INTERVAL 1 MONTH)   AS end_of_month,
       LAST_DAY('2024-02-05')                     AS last_day_feb;

+--------------+----------------+--------------+--------------+
| plus_10_days | minus_2_months | end_of_month | last_day_feb |
+--------------+----------------+--------------+--------------+
| 2024-03-25   | 2024-01-15     | 2024-02-29   | 2024-02-29   |
+--------------+----------------+--------------+--------------+

Look at end_of_month. One month after 31 January is 29 February, not the 31st, because that date does not exist — MySQL clamps to the end of the month. That is sensible and it is not reversible: adding a month and subtracting it again does not always return you to where you started. Monthly billing on the 31st needs a rule of its own.

INTERVAL accepts SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR and compound units. You can also just write date + INTERVAL 1 DAY.

Differences

SELECT DATEDIFF('2024-03-15', '2024-01-01')                          AS days,
       TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-03-15')              AS months,
       TIMESTAMPDIFF(HOUR, '2024-03-15 08:00', '2024-03-15 14:30')   AS hours;

+------+--------+-------+
| days | months | hours |
+------+--------+-------+
|   74 |      2 |     6 |
+------+--------+-------+

Two functions, two conventions, and it is worth keeping them straight:

• DATEDIFF(a, b) — days only, and the arguments are later first. DATEDIFF(b, a) gives -74.
• TIMESTAMPDIFF(unit, a, b) — any unit, and the arguments are earlier first. The opposite order.

Note the 6 in the last column: 08:00 to 14:30 is six and a half hours, and TIMESTAMPDIFF returns whole units, truncated. If you need the fraction, difference in a smaller unit and divide.

Pulling a date apart

SELECT EXTRACT(YEAR FROM '2024-03-15') AS y, EXTRACT(MONTH FROM '2024-03-15') AS m,
       QUARTER('2024-03-15') AS q, WEEKDAY('2024-03-15') AS weekday_0_mon,
       DAYNAME('2024-03-15') AS dayname;

+------+------+------+---------------+---------+
| y    | m    | q    | weekday_0_mon | dayname |
+------+------+------+---------------+---------+
| 2024 |    3 |    1 |             4 | Friday  |
+------+------+------+---------------+---------+

Beware the two weekday functions: WEEKDAY() counts from 0 = Monday and DAYOFWEEK() counts from 1 = Sunday. Picking the wrong one shifts every "is it a weekend" check by a day, and the query still runs.

Parsing

SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y') AS parsed;

+------------+
| parsed     |
+------------+
| 2024-03-15 |
+------------+

STR_TO_DATE is DATE_FORMAT in reverse, with the same specifiers. Use it for imported data rather than trusting MySQL to guess a format. It returns NULL and a warning when the string does not match.

Grouping by period — and the cost

SELECT DATE_FORMAT(d, '%Y-%m') AS month, COUNT(*) AS orders, ROUND(SUM(amount), 2) AS revenue
FROM   (SELECT DATE('2024-01-05') AS d, 25.50 AS amount
        UNION ALL SELECT '2024-01-19', 31.00
        UNION ALL SELECT '2024-02-02', 18.75
        UNION ALL SELECT '2024-02-28', 42.10
        UNION ALL SELECT '2024-03-11', 27.40) t
GROUP  BY month
ORDER  BY month;

+---------+--------+---------+
| month   | orders | revenue |
+---------+--------+---------+
| 2024-01 |      2 |   56.50 |
| 2024-02 |      2 |   60.85 |
| 2024-03 |      1 |   27.40 |
+---------+--------+---------+

'%Y-%m' is the right grouping key because it sorts correctly as a string — '2024-02' before '2024-10'. Grouping by MONTH(d) alone merges every January in your history into one bucket, which is a real reporting bug.

Now the cost. Grouping is fine; filtering this way is not.

-- cannot use an index on created_at: every row is read and converted
SELECT COUNT(*) FROM customer_order WHERE DATE_FORMAT(created_at, '%Y-%m') = '2024-06';

-- can: a half-open range on the bare column
SELECT COUNT(*) FROM customer_order
WHERE  created_at >= '2024-06-01' AND created_at < '2024-07-01';

The index stores created_at; the first query asks about a string derived from it, which the index knows nothing about. So MySQL reads every row. The rule from WHERE applies to every date function: filter on a bare column with a range, then group with DATE_FORMAT if you like. The GROUP BY runs on the rows that already survived, so it costs comparatively nothing.

If you truly need the derived value indexed, MySQL 8 can index a generated column — see indexes.

What to remember

• %m is month, %i is minutes. Not %M, not %s.
• Adding a month clamps to the end of the month, and is not reversible.
• DATEDIFF takes later-first; TIMESTAMPDIFF takes earlier-first and truncates.
• WEEKDAY is 0 = Monday; DAYOFWEEK is 1 = Sunday.
• Group by DATE_FORMAT, filter with a half-open range on the bare column.