Overview: MySQL Last 30 Days

Querying data by time interval is a frequent task in MySQL, the widely used open-source relational database management system. This guide focuses on extracting data from the last 30 days, a common requirement for reports and data analysis.

Understanding Date and Time Functions in MySQL

MySQL provides several functions to work with date and time values. The CURDATE() and NOW() functions are particularly useful for working with the current date and time. To query data from the last 30 days, combine them with the DATE_SUB() function.

Querying Data from the Last 30 Days

To fetch records from the last 30 days, use the DATE_SUB() function in your WHERE clause. This function subtracts a specified time interval from a date.

SELECT *
FROM your_table
WHERE your_date_column >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Replace your_table with your table name and your_date_column with the column that holds the date.

Handling Time with NOW() and CURDATE()

There’s a subtle difference between NOW() and CURDATE() in MySQL:

• NOW() returns the current date and time.
• CURDATE() returns only the current date.

Choose the one that fits your data and requirements. For precise time calculations, use NOW().

SELECT *
FROM your_table
WHERE your_date_column >= DATE_SUB(NOW(), INTERVAL 30 DAY);

Accounting for Time Zones

When dealing with multiple time zones, consider converting your dates to UTC or using the CONVERT_TZ() function for accurate results.

SELECT *
FROM your_table
WHERE CONVERT_TZ(your_date_column, 'System', 'UTC') >= DATE_SUB(UTC_DATE(), INTERVAL 30 DAY);

Using BETWEEN for Range Queries

You can also use the BETWEEN operator to query data within a specific range, such as between two dates.

SELECT *
FROM your_table
WHERE your_date_column BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE();

Tips for Performance Optimization

• Ensure that the date column used in the WHERE clause is indexed.
• For large datasets, consider narrowing down the query with additional conditions to reduce the result set.

Basedash is built as an AI-native BI platform, so teams can go from ad hoc SQL to trusted answers and dashboards quickly, without the overhead of traditional BI setup.

With MySQL’s date and time functions, you can pull the last 30 days of data quickly for reports and analysis.