Timestamp Interval

Question

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
2012-12-26 0:00:01
2012-12-20 0:00:02
2012-12-21 0:00:03

I would like the following output:

2012-12-27 1:00:00
2012-12-27 2:00:00

Let me know if this is unclear.

Answer 1

just use CURDATE(). eg

SELECT *
FROM tableName
WHERE DATE(s_timestamp) = CURDATE()

• DATE()
• CURDATE()

Answer 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(), INTERVAL 1 DAY)

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.