How to Use Date Functions in MySQL
• MySQL stores dates and times in five distinct data types (DATE, DATETIME, TIMESTAMP, TIME, YEAR), each optimized for different use cases and storage requirements—choose DATETIME for most...
Key Insights
• MySQL stores dates and times in five distinct data types (DATE, DATETIME, TIMESTAMP, TIME, YEAR), each optimized for different use cases and storage requirements—choose DATETIME for most application timestamps and TIMESTAMP when you need automatic timezone conversion.
• Date arithmetic functions like DATE_ADD() and DATEDIFF() enable complex temporal calculations, but applying functions to indexed date columns in WHERE clauses destroys index performance—always compare against pre-calculated values instead.
• The DATE_FORMAT() and STR_TO_DATE() functions handle all formatting and parsing needs, but storing dates as strings is a critical mistake that breaks sorting, indexing, and comparison operations.
Understanding MySQL Date and Time Data Types
MySQL provides five temporal data types, each serving specific purposes. The DATE type stores dates from ‘1000-01-01’ to ‘9999-12-31’ in 3 bytes, perfect for birthdates or event dates where time isn’t relevant. DATETIME stores both date and time, ranging from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’, using 8 bytes. This is your go-to type for most application timestamps.
TIMESTAMP also stores date and time but with a smaller range (‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC) and uses 4 bytes. The crucial difference: TIMESTAMP automatically converts from your connection’s timezone to UTC for storage and back for retrieval. TIME stores time values from ‘-838:59:59’ to ‘838:59:59’, useful for durations or time-of-day data. YEAR stores just the year in 1 byte.
Here’s a practical table definition showing these types:
CREATE TABLE events (
id INT PRIMARY KEY AUTO_INCREMENT,
event_date DATE NOT NULL,
event_datetime DATETIME NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
duration TIME,
event_year YEAR
);
Use DATETIME for user-generated timestamps you want to store exactly as entered. Use TIMESTAMP for system-generated timestamps where automatic timezone handling matters, like audit trails in globally distributed applications.
Getting Current Date and Time
MySQL offers several functions to retrieve current temporal values. NOW() and CURRENT_TIMESTAMP() are synonymous, returning the current date and time as a DATETIME value. CURDATE() returns only the date, while CURTIME() returns only the time.
SELECT
NOW() AS current_datetime,
CURRENT_TIMESTAMP() AS also_current_datetime,
CURDATE() AS current_date,
CURTIME() AS current_time;
-- Result:
-- 2024-01-15 14:23:45 | 2024-01-15 14:23:45 | 2024-01-15 | 14:23:45
For UTC values regardless of your server’s timezone, use the UTC variants:
SELECT
NOW() AS local_time,
UTC_TIMESTAMP() AS utc_time,
UTC_DATE() AS utc_date,
UTC_TIME() AS utc_time_only;
This distinction matters when your application serves multiple timezones. Store everything in UTC using UTC_TIMESTAMP(), then convert to local time in your application layer. Never store local times without timezone context—you’ll create data integrity nightmares.
Extracting Date Components
Breaking dates into components enables grouping, filtering, and reporting. The extraction functions are straightforward:
SELECT
order_date,
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
DAY(order_date) AS order_day,
DAYNAME(order_date) AS day_name,
MONTHNAME(order_date) AS month_name,
QUARTER(order_date) AS quarter,
WEEK(order_date) AS week_number
FROM orders
WHERE order_id = 1001;
Real-world example—grouping sales by month:
SELECT
YEAR(order_date) AS year,
MONTH(order_date) AS month,
MONTHNAME(order_date) AS month_name,
COUNT(*) AS total_orders,
SUM(order_total) AS revenue
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;
For quarterly business reports:
SELECT
YEAR(order_date) AS year,
QUARTER(order_date) AS quarter,
COUNT(*) AS orders,
SUM(order_total) AS revenue
FROM orders
GROUP BY YEAR(order_date), QUARTER(order_date)
ORDER BY year, quarter;
DAYOFWEEK() returns 1 for Sunday through 7 for Saturday, useful for analyzing weekly patterns:
SELECT
DAYNAME(order_date) AS day,
COUNT(*) AS order_count
FROM orders
GROUP BY DAYOFWEEK(order_date), DAYNAME(order_date)
ORDER BY DAYOFWEEK(order_date);
Date Arithmetic and Manipulation
Date arithmetic is where MySQL’s temporal functions shine. DATE_ADD() and DATE_SUB() add or subtract intervals:
-- Calculate subscription expiration (30 days from start)
SELECT
user_id,
subscription_start,
DATE_ADD(subscription_start, INTERVAL 30 DAY) AS expiration_date
FROM subscriptions;
-- Find trial expirations (14 days)
SELECT
user_id,
trial_start,
DATE_ADD(trial_start, INTERVAL 14 DAY) AS trial_end
FROM users
WHERE account_type = 'trial';
INTERVAL supports multiple units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, and combinations:
-- Add 1 year, 2 months, and 15 days
SELECT DATE_ADD('2024-01-15', INTERVAL '1-2' YEAR_MONTH);
SELECT DATE_ADD('2024-01-15', INTERVAL 15 DAY);
-- Complex interval
SELECT DATE_ADD(NOW(), INTERVAL '1 3:30:00' DAY_SECOND);
DATEDIFF() calculates days between dates:
-- Find orders from last 30 days
SELECT *
FROM orders
WHERE DATEDIFF(NOW(), order_date) <= 30;
-- Calculate customer lifetime in days
SELECT
customer_id,
signup_date,
DATEDIFF(NOW(), signup_date) AS days_as_customer
FROM customers;
For more precise differences, use TIMESTAMPDIFF():
-- Calculate age in years
SELECT
user_id,
birthdate,
TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age
FROM users;
-- Calculate hours since last login
SELECT
user_id,
last_login,
TIMESTAMPDIFF(HOUR, last_login, NOW()) AS hours_since_login
FROM user_sessions;
-- Minutes between timestamps
SELECT
TIMESTAMPDIFF(MINUTE, '2024-01-15 10:00:00', '2024-01-15 14:30:00') AS minutes;
-- Result: 270
Formatting and Converting Dates
DATE_FORMAT() converts dates to strings using format specifiers:
SELECT
order_date,
DATE_FORMAT(order_date, '%m/%d/%Y') AS us_format,
DATE_FORMAT(order_date, '%d-%m-%Y') AS eu_format,
DATE_FORMAT(order_date, '%W, %M %d, %Y') AS long_format,
DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s') AS iso_format
FROM orders;
-- Result:
-- 2024-01-15 | 01/15/2024 | 15-01-2024 | Monday, January 15, 2024 | 2024-01-15 00:00:00
Common format specifiers: %Y (4-digit year), %y (2-digit year), %m (month 01-12), %d (day 01-31), %H (hour 00-23), %i (minutes), %s (seconds), %W (weekday name), %M (month name).
STR_TO_DATE() parses strings into dates:
-- Parse various date formats
SELECT STR_TO_DATE('01/15/2024', '%m/%d/%Y') AS parsed_date;
SELECT STR_TO_DATE('15-Jan-2024', '%d-%b-%Y') AS parsed_date;
SELECT STR_TO_DATE('2024.01.15 14:30:00', '%Y.%m.%d %H:%i:%s') AS parsed_datetime;
-- Import data with inconsistent date formats
UPDATE temp_imports
SET order_date = STR_TO_DATE(date_string, '%m/%d/%Y')
WHERE date_string REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$';
Unix timestamp conversions:
-- Convert datetime to Unix timestamp
SELECT UNIX_TIMESTAMP('2024-01-15 14:30:00') AS unix_time;
-- Result: 1705330200
-- Convert Unix timestamp to datetime
SELECT FROM_UNIXTIME(1705330200) AS datetime;
-- Result: 2024-01-15 14:30:00
-- Format Unix timestamp directly
SELECT FROM_UNIXTIME(1705330200, '%Y-%m-%d') AS formatted;
Practical Use Cases and Common Patterns
Filtering date ranges is a daily task. Use BETWEEN for inclusive ranges:
-- Orders in January 2024
SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
-- Orders in the last 7 days
SELECT *
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);
Finding records from the current month:
SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE())
AND MONTH(order_date) = MONTH(CURDATE());
-- Better performance (covered in next section)
SELECT *
FROM orders
WHERE order_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
AND order_date < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH);
Relative date queries:
-- Yesterday's orders
SELECT * FROM orders WHERE DATE(order_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);
-- Last week's signups
SELECT * FROM users
WHERE signup_date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK);
-- Next 30 days appointments
SELECT * FROM appointments
WHERE appointment_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY);
Performance Tips and Best Practices
The most critical performance rule: avoid applying functions to indexed columns in WHERE clauses. This prevents index usage:
-- BAD: Function on indexed column
EXPLAIN SELECT * FROM orders
WHERE YEAR(order_date) = 2024 AND MONTH(order_date) = 1;
-- Shows full table scan
-- GOOD: Compare against calculated values
EXPLAIN SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';
-- Uses index on order_date
Always index date columns used for filtering or sorting:
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_created_at ON users(created_at);
For timezone-aware applications, store all timestamps in UTC using TIMESTAMP or DATETIME with UTC values. Convert to local time in your application:
-- Store in UTC
INSERT INTO events (event_name, event_time)
VALUES ('Product Launch', UTC_TIMESTAMP());
-- Retrieve and convert in application layer
SELECT event_name, event_time FROM events;
-- Application converts UTC to user's timezone
Use DATE type when time isn’t needed—it saves storage and simplifies queries:
-- Use DATE for birthdates, not DATETIME
ALTER TABLE users MODIFY birthdate DATE;
When grouping by date parts, include the raw date in GROUP BY for better performance:
-- Include actual date column
SELECT DATE(order_date) AS order_day, COUNT(*)
FROM orders
GROUP BY DATE(order_date);
Avoid storing dates as strings. This breaks sorting, prevents efficient indexing, and complicates comparisons. Always use proper date types and convert during display, not storage. Following these practices ensures your date queries remain fast and your data remains consistent as your application scales.