query mysql: Select from timestamp

Question

I want to SELECT all the rows from the table that correspond to a specific date. I have the timestamp stored in the pattern 2010-08-18 04:43:00. How can I query the table if I want to select all the rows that fall within a day's time?

One way I can think of is get the day's timestamp, convert it into Unix timestamp and then query the table. But that sounds like a lot of work. Is there any easier alternative?

Thanks a lot in advance.

Answer 1

SELECT *
FROM `table_name`
WHERE `date_column` LIKE '2010-08-17 %';

Or:

SELECT *
FROM `table_name`
WHERE DAY( `date_column` ) = 17
AND MONTH( `date_column` ) = 08
AND YEAR( `date_column` ) = 2010
LIMIT 0 , 30

Reference: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Answer 2

The approach outlined by Michael works, but is string-based and as such not as efficient as an integer-based search.

I don't really see a problem in creating two UNIX timestamps, one for 00:00:00 and one for 23:59:59, and checking to see if you fall within that time. (Be sure to actually calculate these two separate values to make sure you account for daylight savings time).

You can even use MySQL to get those values if you really don't want to do it yourself (SELECT UNIX_TIMESTAMP("$Timestamp 00:00:00"), UNIX_TIMESTAMP("$Timestamp 23:59:59")), and then use those two values.

If you have a small dataset, Michael's approach above is fine.

Answer 3

Assuming datetime data type this seems to work?

SELECT Datecol
FROM your_table
WHERE Datecol >= cast('2010-08-18' as date)
    AND Datecol < cast('2010-08-19' as date)

Answer 4

Quite simply:

SELECT *
FROM `table_name`
WHERE DATE(`date_column`) = '2010-08-17';

Note that it will only be efficient if your date_column is of type TIMESTAMP or DATETIME.

Edit: Since Martin raised a point related to performance, you might want to try this instead if speed is an issue (on a huge data set). BETWEEN will make use of any available indexes.

SELECT *
FROM `table_name`
WHERE `date_column` BETWEEN '2010-08-17 00:00:00' AND '2010-08-17 23:59:59';