Adding time to a table in SQLplus without date

I am trying to create a table regarding telecommunication and I want to add time for 2 rows call_start_time and call_end_time and with that, I want to calculate the call_duration.

This is what I used to enter the value

to_date('2021/05/31:20:20:20','yyyy/mm/dd hh24:mi:ss') 

If I use this format while displaying the data only the date is displayed not the time.

Can someone help me create a table where I just have to input the time alone not the date and calculate the call_duration using call_start_time and call_end_time?

Heres the table I created:

SQL> create table MOBILE_TRANSACTION (
                    mob_no number(10), 
                    call_type varchar2(25), 
                    call_category varchar2(25),
                    call_start_time date,
                    call_end_time date, 
                    call_duration number, 
                   call_charges_rs number );

I hope my question is clear.

Answer

[TL;DR] You don’t need to create a time column as the DATE column already has a time but SQL/Plus it not showing it; chage the settings on SQL/Plus and use what you already have.

---

Your table has a time component; the user interface (SQL/Plus) is chosing not to display it.

If you want to change the default display format then use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Then:

SELECT *
FROM   MOBILE_TRANSACTION;

If you want to explicitly convert the date to have a format (since it is stored as a binary value and has no format in the database and it is only when it reaches the user interface you are using that that user interface converts the binary to something human readable) then you can use TO_CHAR:

SELECT mob_no, 
       call_type, 
       call_category,
       TO_CHAR(call_start_time, 'YYYY-MM-DD HH24:MI:SS') AS call_start_time,
       TO_CHAR(call_end_time, 'YYYY-MM-DD HH24:MI:SS') AS call_end_time,
       call_duration, 
       call_charges_rs
FROM   MOBILE_TRANSACTION

---

If you want to calculate the duration then you can use a virtual column:

create table MOBILE_TRANSACTION (
  mob_no          number(10), 
  call_type       varchar2(25), 
  call_category   varchar2(25),
  call_start_time date,
  call_end_time   date, 
  call_duration   INTERVAL DAY TO SECOND
                  GENERATED ALWAYS AS (
                    (call_end_time - call_start_time) DAY TO SECOND
                  ), 
  call_charges_rs number
);

db<>fiddle here

or

create table MOBILE_TRANSACTION (
  mob_no          number(10), 
  call_type       varchar2(25), 
  call_category   varchar2(25),
  call_start_time date,
  call_end_time   date, 
  call_duration   number
                  GENERATED ALWAYS AS ((call_end_time - call_start_time) * 24 * 60 * 60), 
  call_charges_rs number
);

db<>fiddle here