Select records from today, this week, this month php mysql

Select records from today, this week, this month php mysql

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

Stefan Bogdanescu

Founder & Senior Architect

Selecting Time-Bound Records in MySQL Without Hardcoding Dates

A common requirement for dashboards and reporting pages is filtering database records by relative timeframes: "today," "this week," or "this month." The source material presents a specific scenario where date values are stored in the jokes table using the format Y-m-d H:i:s. The user attempts to solve this by calculating offsets from the current moment—subtracting 24 hours for today, 168 hours (7 days) for a week, and 720 hours (30 days) for a month.

While the logic of subtracting time intervals seems sound at first glance, there is a critical flaw in the proposed SQL queries that will prevent them from returning the expected data. The issue lies in how MySQL handles the NOW() function versus the string literal "date".

In the provided examples, the query uses WHERE date>(date-(60*60*24)). Here, the column name is date, but the value being subtracted from is also treated as a column reference named date. Unless there exists a second column explicitly named date that holds the current timestamp, MySQL will attempt to subtract the stored date values from themselves, resulting in zero or invalid comparisons. To filter by "today," you must compare the record's timestamp against the actual current time.

Using NOW() for Relative Calculations

The correct approach involves calling the NOW() function within your SQL statement. This function returns the current date and time at the moment the query executes, allowing you to perform arithmetic on it directly. You can subtract a number of seconds from NOW() using standard MySQL date arithmetic.

For filtering records from "today," you need to subtract exactly 86,400 seconds (24 hours). The query should look like this:

SELECT * FROM jokes 
WHERE DATE(NOW()) = DATE(created_at) -- Or comparing timestamps directly if granularity is needed
-- OR strictly time-based:
WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY score DESC;

Note the switch from date to created_at. While the source material uses date, standard convention in many applications reserves date for schema definitions, so using a descriptive column name like created_at or inserted_at is often clearer. If your column is actually named date, ensure you don't confuse it with the SQL keyword unless quoted.

The "week" query follows the same pattern but subtracts 7 days:

SELECT * FROM jokes 
WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY score DESC;

Similarly, for "this month," assuming a standard 30-day lookback as requested in the source material:

SELECT * FROM jokes 
WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY score DESC;

Handling Month Length Variability

The source material specifies using a fixed 30-day window for "this month." This is a pragmatic approach for simple reporting, but it introduces a specific trade-off: February will only return roughly 28 or 29 days of data, while April might include March's records depending on the execution time. If your business logic requires capturing all records from the current calendar month regardless of how many days have passed since the start, a fixed 30-day offset is insufficient.

In that case, you would need to extract the current year and month:

SELECT * FROM jokes 
WHERE YEAR(created_at) = YEAR(NOW()) 
  AND MONTH(created_at) = MONTH(NOW())
ORDER BY score DESC;

This ensures that even if it is January 2nd, you capture records from December of the previous year only if the logic allows, but typically this specific query restricts results to the current calendar month exactly. However, this approach can suffer from performance issues on large tables because YEAR() and MONTH() functions prevent index usage on the date column. If your table is large, consider partitioning by year or keeping a separate metadata column for the month number if frequent filtering is expected.

Best Practices for Date Columns

When designing these queries, always ensure your timestamp columns are indexed. Comparisons like created_at > DATE_SUB(NOW(), ...) can utilize indexes on created_at, but functions applied to the column in the WHERE clause might not. Since DATE_SUB() operates on NOW() rather than the column itself, standard B-tree indexes on created_at will work efficiently for range queries.

Avoid using string concatenation or manual second calculations like 60*60*24 inside the SQL unless necessary; use INTERVAL keywords. They are more readable and less prone to arithmetic errors. For example, DATE_SUB(NOW(), INTERVAL 1 DAY) is self-documenting compared to (NOW() - (60*60*24)).

If you are building this application using Laravel, the underlying SQL generation remains consistent with these principles. You can pass raw SQL strings or use query builders that handle these intervals gracefully. For more complex date filtering needs in a Laravel context, consider utilizing the Date facade or creating helper methods to generate these ranges dynamically based on the current session's timezone, ensuring consistency between server time and user expectations.

Our network