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 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 2

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

SELECT to_char( SYSDATE,  SSSSS  ) FROM dual;

Answer 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.

Answer 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

Answer 5

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)

友情链接