Version: 11.2
I am not an SQL expert.
I am trying to subtract the start time and end time and thereby find the difference between these two times.
In the below example , the difference between start_time and end_time is around 52 minutes (6:15 PM to 7:07 PM)
I tried some stuff to find the difference below. But it didn't work out .
SQL> create table t (start_time date, end_time date);
Table created.
SQL> desc t
Name Null? Type
----------------------------------------------------------------- -------- ---------------------
START_TIME DATE
END_TIME DATE
SQL> insert into t values (to_date('24/JUN/2013 18:15:42', 'DD/MON/YYYY HH24:MI:SS'), to_date('24/JUN/2013 19:07:54','DD/MON/YYYY HH24:MI:SS'));
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> select * from t;
START_TIME END_TIME
-------------------- --------------------
24 Jun 2013 18:15:42 24 Jun 2013 19:07:54
-- Trying to find the difference from Start_Time and End_Time
SQL> select end_time-start_time from t;
END_TIME-START_TIME
-------------------
.03625 ------------------> What does .03625 mean ?
-- More Unsuccesfull attempts
SQL> select to_number(end_time-start_time)/60 from t;
TO_NUMBER(END_TIME-START_TIME)/60
---------------------------------
.000604167
SQL> select to_number(end_time-start_time)*24/60 from t;
TO_NUMBER(END_TIME-START_TIME)*24/60
------------------------------------
.0145
SQL> select to_number(end_time-start_time)*60*60 from t;
TO_NUMBER(END_TIME-START_TIME)*60*60
------------------------------------
130.5
SQL> select to_number(end_time-start_time)*60*60/24 from t;
TO_NUMBER(END_TIME-START_TIME)*60*60/24
---------------------------------------
5.4375