Most efficient and practical way to store time values (HH:MM) in Oracle that makes it easily searchable
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