CODEWITHSUNDEEP
sqloracle-databasedatetime
user3094295
user3094295

Reputation: 13

Calulating the time difference in minutes and seconds

How can i subtract from two timestamps (i need to concentrate only time diffeerence not dates)

one is attendedInTime and other is plannedInTime, and these two times are in timestamp.

I have written following query it is not working some scenario's. 

SELECT (
   TO_NUMBER(substr(to_Char(attendedintime,'DD.MM.YYYY:HH24:MI:SS'), 12, 2) ) -  
   TO_NUMBER(substr(to_Char(plannedInTime, 'DD.MM.YYYY:HH24:MI:SS'), 12, 2) )
  ) 
   || ':' || 
 ( 
   TO_NUMBER(substr(to_Char(attendedintime,'DD.MM.YYYY:HH24:MI:SS'), 15, 2) ) - 
   TO_NUMBER(substr(to_Char(plannedInTime, 'DD.MM.YYYY:HH24:MI:SS'), 15, 2) )
 ) AS difference FROM TABLENAME

But as per the above query i am not able syncup with the hours and minutes (It is calclating differently minutes and hours)

Upvotes: 1

Views: 143

Answers (1)

San
San

Reputation: 4538

Assuming attendedInTime > plannedInTime:

select extract (hour from (attendedInTime - plannedInTime)) as HOUR_DIFF,
       extract (minute from (attendedInTime - plannedInTime)) as MINUTE_DIFF, 
       extract (second from (attendedInTime - plannedInTime)) as SECOND_DIFF
  from tab ;

Upvotes: 1

Related Questions