MySQL Date and Time Functions

MySQL provides a comprehensive set of date and time functions to retrieve, format, calculate, and manipulate date and time values. These functions are essential for applications that work with timestamps, deadlines, schedules, or any time-sensitive data.

Current Date and Time Functions

SELECT NOW();        -- 2024-05-10 14:30:25
SELECT CURDATE();    -- 2024-05-10
SELECT CURTIME();    -- 14:30:25

Extracting Parts of a Date

SELECT YEAR('2024-05-10');    -- 2024
SELECT MONTH('2024-05-10');   -- 5
SELECT DAY('2024-05-10');     -- 10
SELECT HOUR('14:30:25');      -- 14
SELECT MINUTE('14:30:25');    -- 30
SELECT SECOND('14:30:25');    -- 25
SELECT DAYNAME('2024-05-10'); -- Friday
SELECT MONTHNAME('2024-05-10'); -- May
SELECT DAYOFWEEK('2024-05-10'); -- 6 (1=Sunday)

DATE_FORMAT()

DATE_FORMAT() formats a date value into a readable string using format specifiers.

SELECT DATE_FORMAT('2024-05-10', '%d-%m-%Y');   -- 10-05-2024
SELECT DATE_FORMAT('2024-05-10', '%D %M %Y');   -- 10th May 2024
SELECT DATE_FORMAT(NOW(), '%H:%i %p');          -- 02:30 PM

Common Format Specifiers

DATE_ADD() and DATE_SUB()

These functions add or subtract a time interval from a date.

SELECT DATE_ADD('2024-05-10', INTERVAL 7 DAY);    -- 2024-05-17
SELECT DATE_SUB('2024-05-10', INTERVAL 1 MONTH);  -- 2024-04-10
SELECT DATE_ADD('2024-05-10', INTERVAL 1 YEAR);   -- 2025-05-10

DATEDIFF()

Returns the number of days between two dates.

SELECT DATEDIFF('2024-05-20', '2024-05-10');  -- 10
SELECT DATEDIFF('2024-01-01', '2024-12-31');  -- -365

TIMESTAMPDIFF()

Returns the difference between two dates or timestamps in a specified unit.

SELECT TIMESTAMPDIFF(YEAR, '2000-06-15', CURDATE());   -- age in years
SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-05-01'); -- 4
SELECT TIMESTAMPDIFF(DAY, '2024-04-01', '2024-05-10');   -- 39

STR_TO_DATE()

Converts a string into a date using a format pattern.

SELECT STR_TO_DATE('10-05-2024', '%d-%m-%Y');  -- 2024-05-10

Practical Example: Order Age Report

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE
);

INSERT INTO orders VALUES (1, 'Anita Roy', '2024-04-01');
INSERT INTO orders VALUES (2, 'Suresh Mehta', '2024-05-05');

SELECT customer_name,
       order_date,
       DATEDIFF(CURDATE(), order_date) AS days_since_order,
       DATE_FORMAT(order_date, '%D %M %Y') AS formatted_date
FROM orders;

Key Points

• NOW() returns the current date and time; CURDATE() returns only the date.
• YEAR(), MONTH(), DAY() extract individual parts from a date.
• DATE_FORMAT() formats dates into human-readable strings.
• DATE_ADD() and DATE_SUB() shift a date by a given interval.
• DATEDIFF() calculates the number of days between two dates.