MySQL select rows from exactly 7 days ago

MySQL select rows from exactly 7 days ago

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Why edit_date = DATE_SUB(NOW(), INTERVAL 7 DAY) Returns Empty Sets

You are encountering a classic temporal mismatch in SQL. Your query logic assumes that your data is stored as a single, static point in time relative to the current moment, but the behavior of MySQL's datetime functions and the nature of daily batch processing create a gap that renders an exact equality check ineffective.

When you execute edit_date = DATE_SUB(NOW(), INTERVAL 7 DAY), you are asking MySQL to find rows where the timestamp is identical to the result of subtracting exactly seven days from the current second. Even if you know there are entries from "yesterday" or "a week ago," a strict equality operator (=) will fail if the stored edit_date does not match the calculated timestamp down to the second.

The core issue lies in the precision of NOW(). This function returns the current date and time, including microseconds in newer MySQL versions, and it fluctuates every millisecond that your script runs. If a record was inserted at 2023-10-05 14:30:00 and your cron job runs at 2023-10-12 09:15:00, the calculated subtraction results in 2023-10-05 09:15:00. The query looks for a match against 2023-10-05 09:15:00, but your database holds 2023-10-05 14:30:00. These do not match.

Handling Temporal Granularity

To retrieve rows from exactly 7 days ago, you must account for the fact that records are likely stored with specific timestamps rather than a generic "date." If your goal is to process data that was created within that specific calendar day, ignoring the time component entirely is usually the pragmatic approach.

You can achieve this by truncating both sides of the comparison to the date level. By using DATE() on both the column and the calculation, you strip away hours, minutes, and seconds:

SELECT * 
FROM offers 
WHERE DATE(edit_date) = DATE_SUB(CURDATE(), INTERVAL 7 DAY)

Here, CURDATE() returns just the current date without the time component, and wrapping edit_date in DATE() ensures you are comparing apples to apples. This approach is robust for daily cron jobs because it treats any record created on that specific day as a match, regardless of whether the database was queried at midnight or noon.

If strict adherence to the exact second is required—perhaps for financial auditing where every microsecond counts—you would need to ensure your data ingestion process normalizes timestamps or your query window accounts for execution variance. However, for standard archival or batch processing tasks, date-level comparison is the standard industry practice.

The Trap of Exact Equality in Time-Series Data

A common mistake developers make when working with DATETIME columns is assuming that time-series data behaves like static integers. In a high-volume environment where you ingest 7,000 new entries daily, records are scattered across every minute and second of the day. An exact match query becomes increasingly brittle as the dataset grows because the probability of your cron job running at the exact same second the record was created approaches zero.

Consider the scenario where you run a script on Tuesday morning to find data from Friday. The calculation DATE_SUB(NOW(), INTERVAL 7 DAY) yields a timestamp based on Tuesday's time. Unless you have a record specifically updated or created at that exact moment, the query returns nothing. This is why your SELECT * works fine—you are fetching everything—but your filtered query fails because the filter condition is mathematically too narrow for the data distribution.

Alternative Approaches for Batch Processing

If your use case allows for a slight window of time, or if you need to handle timezone discrepancies between the application server and the database, consider using BETWEEN with a calculated range. This adds a buffer that ensures you capture records even if the cron job runs slightly late or if there is a UTC offset difference:

SELECT * 
FROM offers 
WHERE edit_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) + INTERVAL 0 HOUR 
                  AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) + INTERVAL 23 HOUR

While this widens the net, it effectively captures the entire previous day. If you strictly need "exactly" 7 days ago and your data represents a snapshot of that specific date, truncating to the date column remains the most reliable solution. It decouples the query logic from the volatile nature of server uptime and time zone configurations.

Final Recommendation

For daily cron scripts processing large volumes of data, stop attempting exact second-level matches on NOW(). Instead, normalize your comparison to the date level using DATE() or rely on a specific range that covers the full previous day. This aligns with how most ETL pipelines and batch jobs are architected, ensuring that 7,000 daily entries are reliably captured without relying on impossible timing coincidences.

Our network