How to store in the field only time, not date + time pair?

Question

In one field I need to store not the date + time pair

01/10/2009 22:10:39

But time only

22:10:39

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

Thanks in advance

Answer 1

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

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

Answer 2

You can extract the time 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 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.