MySQL Date Functions

Working with dates and times is a common task in database management. Whether you're tracking user registrations, scheduling appointments, or analyzing time-series data, MySQL provides a robust set of date and time functions to help you manage temporal data effectively.

Introduction to MySQL Date and Time Data Types

Before diving into date functions, it's important to understand the date and time data types available in MySQL:

• DATE: Stores date values in 'YYYY-MM-DD' format
• TIME: Stores time values in 'HH:MM:SS' format
• DATETIME: Stores date and time values in 'YYYY-MM-DD HH:MM:SS' format
• TIMESTAMP: Similar to DATETIME but converts to UTC for storage
• YEAR: Stores year values as 4 digits

Current Date and Time Functions

MySQL offers several functions to retrieve the current date and time:

NOW(), CURRENT_TIMESTAMP(), and SYSDATE()

These functions return the current date and time:

sql

SELECT NOW(); -- Returns current date and time
SELECT CURRENT_TIMESTAMP(); -- Same as NOW()
SELECT SYSDATE(); -- Similar to NOW() but returns the time at execution

Output:

'2023-09-21 14:30:45'
'2023-09-21 14:30:45'
'2023-09-21 14:30:45'

CURDATE(), CURRENT_DATE()

These functions return the current date:

sql

SELECT CURDATE(); -- Returns current date
SELECT CURRENT_DATE(); -- Same as CURDATE()

Output:

'2023-09-21'
'2023-09-21'

CURTIME(), CURRENT_TIME()

These functions return the current time:

sql

SELECT CURTIME(); -- Returns current time
SELECT CURRENT_TIME(); -- Same as CURTIME()

Output:

'14:30:45'
'14:30:45'

Date Extraction Functions

MySQL provides functions to extract specific parts from a date or time value:

YEAR(), MONTH(), DAY()

sql

SELECT YEAR('2023-09-21'); -- Extract year
SELECT MONTH('2023-09-21'); -- Extract month
SELECT DAY('2023-09-21'); -- Extract day

Output:

2023
9
21

HOUR(), MINUTE(), SECOND()

sql

SELECT HOUR('14:30:45'); -- Extract hour
SELECT MINUTE('14:30:45'); -- Extract minute
SELECT SECOND('14:30:45'); -- Extract second

Output:

14
30
45

DAYNAME(), MONTHNAME()

sql

SELECT DAYNAME('2023-09-21'); -- Name of the day
SELECT MONTHNAME('2023-09-21'); -- Name of the month

Output:

'Thursday'
'September'

DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR()

sql

SELECT DAYOFWEEK('2023-09-21'); -- Day of week (1=Sunday, 2=Monday, ..., 7=Saturday)
SELECT DAYOFMONTH('2023-09-21'); -- Day of month (same as DAY())
SELECT DAYOFYEAR('2023-09-21'); -- Day of year (1-366)

Output:

5
21
264

EXTRACT()

The EXTRACT() function allows you to extract various parts of a date:

sql

SELECT EXTRACT(YEAR FROM '2023-09-21'); -- Extract year
SELECT EXTRACT(MONTH FROM '2023-09-21'); -- Extract month
SELECT EXTRACT(DAY FROM '2023-09-21'); -- Extract day

Output:

2023
9
21

Date Formatting Functions

DATE_FORMAT()

This versatile function formats a date according to specified format strings:

sql

SELECT DATE_FORMAT('2023-09-21 14:30:45', '%W, %M %d, %Y'); -- Format date
SELECT DATE_FORMAT('2023-09-21 14:30:45', '%H:%i:%s'); -- Format time
SELECT DATE_FORMAT('2023-09-21 14:30:45', '%d/%m/%Y %H:%i'); -- Custom format

Output:

'Thursday, September 21, 2023'
'14:30:45'
'21/09/2023 14:30'

Common format specifiers include:

Specifier	Description	Example
%Y	4-digit year	2023
%y	2-digit year	23
%M	Month name	January
%m	Month (00-12)	09
%d	Day of month (00-31)	21
%W	Weekday name	Thursday
%w	Day of week (0=Sunday, 6=Saturday)	4
%H	Hour (00-23)	14
%h	Hour (01-12)	02
%i	Minutes (00-59)	30
%s	Seconds (00-59)	45
%p	AM or PM	PM

TIME_FORMAT()

Similar to DATE_FORMAT() but specifically for time values:

sql

SELECT TIME_FORMAT('14:30:45', '%H hours %i minutes %s seconds');

Output:

'14 hours 30 minutes 45 seconds'

Date Calculations

Date Addition and Subtraction

DATE_ADD() and ADDDATE()

sql

SELECT DATE_ADD('2023-09-21', INTERVAL 10 DAY); -- Add 10 days
SELECT ADDDATE('2023-09-21', INTERVAL 1 MONTH); -- Add 1 month
SELECT DATE_ADD('2023-09-21 14:30:45', INTERVAL 15 MINUTE); -- Add 15 minutes

