oracle insert only time

Question

I would like to insert data to db as time, not date. If I use to_date('2012-08-31 07:39:33', 'YYYY-MM-DD HH24:MI:SS') it adds date too. If I use to_date('09:34:00', 'HH24:MI:SS') it adds year, month, day as well, from nowhere :| Later I need to get rows where time is between x and y, not taking in account the year, month or day. How do I do that? thanks

Answer 1

As an alternative to the date solution Dave shows, you could use an interval data type for the column:

create table t42(id number, t interval day to second);

insert into t42 (id, t) values(123, to_dsinterval('0 07:39:33'));
insert into t42 (id, t) values(456, to_dsinterval('0 09:34:00'));

select id
from t42
where t between to_dsinterval('0 07:00:00') and to_dsinterval('0 07:59:59');

    ID
----------
       123

Displaying intervals is a little awkward as they don't have format models, but see this question for some ideas if needed. If you only use them for filtering then that may not be an issue at all.