Skip to content
Advertisement

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

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:

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:

Then:

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:


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

db<>fiddle here

or

db<>fiddle here

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement