3

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.

Sohel Mansuri
  • 564
  • 2
  • 6
  • 18
  • Is the timestamp a `TIMESTAMP`? – Waleed Khan Dec 28 '12 at 00:24
  • @WaleedKhan, Yes it is. Let me edit the question. – Sohel Mansuri Dec 28 '12 at 00:26
  • I would have to agree with the answer given by JW. It also allows you to obtain the info for any date by simply changing "CURDATE" in the query to whatever date you may want to obtain data for. (ie. CURDATE, or 2012-11-23, or 2012-12-24, or whatever date you want to query) – Max West Dec 28 '12 at 00:30

2 Answers2

3

just use CURDATE(). eg

SELECT *
FROM tableName
WHERE DATE(s_timestamp) = CURDATE()
John Woo
  • 258,903
  • 69
  • 498
  • 492
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.

Ilmari Karonen
  • 49,047
  • 9
  • 93
  • 153
  • 1
    yes, `DATE` doesn't use index. but the condition `WHERE s_timestamp >= CURDATE()` will select all days from today and greater than it. `WHERE s_timestamp BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 1 DAY)` will select today and tomorrow. – John Woo Dec 28 '12 at 00:41
  • 1
    Ah, I see, the upper bound is off by one second. Let me fix that... [there you go.](http://www.sqlize.com/bJb2mNF7ds) – Ilmari Karonen Dec 28 '12 at 00:46
  • 1
    This may not just be more efficient. It will definitely be more efficient, and much more efficient if there is an index on `s_timestamp`. – ypercubeᵀᴹ Dec 28 '12 at 00:52