Unit

MySQL Date and Time Functions

Learn MySQL date and time functions for storing, extracting, formatting, converting, calculating, comparing, and filtering DATE, TIME, DATETIME, and TIMESTAMP values.

MySQL date and time functions help you work with calendar dates, times of day, timestamps, durations, and date ranges. You can use them to display the current time, extract a month from an order date, calculate a due date, format report labels, or find events that occurred during a particular day.

This lesson assumes familiarity with MySQL, SELECT, WHERE, ORDER BY, and basic grouping.

MySQL Temporal Data Types

A temporal data type stores a date, a time, or both. Choosing the correct type is important because it affects validation, sorting, arithmetic, and time zone behavior.

The usual display formats are YYYY-MM-DD for DATE, HH:MM:SS for TIME, and YYYY-MM-DD HH:MM:SS for DATETIME and TIMESTAMP. Fractional seconds can be enabled with a precision such as DATETIME(6) or TIMESTAMP(6).

Use DATE when the time is not part of the meaning. Use DATETIME when the stored wall-clock value should remain unchanged, such as a local appointment time. Use TIMESTAMP when the value represents an instant that should be interpreted across time zones, such as a web request or payment event.

Current Date and Time Functions

Choose the function according to whether the query needs a date, a time, or both.

SELECT CURDATE() AS today,
       CURTIME() AS current_time,
       NOW() AS current_timestamp;

A date-only value cannot identify a time of day, and a time-only value cannot identify a calendar day. Although NOW() returns a date and time expression, it does not mean that the expression's result must be stored in a TIMESTAMP column.

Extracting Date and Time Parts

Extraction functions return components such as the year, month, hour, or weekday. They are useful in reports, labels, and calculations.

SELECT YEAR(created_at) AS order_year,
       MONTH(created_at) AS order_month,
       DAYNAME(created_at) AS weekday_name,
       QUARTER(created_at) AS quarter_number
FROM orders;

EXTRACT uses a unit-based syntax:

SELECT EXTRACT(YEAR FROM created_at) AS order_year,
       EXTRACT(MONTH FROM created_at) AS order_month,
       EXTRACT(QUARTER FROM created_at) AS quarter_number,
       EXTRACT(HOUR FROM created_at) AS order_hour
FROM orders;

Weekday numbering differs between functions. DAYOFWEEK() returns Sunday as 1 and Saturday as 7. WEEKDAY() returns Monday as 0 and Sunday as 6. Week numbers also depend on the selected week mode. For ISO-style weeks, explicitly choose the appropriate mode, commonly WEEK(date, 3), and consider YEARWEEK(date, 3) because an ISO week can belong to a neighboring calendar year.

Formatting Temporal Values

DATE_FORMAT() converts a date or timestamp into text. It is intended for presentation, such as a report heading or export label.

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS report_date,
       DATE_FORMAT(created_at, '%M %d, %Y at %H:%i') AS report_label
FROM orders;

Use TIME_FORMAT() for time values:

SELECT TIME_FORMAT(start_time, '%H:%i') AS opening_time
FROM store_hours;

Converting Strings and Numbers to Dates

STR_TO_DATE() parses text according to a pattern. The pattern must describe the input's order and separators.

SELECT STR_TO_DATE('18/08/2026', '%d/%m/%Y') AS parsed_date;
SELECT STR_TO_DATE('2026-08-18 14:35', '%Y-%m-%d %H:%i') AS parsed_datetime;

Use DATE() to obtain the date portion, TIME() to obtain the time portion, and CAST() when an explicit type conversion is clearer:

SELECT DATE('2026-08-18 14:35:20') AS date_part,
       TIME('2026-08-18 14:35:20') AS time_part,
       CAST('2026-08-18' AS DATE) AS converted_date,
       CAST('14:35:20' AS TIME) AS converted_time;

Use unambiguous MySQL date literals such as '2026-08-18' and datetime literals such as '2026-08-18 14:35:20'. Imported values such as 08/18/2026 are ambiguous unless the application defines whether the first number is a month or a day.

