When working with time-sensitive data — such as user activity logs, sales records, or system logs — it’s often necessary to retrieve records from the last month or year relative to today’s date.

This guide will walk you through how to retrieve these recent records accurately and efficiently in MySQL.

Use Case

You have a MySQL table (e.g., orders, users, logs) and want to:

• Fetch all rows from the last 30 days (relative to current date).

• Fetch all rows from the last 365 days (or last calendar year).

Example Table: orders

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255),
    total DECIMAL(10, 2),
    order_date DATE
);

Method 1: Get Records from the Past One Month (Last 30 Days)

✅ Step-by-Step

1. Use CURDATE() to get today’s date.

2. Subtract 1 month using INTERVAL 1 MONTH.

3. Use the >= comparison to filter results.

SQL Query

SELECT * 
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 MONTH;

This returns orders from today minus 30 days up to today.

Example: If today is 2025-05-04, the query returns rows with order_date >= 2025-04-04.

Method 2: Get Records from the Past One Year (Last 365 Days)

SELECT * 
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 YEAR;

This returns records from 365 days ago to today.

Optional: Include Time Component (for DATETIME fields)

If your table uses DATETIME instead of DATE, use NOW() instead of CURDATE():

SELECT * 
FROM orders
WHERE order_date >= NOW() - INTERVAL 1 MONTH;

Full Working Example

✅ Create Table & Insert Sample Data

-- Create table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255),
    total DECIMAL(10,2),
    order_date DATE
);

-- Insert sample records
INSERT INTO orders (customer_name, total, order_date) VALUES
('Alice', 100.00, '2024-12-25'),
('Bob', 250.00, '2025-03-10'),
('Charlie', 180.00, '2025-04-04'),
('Diana', 300.00, '2025-04-28'),
('Eve', 120.00, CURDATE());

✅ Query Last 30 Days

SELECT * 
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 MONTH;

✅ Query Last 1 Year

SELECT * 
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 YEAR;

Tips

• ✅ Use Indexes on Date Columns: Greatly improves performance for large datasets.

• ✅ Use CURDATE() for DATE fields and NOW() for DATETIME fields.

• ✅ Use Parameterized Queries in your application layer to avoid SQL injection.

• ✅ Test With Different Dates: Use SET @today = '2025-05-04'; to simulate different dates.

⚠️ Common Pitfalls

• ❌ Using MONTH(order_date) = MONTH(CURDATE()): This fetches records from the same month across all years, not the last 30 days.

  Example:

  WHERE MONTH(order_date) = MONTH(CURDATE()) -- Wrong!
  

• ❌ Forgetting to Compare with a Full Date Range: Simply filtering by month or year won't give accurate “last X days” range.

• ❌ Using Non-Indexed Date Columns: Can lead to full table scans and slow performance.

Bonus: Using Prepared Statements in PHP (Sample)

$pdo = new PDO("mysql:host=localhost;dbname=testdb", "root", "");
$stmt = $pdo->prepare("SELECT * FROM orders WHERE order_date >= CURDATE() - INTERVAL 1 MONTH");
$stmt->execute();
$orders = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($orders as $order) {
    echo $order['customer_name'] . " - " . $order['order_date'] . "<br>";
}

Conclusion

Using INTERVAL with CURDATE() or NOW() allows you to dynamically retrieve recent records without hardcoding dates. Whether you're creating a dashboard, analytics tool, or scheduled report, this method is simple, efficient, and reliable.