MySQL Date Functions

MySQL stores dates and times in several formats — DATE, TIME, DATETIME, and TIMESTAMP. The built-in date functions let you retrieve the current date and time, extract individual parts, format output, calculate differences, and shift dates forward or backward.

---

Getting the Current Date and Time

SELECT NOW();        -- '2025-06-15 14:32:07'  (date and time)
SELECT CURDATE();    -- '2025-06-15'           (date only)
SELECT CURTIME();    -- '14:32:07'             (time only)

---

Extracting Parts of a Date

Use individual extraction functions to pull out a specific component of a date or datetime value:

SELECT YEAR('2025-06-15');    -- 2025
SELECT MONTH('2025-06-15');   -- 6
SELECT DAY('2025-06-15');     -- 15
SELECT HOUR('14:32:07');      -- 14
SELECT MINUTE('14:32:07');    -- 32
SELECT SECOND('14:32:07');    -- 7
SELECT DAYNAME('2025-06-15'); -- 'Sunday'
SELECT MONTHNAME('2025-06-15'); -- 'June'
SELECT WEEK('2025-06-15');    -- 24  (week number of the year)

These functions are particularly useful in GROUP BY to aggregate data by year or month:

-- Total orders per month
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS mo, COUNT(*) AS total
FROM orders
GROUP BY yr, mo
ORDER BY yr, mo;

---

DATE_FORMAT

Format a date or datetime value as a string using format specifiers:

SELECT DATE_FORMAT('2025-06-15', '%d %M %Y');   -- '15 June 2025'
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');           -- '2025-06-15'
SELECT DATE_FORMAT(NOW(), '%h:%i %p');           -- '02:32 PM'

Common format specifiers:

Specifier	Meaning
%Y	4-digit year
%m	Month number (01–12)
%M	Month name (January–December)
%d	Day (01–31)
%D	Day with suffix (1st, 2nd, 3rd...)
%H	Hour (00–23)
%h	Hour (01–12)
%i	Minutes (00–59)
%s	Seconds (00–59)
%p	AM or PM

---

DATEDIFF

Calculate the number of days between two dates. The result is date1 - date2:

SELECT DATEDIFF('2025-12-31', '2025-01-01');   -- 364
SELECT DATEDIFF('2025-01-01', '2025-12-31');   -- -364

-- Days since each order was placed
SELECT id, amount, DATEDIFF(CURDATE(), created_at) AS days_ago
FROM orders;

---

DATE_ADD and DATE_SUB

Add or subtract a time interval from a date:

SELECT DATE_ADD('2025-06-15', INTERVAL 30 DAY);    -- '2025-07-15'
SELECT DATE_ADD('2025-06-15', INTERVAL 3 MONTH);   -- '2025-09-15'
SELECT DATE_ADD('2025-06-15', INTERVAL 1 YEAR);    -- '2026-06-15'
SELECT DATE_SUB('2025-06-15', INTERVAL 7 DAY);     -- '2025-06-08'

Common interval units: DAY, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND.

---

TIMESTAMPDIFF

Calculate the difference between two datetime values in a specified unit:

-- Age in years
SELECT TIMESTAMPDIFF(YEAR, '1995-03-20', CURDATE()) AS age;

-- Hours between two timestamps
SELECT TIMESTAMPDIFF(HOUR, '2025-06-15 08:00:00', '2025-06-15 14:30:00');  -- 6

---

LAST_DAY

Returns the last day of the month for any given date — useful for monthly reporting:

SELECT LAST_DAY('2025-02-01');   -- '2025-02-28'
SELECT LAST_DAY('2024-02-01');   -- '2024-02-29'  (leap year)

---

Key Points to Remember

• NOW() returns date and time; CURDATE() returns date only; CURTIME() returns time only.
• Use YEAR(), MONTH(), DAY() to extract parts — especially useful in GROUP BY.
• DATE_FORMAT() converts a date to a custom string for display.
• DATEDIFF(d1, d2) returns d1 - d2 in days.
• DATE_ADD() and DATE_SUB() shift a date by an interval.
• TIMESTAMPDIFF(unit, d1, d2) returns the difference in any unit you choose.