Mysql - search by label by hour of the day

Question

I have a column called updatetime , which is a timestamp . So, for example, the weighted average value can be: 2011-02-01 09:00:51 . I want to be able to search and return all results for a particular hour of the day regardless of the date.

For example, if I were looking for a column for BETWEEN 09:00:00 AND 09:59:99 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 can use the HOUR () function:

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

Alas, this query performance will be terrible as soon as you go to a few thousand rows - the functions are not indexed, so every time this query is run, a full table scan will be performed.

What we did in a similar situation: we created another updatetime_hour column, indexed it and filled it in (and updated it when updating); then the request becomes fast:

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

Yes, we have denormalized the data and this is a bit more of a household, but I have yet to see a faster solution. (We reviewed and measured insert and update triggers to populate updatetime_hour from updatetime , but decided against performance, see if they are useful to you.)

Answer 2

Try HOUR() :

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

This will return all rows from 09:00:00 to 09:59:59.

HOUR() just returns the hour of the date.

Answer 3

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

Will be back 9 am ..

Answer 4

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

Answer 5

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

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