Oracle SQL time data type

Question

I'm looking to store just a time value in a number of columns, but I'm unsure what data type I have to use? There is currently TIMESTAMP in place, but this seems to store date as well and is useless if I'm trying to store a recorded time from a race.

Any ideas, I've searched around and can't find what I'm looking for.

Thank you for reading!

Answer 1

The elapsed time for a race should be stored in an INTERVAL DAY TO SECOND.

SQL> create table runner(
  2    runner_id number primary key,
  3    runner_name varchar2(100),
  4    runner_time interval day to second
  5  );

Table created.

SQL> insert into runner
  2    values( 1, 'Justin', numtodsinterval( 250, 'second' ) );

1 row created.

SQL> select *
  2    from runner;

 RUNNER_ID RUNNER_NAME          RUNNER_TIME
---------- -------------------- ------------------------------
         1 Justin               +00 00:04:10.000000

Answer 2

TIMESTAMP and DATETIME aren't appropriate here. What you're trying to store isn't a single point in time, but an interval of time. Oracle 9i and later have support for such intervals. Try using INTERVAL DAY TO SECOND:

INTERVAL '3 12:30:06.7' DAY TO SECOND(1)

Reference: http://www.techrepublic.com/article/master-oracles-time-interval-datatypes/6085801