Storing Times in Oracle

Times can be supported by the oracle "Date" type. By default, the sqlplus session views dates as 19-May-1999 and similar. SQLPlus is similar to a shell (csh, tcsh, ksh, bash) in that you can change certain aspects of the environment, such as the date format environment.

Consider the code below (put the code in foo.sql and then say @foo to run it)

  alter session set nls_date_format = 'HH24-mi' ;
  PROMPT Creating Table foo2
  PROMPT
  Create Table foo2 (
    a	Date,
    b  	Number
  );
  insert into foo2 values ('23-15',1) ;
  select * from foo2 ;
  

This code will create attribute a which is a time in 24 hour and minutes. Thus if you enter 11-15 it will be 11:15 in the AM, whereas 23-15 is 11:15 PM.

Run the code above. You now know how to put times into your database. Exit sqlplus without dropping the table foo2. Next, start a new sqlplus session and type:

  select * from foo2;
  

You will get a day-month-year format! Not what you want. To get it correct you have to reset the date format enviornemt:

  alter session set nls_date_format = 'HH24-mi' ;
  select * from foo2;
  

Now it works.