mysql - search timestamp by hour of day

I have a column named updatetime that is a timestamp. So, for example, an average looking value could be: 2011-02-01 09:00:51. I want to be abl开发者_C百科e to search through and return all results for a particular hour of the day regardless of the date.

For example if I searched the column for values BETWEEN 09:00:00 AND 09:59:99 it would return:

2011-02-01 09:00:51
2011-01-31 09:20:51
2011-01-11 09:55:44
etc....

SELECT * FROM table WHERE updatetime ......

Thoughts?

You could use the HOUR() function:

SELECT * FROM 'table' WHERE HOUR(`updatetime`) = 9

Alas, this query's performance will be horrible, as soon as you go over a few thousand rows - functions aren't indexable, so there will be a full table scan each time this query runs.

What we did in a similar situation: we created another column updatetime_hour, indexed it, and populated it on insert (and updated on update); then the query becomes fast:

SELECT * FROM 'table' WHERE `updatetime_hour` = 9

Yes, we have denormalized the data, and it's a bit more housekeeping, but I have yet to see a faster solution. (We considered and measured insert and update triggers to populate the updatetime_hour from updatetime, but decided against for performance; see if they would be useful for you.)

Try with HOUR():

SELECT * FROM table WHERE HOUR(updatetime) = 9;

This will return all rows between 09:00:00 and 09:59:59.

The HOUR() just returns the hour part of the date.

SELECT * FROM table WHERE hour('updatetime') = 9;

Would return 9am..

Possibility: SELECT * FROM 'table' WHERE HOUR(updatetime)='09';

These answers do not work, this is what worked for me only

SELECT hour(FROM_UNIXTIME(`updatetime`)) AS date_formatted FROM `table` where hour(FROM_UNIXTIME(`updatetime`))='9'