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' formatTIME: Stores time values in 'HH:MM:SS' formatDATETIME: Stores date and time values in 'YYYY-MM-DD HH:MM:SS' formatTIMESTAMP: Similar to DATETIME but converts to UTC for storageYEAR: 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:
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:
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:
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()
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()
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()
SELECT DAYNAME('2023-09-21'); -- Name of the day
SELECT MONTHNAME('2023-09-21'); -- Name of the month
Output:
'Thursday'
'September'
DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR()
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:
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:
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:
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()
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()
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):
SELECT DATEDIFF('2023-09-21', '2023-09-01'); -- Days between dates
Output:
20
TIMEDIFF()
Calculate the difference between two times:
SELECT TIMEDIFF('14:30:45', '09:15:00'); -- Time difference
Output:
'05:15:45'
TIMESTAMPDIFF()
Calculate the difference in specified units:
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:
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:
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:
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:
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:
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:
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:
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
- Write a query to display the current date in the format "Thursday, September 21, 2023".
- Create a query that calculates the number of days until Christmas of the current year.
- Write a query to find all records in a table where the date is within the last 7 days.
- Create a function that returns a person's age in years, months, and days based on their birth date.
- 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.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)