How to get time difference between two date columns in oracle Sql

First of all both date columns should have time stored in it wether it is in date Datatype or Datetime Datatype .

To get time  difference between two date column having same date you can use this single sql statement

Select substr(numtodsinterval([END_TIME]-[START_TIME],’day’),12,5)  from [TABLE_NAME];

Where [TABLE_NAME] is the table where columns are stored and [END_TIME],[START_TIME] are the column names with date datatype having time stored in it. Out put will be rounded to minutes.

E.g

if  Start_Time=12/25/2011 8:40:21 AM

and End_time= 12/25/2011 5:20:21 PM  

output will be          08:40  

Above written sql statement is only for getting time difference between two columns having max 24 hour difference or you can say time difference between two date columns having same date but different time.

You can make it more flexible to get hour ,minute difference between two date columns having days or month difference
For that you need to get days diff and multiply with 24 .

Select to_number(substr(numtodsinterval([END_TIME]-[START_TIME]),’day’,2,9))*24+
to_number(substr(numtodsinterval([END_TIME]-[START_TIME],’day’),12,2))
||’:'||to_number(substr(numtodsinterval([END_TIME]-[START_TIME],’day’),15,2)) from [TABLE_NAME];

if  Start_Time=12/20/2011 8:40:21 AM

and End_time= 12/25/2011 5:20:21 PM  

output will be          152:40  

So this is the generalized query to get time difference between two date column and output will be in hours and minutes.

The best thing is about this query that you can run it with group functions like Sum,Max,Min.Like we can run this same query with sum function to get the time spent by one employee to do a job .

Select to_number(substr(sum(numtodsinterval([END_TIME]-[START_TIME],’day’)),2,9))*24+
to_number(substr(sum(numtodsinterval([END_TIME]-[START_TIME],’day’)),12,2))
||’:'||to_number(substr(sum(numtodsinterval([END_TIME]-[START_TIME],’day’)),15,2)) from [TABLE_NAME]

where emp_code=[EMP_CODE]

and job=[JOB_CODE];

So it will give you summarized total Hour and Minutes spent by any employee on a perticular job.

Hope it will be  use full for some…

Advertisement