When conversion returns NULL or a warning, check the format specifiers, separators, day-month order, zero padding, locale assumptions, and whether the calendar date actually exists. Invalid or incomplete input can be rejected, adjusted, or produce warnings depending on MySQL version and SQL mode. Validate imported data rather than relying on permissive conversion.

Date and Time Arithmetic

An INTERVAL is a quantity paired with a unit, such as 30 DAY or 2 MONTH. Use DATE_ADD() and ADDDATE() to add intervals, and DATE_SUB() and SUBDATE() to subtract them.

SELECT DATE_ADD(order_date, INTERVAL 30 DAY) AS follow_up_date,
       DATE_ADD(order_date, INTERVAL 2 WEEK) AS two_weeks_later,
       DATE_ADD(order_date, INTERVAL 1 MONTH) AS next_month,
       DATE_SUB(NOW(), INTERVAL 7 DAY) AS seven_days_ago;

Common interval units include DAY, WEEK, MONTH, QUARTER, YEAR, HOUR, MINUTE, and SECOND. Compound units such as DAY_HOUR can represent multiple components when the interval expression follows MySQL's required format.

SELECT DATEDIFF(due_date, CURDATE()) AS days_until_due,
       TIMESTAMPDIFF(MONTH, start_date, end_date) AS full_months
FROM invoices;

DATEDIFF(end, start) measures calendar-day boundaries and ignores hours, minutes, and seconds. TIMESTAMPDIFF(unit, start, end) measures the difference in the requested unit. For example, a period of 23 hours produces zero completed days with TIMESTAMPDIFF(DAY, ...), while dates on adjacent calendar days can produce a one-day DATEDIFF() result even when less than 24 hours elapsed.

Month arithmetic follows calendar rules. Adding one month to a date near the end of a 31-day month may produce the last valid day of the destination month. February has 28 days, or 29 in a leap year. Test month-end and leap-day cases when the business rule depends on anniversaries or billing schedules.

Date Boundaries and Calendar Helpers

LAST_DAY() returns the final date in the month containing its argument.

SELECT LAST_DAY(invoice_date) AS invoice_month_end,
       LAST_DAY(CURDATE()) AS end_of_current_month
FROM invoices;

To get the date portion of a timestamp, use DATE(timestamp_value). To construct a date from separate components, use MAKEDATE(year, day_of_year) when you have a year and ordinal day, or STR_TO_DATE() when building a value from text. For a year, month, and day, a clear approach is to construct an ISO string and parse it:

SELECT STR_TO_DATE('2026-08-18', '%Y-%m-%d') AS constructed_date;

Calendar weeks require an explicit convention. A week may begin on Sunday or Monday, and the first week may be defined differently by locale or ISO rules. Document the rule used by a report. ISO week-based reports should group by both ISO week and ISO week-year, not by calendar year alone.

Using Date Functions in Queries

Filtering by a Date Range

For a DATE column, an inclusive range is often straightforward:

SELECT *
FROM orders
WHERE order_date BETWEEN '2026-08-01' AND '2026-08-31';

For a DATETIME or TIMESTAMP column, use a half-open range: include the start and exclude the next boundary.

SELECT *
FROM events
WHERE occurred_at >= '2026-08-01 00:00:00'
  AND occurred_at <  '2026-09-01 00:00:00';

Today, Yesterday, and Rolling Periods

-- Events occurring today
SELECT *
FROM events
WHERE occurred_at >= CURDATE()
  AND occurred_at <  CURDATE() + INTERVAL 1 DAY;

-- Events occurring yesterday
SELECT *
FROM events
WHERE occurred_at >= CURDATE() - INTERVAL 1 DAY
  AND occurred_at <  CURDATE();

-- Events from the previous seven 24-hour days
SELECT *
FROM events
WHERE occurred_at >= NOW() - INTERVAL 7 DAY
  AND occurred_at <  NOW();

-- Rows in the current calendar month
SELECT *
FROM orders
WHERE order_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND order_date <  DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01');

Grouping and Sorting

SELECT YEAR(created_at) AS order_year,
       MONTH(created_at) AS order_month,
       COUNT(*) AS order_count
FROM orders
GROUP BY YEAR(created_at), MONTH(created_at)
ORDER BY order_year, order_month;

