Himanshu SharmaSoftware engineer
Let’s talk

MySQL · Lesson 18 of 50

Date and time functions

Work with dates in MySQL: DATE, DATETIME and TIMESTAMP, formatting, extracting parts, adding intervals, finding differences and filtering by month.

15 minFunctionsUpdated 22 October 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonRead, format, calculate with and filter dates and times using MySQL date functions.

Date and time types

MySQL has separate types for dates and times:

Type Stores Example
DATE a calendar day 2024-05-02
TIME a time of day or duration 13:45:00
DATETIME day and time, no time zone 2024-05-02 13:45:00
TIMESTAMP a moment, stored in UTC and shown in the session's time zone 2024-05-02 13:45:00

The sample shop uses DATE for order_date and signup_date. For events that happen at a particular moment, such as when a payment was made, use DATETIME or TIMESTAMP. Always write dates as 'YYYY-MM-DD'.

The current date and time

sql
SELECT NOW() AS right_now, CURDATE() AS today, CURTIME() AS time_now;

NOW() returns the date and time, CURDATE() only the date and CURTIME() only the time. These values depend on the server's clock and time zone, so they change every time you run the statement. In a stored routine or a single statement, NOW() returns the same value throughout the statement.

Taking a date apart

sql
SELECT order_date, YEAR(order_date) AS yr, MONTH(order_date) AS mon, DAY(order_date) AS day_of_month,
       DAYNAME(order_date) AS weekday, MONTHNAME(order_date) AS month_name, QUARTER(order_date) AS qtr
FROM orders WHERE id IN (1, 12);
result
+------------+------+------+--------------+----------+------------+------+
| order_date | yr   | mon  | day_of_month | weekday  | month_name | qtr  |
+------------+------+------+--------------+----------+------------+------+
| 2024-01-05 | 2024 |    1 |            5 | Friday   | January    |    1 |
| 2024-04-18 | 2024 |    4 |           18 | Thursday | April      |    2 |
+------------+------+------+--------------+----------+------------+------+

EXTRACT(unit FROM date) is the standard form of the same idea, for example EXTRACT(YEAR FROM order_date).

Formatting dates

DATE_FORMAT(date, format) builds text using specifiers that start with a percent sign:

Specifier Meaning Specifier Meaning
%Y 4-digit year %d day of month, 01-31
%y 2-digit year %e day of month, 1-31
%m month number, 01-12 %H hour, 00-23
%M month name %i minutes
%b short month name %W weekday name
sql
SELECT DATE_FORMAT(order_date, '%d %b %Y') AS pretty, DATE_FORMAT(order_date, '%W') AS weekday FROM orders WHERE id = 1;
result
+-------------+---------+
| pretty      | weekday |
+-------------+---------+
| 05 Jan 2024 | Friday  |
+-------------+---------+

To read dates in another format into a real date, use STR_TO_DATE:

sql
SELECT STR_TO_DATE('14/02/2024', '%d/%m/%Y') AS parsed;
result
+------------+
| parsed     |
+------------+
| 2024-02-14 |
+------------+

Format dates for display in your application where possible. In the database keep real dates, so they sort and compare correctly.

Adding and subtracting time

Use DATE_ADD, DATE_SUB, or the + INTERVAL shorthand. The unit can be DAY, WEEK, MONTH, YEAR, HOUR and more:

sql
SELECT order_date, DATE_ADD(order_date, INTERVAL 7 DAY) AS due_date, order_date + INTERVAL 1 MONTH AS next_month, order_date - INTERVAL 1 YEAR AS year_before FROM orders WHERE id = 2;
result
+------------+------------+------------+-------------+
| order_date | due_date   | next_month | year_before |
+------------+------------+------------+-------------+
| 2024-02-14 | 2024-02-21 | 2024-03-14 | 2023-02-14  |
+------------+------------+------------+-------------+

Month arithmetic is clever: adding one month to 31 January gives the last day of February, not an invalid date.

sql
SELECT '2024-01-31' + INTERVAL 1 MONTH AS leap_year, LAST_DAY('2024-02-10') AS end_of_feb;
result
+------------+------------+
| leap_year  | end_of_feb |
+------------+------------+
| 2024-02-29 | 2024-02-29 |
+------------+------------+

Differences between dates

DATEDIFF(later, earlier) returns whole days. TIMESTAMPDIFF(unit, earlier, later) returns a number of any unit:

