⏱️ MySQL Date & Time Functions – Complete Guide with Syntax & Examples

---

🧲 Introduction – Why Use MySQL Date & Time Functions?

From tracking user signups to generating financial reports, dates and times are essential in every database application. MySQL provides a rich set of date and time functions to help you extract, format, calculate, and manipulate temporal data directly within SQL queries.

Whether you’re building an attendance tracker, a billing system, or a time-series dashboard, mastering these functions empowers you to write cleaner, more dynamic, and efficient SQL logic.

🎯 In this guide, you’ll learn:

• Key MySQL date and time functions
• How to format, compare, and compute with dates
• Examples of temporal filtering, formatting, and reporting
• Real-world use cases and best practices

---

📘 Categories of MySQL Date & Time Functions

---

📅 1. Current Date/Time Functions

Function	Description	Example	Output
NOW()	Current date and time	SELECT NOW();	'2025-05-21 14:30:00'
CURDATE()	Current date only	SELECT CURDATE();	'2025-05-21'
CURTIME()	Current time only	SELECT CURTIME();	'14:30:00'
SYSDATE()	Same as NOW(), system-based	SELECT SYSDATE();	'2025-05-21 14:30:02'
UTC_DATE()	Current UTC date	SELECT UTC_DATE();	'2025-05-21'

---

🧮 2. Date Arithmetic Functions

Function	Description	Example	Result
DATE_ADD()	Add interval to date	DATE_ADD('2025-05-01', INTERVAL 7 DAY)	'2025-05-08'
DATE_SUB()	Subtract interval from date	DATE_SUB('2025-05-10', INTERVAL 5 DAY)	'2025-05-05'
DATEDIFF()	Days between dates	DATEDIFF('2025-06-01', '2025-05-01')	31
TIMESTAMPDIFF()	Difference in specified unit	TIMESTAMPDIFF(YEAR, '2000-01-01', CURDATE())	25

---

📊 3. Date Part Extraction Functions

Function	Description	Example	Output
YEAR()	Extract year	YEAR('2025-05-21')	2025
MONTH()	Extract month (1–12)	MONTH('2025-05-21')	5
DAY() / DAYOFMONTH()	Extract day	DAY('2025-05-21')	21
HOUR()	Extract hour	HOUR('2025-05-21 13:45:00')	13
MINUTE()	Extract minute	MINUTE('2025-05-21 13:45:00')	45
SECOND()	Extract second	SECOND('2025-05-21 13:45:30')	30
WEEKDAY()	Day of week (0=Monday)	WEEKDAY('2025-05-21')	2

---

🎨 4. Date Formatting & Conversion

Function	Description	Example	Output
DATE_FORMAT()	Format date/time as string	DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i')	'2025-05-21 14:30'
STR_TO_DATE()	Parse string to date	STR_TO_DATE('21-05-2025', '%d-%m-%Y')	'2025-05-21'
UNIX_TIMESTAMP()	Converts date/time to UNIX timestamp	UNIX_TIMESTAMP('2025-05-21 14:30:00')	1747789800
FROM_UNIXTIME()	Converts UNIX timestamp to datetime	FROM_UNIXTIME(1747789800)	'2025-05-21 14:30:00'

---

⏱️ 5. Time Duration Functions

Function	Description	Example	Output
TIMEDIFF()	Difference between 2 times (HH:MM:SS)	TIMEDIFF('14:30:00', '12:00:00')	'02:30:00'
ADDTIME()	Add time to datetime	ADDTIME('2025-05-21 12:00:00', '02:30:00')	'2025-05-21 14:30:00'
SUBTIME()	Subtract time from datetime	SUBTIME('14:30:00', '01:00:00')	'13:30:00'

---

📘 Practical Examples

---

🔎 1. Users Signed Up This Month

SELECT * FROM users
WHERE MONTH(created_at) = MONTH(CURDATE())
  AND YEAR(created_at) = YEAR(CURDATE());

---

📈 2. Orders Placed in the Last 7 Days

SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

---

📅 3. Group Sales by Month

SELECT DATE_FORMAT(order_date, '%Y-%m') AS order_month, SUM(total) AS monthly_sales
FROM orders
GROUP BY order_month;

---

⏳ 4. Calculate Age of a User

SELECT name, TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age
FROM users;

---

📘 Best Practices for Date & Time in MySQL

✅ Practice	🔍 Tip
Use DATE_FORMAT() for reporting	Format timestamps for readability
Use indexed datetime columns	Avoid wrapping functions in WHERE clause on them
Avoid storing time in VARCHAR formats	Use DATETIME, DATE, TIMESTAMP data types
Use UTC_TIMESTAMP() for global apps	Standardize times across time zones
Validate date input with STR_TO_DATE()	Ensure consistent formats during import

---

🚀 Real-World Use Cases

Industry	Use Case	Example Function
🛒 E-commerce	Track orders per day/week/month	DATE_FORMAT(), GROUP BY
🧾 Invoicing	Calculate payment due dates	DATE_ADD()
📅 HR System	Calculate age, tenure, leave balances	TIMESTAMPDIFF()
📊 Analytics	Compare user activity over time	DATEDIFF(), CURDATE()
🔔 Reminders	Trigger alerts before expiry dates	DATE_SUB(), NOW()

---

📌 Summary – Recap & Next Steps

MySQL’s Date and Time functions are essential tools for data manipulation, filtering, reporting, and scheduling. They simplify time-based logic, help with formatting, and improve the performance of time-sensitive applications.

🔍 Key Takeaways

• Use NOW(), CURDATE(), DATE_FORMAT() to manage time flexibly
• Perform calculations using DATEDIFF, DATE_ADD, and TIMESTAMPDIFF
• Use STR_TO_DATE to convert strings into valid dates
• Format output for user-facing reports using DATE_FORMAT

⚙️ Real-World Relevance

Time-sensitive operations like scheduling, logging, analytics, billing, and real-time alerts rely heavily on MySQL’s date and time functionality.

---

❓ FAQ – MySQL Date & Time Functions

---

❓ What’s the difference between NOW() and SYSDATE()?

✅ NOW() returns a consistent timestamp for the session,
✅ SYSDATE() returns the actual time at execution—useful for precision timing.

---

❓ How can I get the first or last day of a month?

-- First day
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01');

-- Last day
SELECT LAST_DAY(CURDATE());

---

❓ How do I calculate days between two timestamps?

SELECT DATEDIFF('2025-06-01', '2025-05-01'); -- returns 31

---

❓ How to format datetime as ‘DD/MM/YYYY’?

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y'); -- '21/05/2025'

---

❓ How to convert UNIX timestamp to readable date?

SELECT FROM_UNIXTIME(1747789800);

---

Share Now :