Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I've never used this, but maybe it will help? docs.oracle.com/cd/B19306_01/server.102/b14225/… –  FrustratedWithFormsDesigner May 13 '13 at 17:59
add comment

3 Answers 3

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 
share|improve this answer
add comment

Try storing as number. Worst case is 3 bytes for HHMM, and 4 for HHMMSS. 01:00 would take 1 byte.

share|improve this answer
add comment

I did some testing using oracle 11g and got this:

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 13 22:40:32 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Sessie: 44,8525
Eventueel trace-bestand: c:\app\administrator\diag\rdbms\sm10\sm10\trace\sm10_ora_5748.trc

sys@SM10> set timing on;
sys@SM10> set autotrace off;
sys@SM10> drop table timing;

Table dropped.

Elapsed: 00:00:00.07
sys@SM10> create table timing ( hoursDate date , hoursText varchar2(5) , hoursNumber number(4) ) nologging;

Table created.

Elapsed: 00:00:00.01
sys@SM10> insert into timing (select to_date(trunc(dbms_random.value(0,23)) || ':' || trunc(dbms_random.value(0,59)),'HH24:MI'),to_char(trunc(dbms_random.value(0,23)) || ':' || tru
nc(dbms_random.value(0,59)) ), trunc(dbms_random.value(0,23)) ||  trunc(dbms_random.value(0,59)) from all_objects );

72514 rows created.

Elapsed: 00:00:02.72
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> insert /*+ append */ into timing select * from timing;

72514 rows created.

Elapsed: 00:00:00.03
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> insert /*+ append */ into timing select * from timing;

145028 rows created.

Elapsed: 00:00:00.05
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> insert /*+ append */ into timing select * from timing;

290056 rows created.

Elapsed: 00:00:00.08
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> insert /*+ append */ into timing select * from timing;

580112 rows created.

Elapsed: 00:00:00.15
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> insert /*+ append */ into timing select * from timing;

1160224 rows created.

Elapsed: 00:00:00.28
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.01
sys@SM10> insert /*+ append */ into timing select * from timing;

2320448 rows created.

Elapsed: 00:00:00.51
sys@SM10> commit;

Commit complete.

Elapsed: 00:00:00.00
sys@SM10> select count (*) from timing;

  COUNT(*)
----------
   4640896

1 row selected.

Elapsed: 00:00:00.15
sys@SM10> create index normal_hourdate on timing(hoursDate) nologging;

Index created.

Elapsed: 00:00:04.54
sys@SM10> create index normal_hourtext on timing(hoursText) nologging;

Index created.

Elapsed: 00:00:14.21
sys@SM10> create index normal_hournumber on timing(hoursNumber) nologging;

Index created.

Elapsed: 00:00:10.33
sys@SM10> analyze table timing compute statistics for table for all indexes for all indexed columns;

Table analyzed.

Elapsed: 00:00:31.40
sys@SM10> set autotrace traceonly;
sys@SM10> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.02
sys@SM10> select        * from timing where hoursDate between to_date('12:00','HH24:MI') and to_date('13:00','HH24:MI') ;

201152 rows selected.

Elapsed: 00:00:01.81

Execution Plan
----------------------------------------------------------
Plan hash value: 3409064691

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |   196K|  2879K|  4084   (6)| 00:00:50 |
|*  1 |  FILTER            |        |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TIMING |   196K|  2879K|  4084   (6)| 00:00:50 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE('12:00','HH24:MI')<=TO_DATE('13:00','HH24:MI'))
   2 - filter("HOURSDATE">=TO_DATE('12:00','HH24:MI') AND
              "HOURSDATE"<=TO_DATE('13:00','HH24:MI'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      27682  consistent gets
      14311  physical reads
          0  redo size
    5421569  bytes sent via SQL*Net to client
     147925  bytes received via SQL*Net from client
      13412  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     201152  rows processed

sys@SM10> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
sys@SM10> select        * from timing where to_date(hoursText,'HH24:MI') between to_date('12:00','HH24:MI') and to_date('13:00','HH24:MI') ;

207104 rows selected.

Elapsed: 00:00:04.27

Execution Plan
----------------------------------------------------------
Plan hash value: 3409064691

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        | 11602 |   169K|  4148   (7)| 00:00:50 |
|*  1 |  FILTER            |        |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TIMING | 11602 |   169K|  4148   (7)| 00:00:50 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE('12:00','HH24:MI')<=TO_DATE('13:00','HH24:MI'))
   2 - filter(TO_DATE("HOURSTEXT",'HH24:MI')>=TO_DATE('12:00','HH24:MI')
               AND TO_DATE("HOURSTEXT",'HH24:MI')<=TO_DATE('13:00','HH24:MI'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      28078  consistent gets
      14311  physical reads
          0  redo size
    5667161  bytes sent via SQL*Net to client
     152281  bytes received via SQL*Net from client
      13808  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     207104  rows processed

sys@SM10> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
sys@SM10> select        * from timing where hoursNumber between 1200 and 1300 ;

171968 rows selected.

Elapsed: 00:00:01.38

Execution Plan
----------------------------------------------------------
Plan hash value: 4284679420

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   135K|  1988K|  3940   (2)| 00:00:48 |
|*  1 |  TABLE ACCESS FULL| TIMING |   135K|  1988K|  3940   (2)| 00:00:48 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("HOURSNUMBER">=1200 AND "HOURSNUMBER"<=1300)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      25746  consistent gets
      14311  physical reads
          0  redo size
    4654857  bytes sent via SQL*Net to client
     126519  bytes received via SQL*Net from client
      11466  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     171968  rows processed

So i would go for the DATE or NUMBER if i can. It is faster and smaller. Just make sure you ignore the DAY part of your date, if you don't need.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.