Output:

'2023-10-01'
'2023-10-21'
'2023-09-21 14:45:45'

DATE_SUB() and SUBDATE()

sql

SELECT DATE_SUB('2023-09-21', INTERVAL 10 DAY); -- Subtract 10 days
SELECT SUBDATE('2023-09-21', INTERVAL 1 MONTH); -- Subtract 1 month

Output:

'2023-09-11'
'2023-08-21'

You can use various interval types:

• SECOND, MINUTE, HOUR
• DAY, WEEK, MONTH, QUARTER, YEAR
• SECOND_MICROSECOND, MINUTE_SECOND, HOUR_MINUTE, DAY_HOUR, YEAR_MONTH, etc.

DATEDIFF()

Calculate the difference between two dates (in days):

sql

SELECT DATEDIFF('2023-09-21', '2023-09-01'); -- Days between dates

Output:

20

TIMEDIFF()

Calculate the difference between two times:

sql

SELECT TIMEDIFF('14:30:45', '09:15:00'); -- Time difference

Output:

'05:15:45'

TIMESTAMPDIFF()

Calculate the difference in specified units:

sql

SELECT TIMESTAMPDIFF(MONTH, '2022-09-21', '2023-09-21'); -- Months between dates
SELECT TIMESTAMPDIFF(HOUR, '2023-09-21 09:00:00', '2023-09-21 17:00:00'); -- Hours between times

Output:

12
8

Date Validation and Conversion

STR_TO_DATE()

Convert a string to a date using a specified format:

sql

SELECT STR_TO_DATE('September 21, 2023', '%M %d, %Y'); -- String to date
SELECT STR_TO_DATE('21-09-2023 14:30:45', '%d-%m-%Y %H:%i:%s'); -- Custom format

Output:

'2023-09-21'
'2023-09-21 14:30:45'

TO_DAYS() and FROM_DAYS()

Convert between dates and day numbers since year 0:

sql

SELECT TO_DAYS('2023-09-21'); -- Days since year 0
SELECT FROM_DAYS(739060); -- Convert back to date

Output:

739060
'2023-09-21'

UNIX_TIMESTAMP() and FROM_UNIXTIME()

Convert between dates and Unix timestamps:

sql

SELECT UNIX_TIMESTAMP('2023-09-21 14:30:45'); -- Convert to Unix timestamp
SELECT FROM_UNIXTIME(1695303045); -- Convert Unix timestamp to date

Output:

1695303045
'2023-09-21 14:30:45'

Real-World Applications

Example 1: User Registration Analysis

Suppose you have a user registration table and want to analyze sign-ups by month:

sql

SELECT 
    YEAR(registration_date) AS year,
    MONTH(registration_date) AS month,
    MONTHNAME(registration_date) AS month_name,
    COUNT(*) AS num_registrations
FROM users
GROUP BY year, month
ORDER BY year, month;

Example 2: Finding Upcoming Birthdays

Find users with birthdays in the next 30 days:

sql

SELECT 
    user_id, 
    full_name,
    birth_date,
    DATE_FORMAT(birth_date, '%M %d') AS birthday
FROM users
WHERE 
    DAYOFYEAR(DATE_ADD(birth_date, 
        INTERVAL YEAR(CURDATE()) - YEAR(birth_date) YEAR)) 
    BETWEEN DAYOFYEAR(CURDATE()) AND DAYOFYEAR(CURDATE()) + 30
ORDER BY DAYOFYEAR(birth_date);

Example 3: Calculating Age

Calculate the age of users in years:

sql

SELECT 
    user_id,
    full_name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users;

Example 4: Business Hours Calculation

Calculate business hours worked between timestamps:

sql

SELECT 
    employee_id,
    check_in,
    check_out,
    TIMESTAMPDIFF(HOUR, check_in, check_out) AS hours_worked,
    CASE
        WHEN WEEKDAY(check_in) IN (5, 6) THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type
FROM attendance
WHERE check_in >= '2023-09-01' AND check_out <= '2023-09-30';

Summary

MySQL provides a comprehensive set of date functions that allow you to:

• Get current date and time values
• Extract components from dates (year, month, day, etc.)
• Format dates for display
• Perform date arithmetic and calculate differences
• Convert between different date formats

These functions are essential for working with temporal data in databases and can help you build applications that handle dates and times effectively.

Practice Exercises

1. Write a query to display the current date in the format "Thursday, September 21, 2023".
2. Create a query that calculates the number of days until Christmas of the current year.
3. Write a query to find all records in a table where the date is within the last 7 days.
4. Create a function that returns a person's age in years, months, and days based on their birth date.
5. Write a query that groups records by week number within the current year.

Additional Resources

• MySQL Official Documentation - Date and Time Functions
• MySQL Date Format Specifiers Reference
• SQL Date Functions Cheat Sheet

With these date functions at your disposal, you'll be well-equipped to tackle any date and time challenges in your MySQL databases.