sql - mysql - search timestamp by hour of day

Question

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 able 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?

Answer 1

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.)