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.

Solution 1

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.

Solution 2

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

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

Solution 3

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.

Solution 4

you can use:

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

example

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

for time format you can check in here

View more solutions