oracle insert only time [duplicate]

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.

Answer 2

A DATE type always includes the date component.

One option is to continue using DATE and write your code to ignore the date component. In order to make queries on the time efficient, you might want to create a function-based index on something like TO_CHAR( date_field, 'HH24:MI:SS' ) and use that expression in your queries.

Alternatively, you could use a NUMBER field to store the number of seconds since midnight, and write your queries in terms of that.