How to Query Date and Time in MySQL

When working with databases, handling date and time data is a critical skill. MySQL provides various built-in functions to retrieve, manipulate, and format date and time values efficiently. This guide covers the most common techniques for querying date and time data in MySQL.

1. Retrieving the Current Date and Time

To retrieve the current date and time in MySQL, you can use the NOW() function, which returns the current date and time based on the server's system clock.

SELECT NOW();

This will return the current date and time in the format YYYY-MM-DD HH:MM:SS.

2. Extracting Date and Time Components

If you want to retrieve only specific parts of a date or time, MySQL offers several functions:

• YEAR(date) - Extracts the year part of a date.
• MONTH(date) - Extracts the month part of a date.
• DAY(date) - Extracts the day part of a date.
• HOUR(time) - Extracts the hour part of a time.
• MINUTE(time) - Extracts the minute part of a time.
• SECOND(time) - Extracts the second part of a time.

For example, to extract the year from a timestamp:

SELECT YEAR(NOW());

3. Formatting Date and Time

MySQL provides the DATE_FORMAT() function, which allows you to format date and time values in various ways. The function takes two arguments: the date/time value and the format string. Here's an example:

SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y');

This will return a formatted date like Tuesday, May 8, 2025.

4. Filtering by Date and Time

You can filter records by date or time using the WHERE clause. Here's an example where we filter all records from the past week:

SELECT * FROM orders WHERE order_date > NOW() - INTERVAL 7 DAY;

This query retrieves all orders from the past 7 days.

5. Date and Time Arithmetic

MySQL allows you to perform arithmetic with dates and times, such as adding or subtracting time intervals. For example, you can add 3 days to the current date like this:

SELECT NOW() + INTERVAL 3 DAY;

This will return the date and time 3 days from the current moment.

6. Timezone Handling

MySQL stores dates and times in the server's timezone. If you need to convert a datetime to a specific timezone, use the CONVERT_TZ() function:

SELECT CONVERT_TZ(NOW(), 'UTC', 'America/New_York');

This will convert the current date and time from UTC to New York's timezone.

Conclusion

MySQL provides a wide range of functions for querying, manipulating, and formatting date and time values. By mastering these functions, you can efficiently work with time-related data in your database.