How can I filter a query by the hour portion of a DateTime field in MySQL?

Question

I need to select rows from table, where e.g. time is >= 18:00:00 no matter of date. Problem is that value is datetime type so there is also date beside. e.g. 2012-01-25 18:00:00.

table1
======
row 1:   id='1'   datetime='2012-01-25 18:00:00'
row 2:   id='2'   datetime='2012-01-27 15:00:00'
row 3:   id='3'   datetime='2012-01-30 19:45:00'

I need to select row 1 and row 3.

Is there way to combine LIKE and >= to do time >= '% 18:00:00' where % represents whatever date?

Answer 1

You can use the TIME() function:

WHERE TIME(`datetime`) >= '18:00:00'