Temporal columns sort chronologically when stored as temporal types. Do not sort by a display string unless its format is deliberately designed to be chronologically sortable, such as %Y-%m-%d.

Overdue Invoices, Ages, and Scheduled Dates

SELECT invoice_id,
       due_date,
       CASE
         WHEN due_date < CURDATE() THEN DATEDIFF(CURDATE(), due_date)
         ELSE 0
       END AS overdue_days,
       DATE_ADD(due_date, INTERVAL 30 DAY) AS reminder_date
FROM invoices;

SELECT customer_id,
       TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age_years,
       TIMESTAMPDIFF(YEAR, employment_start, CURDATE()) AS completed_years
FROM customers;

TIMESTAMPDIFF(YEAR, ...) is useful for completed years, but exact age or tenure rules may require checking whether this year's anniversary has occurred. Birthday and employment-anniversary calculations should be tested around the boundary date.

Performance-Aware Date Filtering

Applying a function to an indexed column can prevent MySQL from using a simple index range lookup efficiently. For example, this pattern may be slow on a large table:

-- Avoid when occurred_at is indexed
SELECT *
FROM events
WHERE DATE(occurred_at) = CURDATE();

Instead, calculate constant boundaries and compare the raw column:

SELECT *
FROM events
WHERE occurred_at >= CURDATE()
  AND occurred_at <  CURDATE() + INTERVAL 1 DAY;

The same principle applies to formatted comparisons. Avoid comparing DATE_FORMAT(occurred_at, ...) in the WHERE clause when a range can express the condition. A half-open range has an inclusive lower bound and an exclusive upper bound, so it includes every fractional second at the start and avoids trying to guess the final second of a day.

Also avoid equality between a date literal and a timestamp column when the column contains a time component:

-- Usually does not match all events on the date
WHERE occurred_at = '2026-08-18'

Use the beginning of the date and the beginning of the following date instead. Review execution plans with EXPLAIN when performance matters.

NULL Values and Edge Cases

• Most date functions return NULL when their temporal argument is NULL. Comparisons with NULL are not true; use IS NULL or IS NOT NULL.
• Zero dates such as '0000-00-00' are legacy values, not real calendar dates. Whether they are accepted or rejected depends on SQL mode and MySQL configuration. Prefer NULL for an unknown date.
• Invalid dates may produce errors, warnings, adjusted values, or NULL depending on the operation and SQL mode. Inspect validation settings with SELECT @@sql_mode;.
• Leap days occur only in valid leap years. Month lengths range from 28 to 31 days, so do not assume that adding a month equals adding 30 days.
• Daylight-saving transitions can create missing or repeated local times. Time zone-sensitive event data should use an intentional time zone policy, often storing instants as TIMESTAMP or normalized UTC values.
• Results involving TIMESTAMP, NOW(), and CURDATE() can differ between sessions if session time zones differ. Check @@session.time_zone before diagnosing discrepancies.

Practical Troubleshooting

Exam- and Interview-Relevant Notes

• DATE stores a calendar date; TIME stores a time or duration; DATETIME stores date and time without automatic time zone conversion; TIMESTAMP performs session time zone conversion.
• DATEDIFF() returns calendar-day difference and ignores time portions.
• TIMESTAMPDIFF() requires a unit and returns completed units between two values.
• DATE_FORMAT() produces text for presentation. STR_TO_DATE() parses text into a temporal value.
• DAYOFWEEK() and WEEKDAY() use different numbering conventions.
• For timestamp columns, use half-open ranges such as >= start and < next_start.
• Do not wrap an indexed temporal column in DATE(), YEAR(), or DATE_FORMAT() when a range predicate can express the same filter.

Summary

Use temporal types according to the meaning of the data, select current-value functions based on the required precision, and extract components only when reporting or analysis needs them. Use formatting for output rather than storage, use STR_TO_DATE() for controlled imports, and use DATE_ADD(), DATE_SUB(), DATEDIFF(), and TIMESTAMPDIFF() for arithmetic. For reliable and fast filtering, compare raw date columns with half-open ranges and account for time zones, month boundaries, leap years, SQL modes, and NULL values.