How to Retrieve Rows with Timestamps from Today Only in SQL?

How to Select Rows with Timestamps from Today Only

In a relational database, selecting rows with specific timestamps can be essential for data analysis and reporting. A common scenario is to retrieve records from the current day, excluding any time component.

The Current Approach and Its Limitation

The provided query, SELECT * FROM table WHERE (timestamp > DATE_SUB(now(), INTERVAL 1 DAY));, retrieves results within the last 24 hours. This is because it subtracts one day from the current time using DATE_SUB, which includes all timestamps from the previous day.

Using DATE and CURDATE() for Precision

To select results based solely on the date, ignoring the time component, the following query can be used:

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

Here, DATE() extracts only the date portion of the timestamp, and CURDATE() returns the current date. By comparing these values, the query effectively filters out timestamps that are not from today.

Efficient Index Usage

It's important to note that the above query may not utilize indexes efficiently. Consider using this alternative query instead:

SELECT * FROM `table` WHERE `timestamp` >= DATE(NOW()) AND `timestamp` < DATE(NOW()) + INTERVAL 1 DAY;

This query utilizes an index on the timestamp column, which is a common optimization technique in SQL.

The above is the detailed content of How to Retrieve Rows with Timestamps from Today Only in SQL?. For more information, please follow other related articles on the PHP Chinese website!