sql
SELECT DATEDIFF('2024-07-19', '2024-01-05') AS days, TIMESTAMPDIFF(MONTH, '2023-01-15', '2024-07-19') AS months, TIMESTAMPDIFF(YEAR, '2023-01-15', '2024-07-19') AS years;
result
+------+--------+-------+
| days | months | years |
+------+--------+-------+
|  196 |     18 |     1 |
+------+--------+-------+

Notice the order of arguments differs: DATEDIFF puts the later date first, TIMESTAMPDIFF the earlier one. A real example, how many days each customer had been a member on 1 January 2024:

sql
SELECT first_name, signup_date, DATEDIFF('2024-01-01', signup_date) AS days_member FROM customers ORDER BY days_member DESC LIMIT 3;
result
+------------+-------------+-------------+
| first_name | signup_date | days_member |
+------------+-------------+-------------+
| Asha       | 2023-01-15  |         351 |
| Ben        | 2023-02-03  |         332 |
| Chloe      | 2023-02-20  |         315 |
+------------+-------------+-------------+

Filtering by period

To select all orders from May 2024, use a half-open range: start included, next month's start excluded.

sql
SELECT id, order_date FROM orders WHERE order_date >= '2024-05-01' AND order_date < '2024-06-01';
result
+----+------------+
| id | order_date |
+----+------------+
| 13 | 2024-05-02 |
| 14 | 2024-05-15 |
| 15 | 2024-05-21 |
+----+------------+

It might be tempting to write WHERE MONTH(order_date) = 5, but that has two problems: it includes May of every year, and the function hides the column from an index. The half-open range is correct for DATE, DATETIME and TIMESTAMP alike, and it never misses a row at 23:59:59.

Time zones in short

TIMESTAMP values are converted to the session time zone when you read them, while DATETIME values are returned as stored. Many teams store all moments in UTC and convert in the application. Whatever you choose, be consistent and document it.

Common mistakes

  • Storing dates as text like '14/02/2024', which sorts and compares wrongly.
  • Using MONTH() or YEAR() in WHERE on big tables.
  • Mixing up the argument order of DATEDIFF and TIMESTAMPDIFF.
  • Ending a range at '2024-05-31' on a DATETIME column and missing most of that day.
  • Assuming the server and application share a time zone.

Exercise

List the order id, a formatted date such as 05 Jan 2024, and the weekday name for all orders placed in the second quarter of 2024 (April to June), using a date range in WHERE.

Show one possible answer
sql
SELECT id, DATE_FORMAT(order_date, '%d %b %Y') AS pretty_date, DAYNAME(order_date) AS weekday
FROM orders
WHERE order_date >= '2024-04-01' AND order_date < '2024-07-01'
ORDER BY order_date;
result
+----+-------------+-----------+
| id | pretty_date | weekday   |
+----+-------------+-----------+
| 10 | 04 Apr 2024 | Thursday  |
| 11 | 12 Apr 2024 | Friday    |
| 12 | 18 Apr 2024 | Thursday  |
| 13 | 02 May 2024 | Thursday  |
| 14 | 15 May 2024 | Wednesday |
| 15 | 21 May 2024 | Tuesday   |
| 16 | 03 Jun 2024 | Monday    |
| 17 | 10 Jun 2024 | Monday    |
| 18 | 25 Jun 2024 | Tuesday   |
+----+-------------+-----------+

Key points

  • DATE stores a day, DATETIME a day and time, and TIMESTAMP a moment in UTC that is shown in the session time zone.
  • NOW(), CURDATE() and CURTIME() give the current moment. YEAR(), MONTH(), DAYNAME() and DATE_FORMAT() take dates apart or format them.
  • Add or subtract time with DATE_ADD(date, INTERVAL n unit) or date + INTERVAL n unit. DATEDIFF and TIMESTAMPDIFF measure the gap.
  • Filter on a date range using >= and < on the raw column instead of wrapping the column in YEAR() or MONTH(), so an index can be used.

Check yourself

1. What does DATEDIFF('2024-03-10', '2024-03-01') return?
2. Which format specifier gives a four-digit year in DATE_FORMAT?
3. Which expression adds three months to a date column named d?
4. Why is WHERE order_date >= '2024-05-01' AND order_date < '2024-06-01' better than WHERE MONTH(order_date) = 5 on a large table?