MySQL 8: How to select rows from the last 7 days, 30 days, etc.

Overview

Working with dates in databases is a common requirement for many applications, from generating reports to analyzing trends. MySQL 8 provides robust date and time functions that make querying data within certain time frames, like the last 7 or 30 days, rather straightforward. This tutorial will walk you through the process of selecting rows based on date ranges using MySQL 8.

Before diving into the examples, make sure that you have MySQL 8 installed and that you can connect to a MySQL database. We will use a sample table called orders for all our examples. Assume the table structure is as follows:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    order_date DATETIME
);

Selecting Rows from the Last 7 Days

Let’s start with a basic query that selects all rows from the orders table that were created in the last 7 days. The MySQL function CURDATE() can be used to get the current date, and we can subtract an interval of 7 days to get the date one week ago.

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

This query will return all the orders that have an order_date from the current date minus 7 days.

Selecting Rows from the Last 30 Days

Similar to the previous example, you can modify the interval to select rows from the last 30 days.

SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY;

It’s that simple. However, what if you want to be more precise and include the exact time for the last 30 days? You would then use the NOW() function which returns both the current date and time.

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

Advanced Date Range Selection

What if your requirements are more specific? For example, selecting all the rows created on the last weekend, or on the last Monday? MySQL’s powerful date functions like DAYOFWEEK() make this pretty straightforward.

SELECT * FROM orders
WHERE order_date >= CURDATE() - INTERVAL 7 DAY
AND DAYOFWEEK(order_date) IN (1,7);

This query selects all the rows where order_date is within the last 7 days and is either a Sunday (1) or a Saturday (7) according to the MySQL DAYOFWEEK() function.

Using Temporary Tables for Complex Queries

In more advanced scenarios, you might need to create a temporary table that holds the time criteria and then join it with your original table. This allows you to have more readable and maintainable code.

CREATE TEMPORARY TABLE IF NOT EXISTS date_criteria AS (
    SELECT CURDATE() - INTERVAL n DAY AS date_n
    FROM (SELECT a.N + b.N * 10 + 1 n FROM 
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ) a
    WHERE n <= 30
);

SELECT o.*
FROM orders o
JOIN date_criteria dc ON o.order_date >= dc.date_n;

This strategy separates the logic of date calculations from the main query and can be beneficial if you have multiple or complex date conditions.

Using TimeZone Conversions

Sometimes, selecting by date can be complicated by time zones. MySQL 8 provides time zone functions that allow you to accurately query data regardless of server, database, or client time zones.

SET time_zone = '+00:00';
SELECT CONVERT_TZ(order_date, @@session.time_zone, '+00:00'), *
FROM orders
WHERE order_date >= CONVERT_TZ(NOW() - INTERVAL 7 DAY, '+00:00', @@session.time_zone);

By converting the dates to a standard time zone (UTC in this case), you can ensure consistent results across different localized configurations.

Conclusion

In this tutorial, we’ve explored a range of techniques for selecting rows from the last 7 to 30 days using MySQL 8. We’ve seen how MySQL’s date and time functions can be adapted for both simple and advanced use cases. Understanding these functions enables you to manipulate and analyze temporal data effectively, providing valuable insights for your applications.