How to Efficiently Select Rows with Today's Timestamp in a Database?

Selecting Rows with Today's Timestamp

In a database, it's often essential to retrieve records based on specific time periods. One common scenario is selecting records that have today's timestamp. This article presents a solution to this specific problem.

The question raised concerns selecting records from a database table where the timestamp field contains today's date, irrespective of the time. The provided query uses DATE_SUB() to subtract 24 hours from the current time and retrieves results for the last 24 hours.

To select results based solely on the date, we can leverage the DATE() and CURDATE() functions. Here's the modified query:

SELECT * FROM `table` WHERE DATE(`timestamp`) = CURDATE()

In this query, DATE() extracts the date component from the timestamp field, discarding the time information. CURDATE() returns the current date, ensuring we only retrieve records for today.

However, it's important to note that this query may not utilize indexes efficiently. For a more optimized solution, consider the following query, which uses an indexed date column:

SELECT * FROM `table` WHERE `date` = DATE(NOW())

As this query directly compares the date column with the current date, it can leverage an index on the date column for faster execution.

The above is the detailed content of How to Efficiently Select Rows with Today's Timestamp in a Database?. For more information, please follow other related articles on the PHP Chinese website!