I have a table abc as:
-- start_time |end_time | total_time_taken
-- 27.05.2020 00:52:48 |27.05.2020 02:08:33 |
I want to set the value of total_time_taken as the difference of end_time-start_time. in the format "HH:MM:SS".I searched the similar topic but didnot find the exact answer.
My expected output is like : 01:44:12 (HH:MM:SS)
So,i tried :
SELECT To_Char(end_time,'HH24:MM:SS'),To_Char(start_time,'HH24:MM:SS'),
To_Char(end_time,'HH24:MM:SS')-To_Char(start_time,'HH24:MM:SS') FROM abc;
The datatypes of start_time,end_time,total_time_taken is DATE.Please help me to find the solution.
If you cast those dates as timestamps, you can easily subtract them and see relatively nice result:
SQL> with test (st, et) as
2 (select to_date('27.05.2020 00:52:48', 'dd.mm.yyyy hh24:mi:ss'),
3 to_date('27.05.2020 02:08:33', 'dd.mm.yyyy hh24:mi:ss')
4 from dual
5 )
6 select cast(et as timestamp) - cast(st as timestamp) diff
7 from test;
DIFF
--------------------------------------------------------------------------
+000000000 01:15:45.000000
SQL>
If you want to format it as you wanted (note that mm format mask is for months; mi is for minutes), then you could do some extracting - again from timestamp (won't work for date):
SQL> with test (st, et) as
2 (select to_date('27.05.2020 00:52:48', 'dd.mm.yyyy hh24:mi:ss'),
3 to_date('27.05.2020 02:08:33', 'dd.mm.yyyy hh24:mi:ss')
4 from dual
5 ),
6 diff as
7 (select cast(et as timestamp) - cast(st as timestamp) diff
8 from test
9 )
10 select extract(hour from diff) ||':'||
11 extract(minute from diff) ||':'||
12 extract(second from diff) diff
13 from diff;
DIFF
-------------------------------------------------------------------------
1:15:45
SQL>
You can further make it pretty (e.g. two digits for hours, using LPAD function). Or, you can even write your own function which will actually work on difference of DATE datatype values, do some calculations (using trunc function, subtractions, whatnot), but the above looks pretty elegant if compared to a home-made function.