️ 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

2. Date Arithmetic Functions

3. Date Part Extraction Functions

4. Date Formatting & Conversion

️ 5. Time Duration Functions

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

Real-World Use Cases

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);