How to store only time; not date and time?

Question

In one field I need to store not a datetime pair, i.e. a standard Oracle date.

01/10/2009 22:10:39

But time only

22:10:39

I think that save disk space (I have 2 million rows) or provide faster processing.

Answer 1

You can extract the time from a date as a string like this:

to_char(sysdate,'HH.MI.SS')

but there is no time-only data type that will help you save space.

Answer 2

you can use:

TO_CHAR(<DATE_COLUMN>, '<TIME_FORMAT>');

example

TO_CHAR(SYSDATE, 'HH24:MI:SS');

for time format you can check in here

Answer 3

You would save a few Mb of disk space(which is nothing nowadays) and you would gain next to nothing in performance.

You could use a column of NUMBER type for storing the number of seconds since midnight as suggested, just don't forget about the constraints.
(You'd probably use NUMBER(5, 0) which uses 1-3 bytes depending on the stored value, instead of a constant 7 bytes used by a DATE column)

Answer 4

You could try the INTERVAL DAY TO SECOND data type but it won't save you any disk space ... it is very suitable for this purpose though.

create table t1 (time_of_day interval day (0) to second(0));

insert into t1 values (TO_DSINTERVAL('0 23:59:59'));

select date '2009-05-13'+time_of_day
from   t1;

11 bytes though.

Answer 5

Your best bet would probably be storing "seconds since midnight" as a number field.

SELECT to_char( SYSDATE, 'SSSSS' ) FROM dual;