Oracle — separate time and date

Question

I have a question with Oracle (I've installed Oracle 11g Express Edition). I want to insert values for 'date' and 'time', but I cannot separate them.

create table Match 
(
    numMatch number(2) constraint PKMatch primary key,
    dateM date, 
    heureM date,
    numE_Eq number(2),
    numE_Eq2 number(2),
    nomTerrain varchar2(30)
);

--"tools"=>"preferences"=>"format de date:DD/MM/YYYY HH24:MI:SS"

insert into Match values (1,to_date
('10/12/2010','DD/MM/YYYY'),to_date('15:00:00','HH24:MI:SS'),1,3,'Stade Argentina'
);

result:

dateM: 10/12/2010 00:00:00

heureM: 01/11/2012 15:00:00

PS: I've tried to_char instead of to_date, but it didn't work at all.

---

Yes, I'm aware of that 'DATE datatype contains both date and time', but it's the prof who insists showing date and time separately in the table, and I've seen your solutions before, but for me, it's a query, not to 'insert values' in the table.

So I'd like to know how I can have a table directly presenting date and time.

Answer 1

Oracle doesn't have a TIME datatype. You can store a DATE with a time component, and just query based on time, and display based on time.

select to_char(my_date_field, 'HH24:MI:SS') 
from my_table
where to_date(my_date_field, 'HH24:MI') = '18:51';

Alternatively, you can store seconds from midnight as an integer, and calculate the time of day from that. It will also make querying for range times easier I think.

Also, within a session, execute the following to have all dates formatted the way you wish:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'

Answer 2

Another way to represent a TIME equivalent type in Oracle is with the INTERVAL type, such as:

SQL> CREATE TABLE foo (
       bar INTERVAL DAY(0) TO SECOND(3)
     );

This would allow the storage of a time period with 0 precision of the DAY component, and 3 decimal points for the SECOND component. An INSERT example is:

SQL> INSERT INTO foo VALUES ('0 01:01:01.333');

What's great about this approach is that it automatically presents the results of a SELECT in an intuitive format without the need for conversion:

SQL> SELECT * FROM foo;

BAR
---------------------------------------------------------------------------
+0 01:01:01.333

Answer 3

but it's the prof who insists showing date and time separately in the table

While this sounds like a pretty stupid requirement, one thing you could do is to create two computed columns that show the date and time as varchar columns:

create table match 
(
  nummatch number(2) constraint pkmatch primary key,
  the_date date,
  datem generated always as to_char(the_date, 'yyyy-mm-dd'),
  heurem generated always as to_char(the_date, 'hh24:mi')
  nume_eq number(2),
  nume_eq2 number(2),
  nomterrain varchar2(30)
);

insert into Match (nummatch, the_date, nume_eq, nume_eq2, nomterrain) 
values 
(1,to_date('10/12/2010 15:00:00','DD/MM/YYYY hh24:mi:ss'),1,3,'Stade Argentina');

Then a

select *
from match;

will return:

NUMMATCH | THE_DATE            | DATEM      | HEUREM | NUME_EQ | NUME_EQ2 | NOMTERRAIN     
---------+---------------------+------------+--------+---------+----------+----------------
       1 | 2010-12-10 15:00:00 | 2010-12-10 | 15:00  |       1 |        3 | Stade Argentina

Alternatively you could just create a view on the table that separates the date and time using to_char()

Answer 4

Oracle DATE type includes both DATE and TIME information (because it pre-dates the SQL-92 standard when the standard DATE, TIME and TIMESTAMP types were added). So, you can't separate them in the table; there's no reason to do so, either. You can, if you so desire, create a view which presents the DATE field as separate date-only and time-only display fields.