How to Retrieve Records with Today's Timestamp in MySQL?

How to Select Rows with Current Day's Timestamp

In database management systems, it's common to store timestamps for data records to track when they were created or modified. However, when you need to retrieve data from a table with timestamps, you might encounter challenges in selecting records based on specific dates.

Problem Statement

You have a database table with a timestamp column, and you want to retrieve only records that were created on the current day. You initially used the query below but noticed that it returns records for the last 24 hours, not just today's date:

SELECT * FROM `table` WHERE (`timestamp` > DATE_SUB(now(), INTERVAL 1 DAY));

Solution

To select rows that have the current day's timestamp while ignoring time, you can use the following query:

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

• The DATE() function returns the date part of a timestamp, removing the time information.
• The CURDATE() function returns the current date as a MySQL date value.

Warning:

While this query achieves your intended result, it may not perform optimally. Using DATE() on a timestamp column can prevent MySQL from efficiently using any indexes on that column.

For a more efficient solution, you can create a dedicated date column and store the date part of the timestamp in that column. This allows MySQL to use an index on the date column for faster querying.

Here's an example of how you can create a dedicated date column and update its values:

ALTER TABLE `table` ADD COLUMN `date` DATE NOT NULL;
UPDATE `table` SET `date` = DATE(`timestamp`);

The above is the detailed content of How to Retrieve Records with Today's Timestamp in MySQL?. For more information, please follow other related articles on the PHP Chinese website!