Most efficient and practical way to store time values (HH:MM) in Oracle that makes it easily searchable

oracle

I have a set of starting and ending times that I need to store in an Oracle database. I need to store them in a manner that makes them easily searchable (i.e. a typical search is find all rows where a time value, such as 9:30AM, falls in between the start time and end time). In SQL Server I would just use a TIME datatype field, but Oracle does not appear to have an equivalent.

I have seen a number of blogs and forums that recommend just using a DATE field and doing conversions with TO_CHAR then comparing, or to store the time values as varchar(4 / HHMM) or varchar(6 / HHMMSS) fields. Both of these seem needlessly inefficient.

Is there a better or more efficient way to accomplish this in Oracle?

Best Answer

If you've no need to store the date component, then an interval day(0) to second is the most practical approach. The biggest advantage comes if you need to do any calculations finding how long has elapsed between two times as you don't need to do any extra processing out of the box.

For example:

create table times ( t1 interval day(0) to second, t2 interval day(0) to second);

insert into times values ( interval '0 09:30:00' day(0) to second, interval '0 12:30:00' day(0) to second);
insert into times values ( interval '0 10:30:00' day(0) to second, interval '0 13:45:00' day(0) to second);
insert into times values ( interval '0 11:30:00' day(0) to second, interval '0 12:05:01' day(0) to second);

commit;

SELECT * FROM times
where  t1 between interval '0 10:00:00' day to second and interval '0 11:00:00' day to second ;

T1          T2        
----------- -----------
0 10:30:0   0 13:45:0   

SELECT max(t2-t1) FROM times; 

MAX(T2-T1)
-----------
0 3:15:0.0

You also have standard functions to extract the various components of the time (hour, minute, etc.):

SELECT extract(hour from t1) hr, extract(minute from t1) mn, extract(second from t1) sec
FROM times; 

        HR         MN        SEC
---------- ---------- ----------
         9         30          0 
        10         30          0 
        11         30          0

Related Solutions

Most efficient way to perform table operations (spec. drop) on tables having table names that match a certain criteria

Why does the code need to be more efficient? It seems unlikely that you would be dropping large numbers of tables frequently or that it would take enough time to drop tables that it would be worth optimizing.

What trade-offs are you willing to make to cause the code to be more efficient? And what do you mean by "efficient" in this context-- wall clock time? The resources consumed on the server? Something else? The code that you have written is very straightforward and easy to follow. You could complicate things by, for example, submitting multiple jobs that each drop a subset of the tables so that you can have multiple threads working simultaneously. But that would involve making the code much more complicated-- you'd need to assign work to different threads and then coordinate the responses from each thread to verify that all the tables were dropped successfully. It seems most unlikely that this would be a trade-off that you would really want to make though it would reduce the wall clock time needed to drop all the tables.

Oracle: Is there a way to put exception handling into a function and just reference that function inside procedures

Well, you could have a procedure that checked for various values of sqlcode or sqlerrm and performed whatever actions you have in mind based on those, and called that procedure from your exception handler passing the filename and directory object name. However, I don't think that would be a neat solution.

If the problem is trying to reproduce the (quite old) Oracle Base approach for handling utl_file exceptions in multiple procedures, don't do that. It just copies every single exception from the manual and dumps them all into a monumental end-of-procedure exception handler, regardless of context.

When you open a file, you should use an exception handler to ensure that it opened OK, otherwise you can report that you couldn't open file x in folder y. For example (from www.williamrobertson.net/documents/refcursor-to-csv.shtml):

begin
    l_file :=
        utl_file.fopen
        ( filename => p_filename
        , location => p_directory
        , open_mode => 'w'
        , max_linesize => 4000 );
exception
    when utl_file.invalid_operation then
        raise_application_error(-20007, 'File '''||p_filename||''' could not be opened in directory '||p_directory||' ('||k_dir_path||')', TRUE);
    when utl_file.invalid_path then
        raise_application_error(-20008, 'File location '||p_directory||' ('||k_dir_path||') is invalid.', TRUE);
    when utl_file.invalid_filename then
        raise_application_error(-20009, 'Filename '''||p_filename||''' is invalid.', TRUE);
end;

Similarly, when you write to a file you could use exception handlers to ensure that write errors are reported clearly with appropriate details.

Or, centralise all CSV writing operations in a single package like the one I linked to, where you just pass in a refcursor and the file details and it does the rest.