Informix SQL Compare DateTime value on Where Statement

Question

I have a datetime field called entrytimestamp, with content of the field is for example: 2014-01-07 16:20:00. I would like to query all the data that has entrytimestamp after 09:00:00 o'clock, regardless what date it was.

I have a prototype query:

select *
from trading
where to_char(entrytimestamp, "%H%M%S") >= "090000"

But I think it is logically a mistake, because it will compare the text string, not the sequence value. What is the right way to do it?

Answer 1

I dont know if it performs well, but you could compare directly with the time portion of the datetime,

i think a cast here should perform pretty fast (just cuts off the date)

select *

from (select getdate() as mydatetime) as data

where cast(mydatetime as time) > cast('09:00:00' as time)

EDIT, just noticed this was for Informix SQL, so not sure it works then, Sorry

Answer 2

Use the EXTEND() function to extract the time part:

select *
from mytable
where extend(entrytimestamp, hour to second) > '09:00:00'