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.
Advertisement
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