Timestamp Interval

I have a column called 's_timestamp.' How can I return all the records that have the current day in the timestamp? For example, s_timestamp 2012-12-27 1:00:00 2012-12-27 2:00:00 20

Solution 1:

just use CURDATE(). eg

SELECT*FROM tableName
WHEREDATE(s_timestamp) = CURDATE()

• DATE()
• CURDATE()

Solution 2:

This may be more efficient than casting the timestamps to DATE, especially if you have an index on the timestamp column (which you should have):

SELECT*FROM tableName
WHERE s_timestamp >= CURDATE()

or, if you want to exclude any future dates:

SELECT*FROM tableName
WHERE s_timestamp >= CURDATE()
  AND s_timestamp < DATE_ADD(CURDATE(), INTERVAL1DAY)

This works because, when a DATETIME or a TIMESTAMP is compared with a DATE, the DATE is, in effect, interpreted as having a time part of 0:00:00.