Most efficient and practical way to store time values (HH:MM) in Oracle that makes it easily searchable I have a set of starting and ending times that I need to store in an Oracle database.  I need to store them in a manner that makes them easily searchable (i.e. a typical search is find all rows where a time value, such as 9:30AM, falls in between the start time and end time).  In SQL Server I would just use a TIME datatype field, but Oracle does not appear to have an equivalent.  

I have seen a number of blogs and forums that recommend just using a DATE field and doing conversions with TO_CHAR then comparing, or to store the time values as varchar(4 / HHMM) or varchar(6 / HHMMSS) fields.  Both of these seem needlessly inefficient.  

Is there a better or more efficient way to accomplish this in Oracle?
If you've no need to store the date component, then an interval day(0) to second is the most practical approach. The biggest advantage comes if you need to do any calculations finding how long has elapsed between two times as you don't need to do any extra processing out of the box.

For example:

create table times ( t1 interval day(0) to second, t2 interval day(0) to second);

insert into times values ( interval '0 09:30:00' day(0) to second, interval '0 12:30:00' day(0) to second);
insert into times values ( interval '0 10:30:00' day(0) to second, interval '0 13:45:00' day(0) to second);
insert into times values ( interval '0 11:30:00' day(0) to second, interval '0 12:05:01' day(0) to second);

commit;

SELECT * FROM times
where  t1 between interval '0 10:00:00' day to second and interval '0 11:00:00' day to second ;

T1          T2        
----------- -----------
0 10:30:0   0 13:45:0   

SELECT max(t2-t1) FROM times; 

MAX(T2-T1)
-----------
0 3:15:0.0  


You also have standard functions to extract the various components of the time (hour, minute, etc.):

SELECT extract(hour from t1) hr, extract(minute from t1) mn, extract(second from t1) sec
FROM times; 

        HR         MN        SEC
---------- ---------- ----------
         9         30          0 
        10         30          0 
        11         30          0