Diffrence Between Dates in Oracle HH:MM:SS format

To get the Difference Between Date in Oracle:

Query 1:

Calculating the time difference between two TIMESTAMP datatypes is much easier than the old DATE datatype. Look at what happens when you just do the same substraction as in the above queries:

SELECT SUBSTR(time1,1,30) "Time1",
       SUBSTR(time2,1,30) "Time2",
       SUBSTR((time2-time1),1,30) "Time1 - Time2"
 FROM date_table;

Time1                          Time2                          Time1 - Time2
------------------------------ ------------------------------ ---------------------------
17-DEC-80 12.00.00.000000 AM   03-DEC-04 10.34.24.000000 AM   +000008752 10:34:24.000000

As you can see, the results are much easier to recognize, 8752 days, 10 hours, 34 minutes, and 24 seconds. This means no more worries about how many seconds in a day and all those cumbersome calculations. And therefore the calculations for getting the weeks, days, hours, minutes, and seconds becomes a matter of picking out the number by using the SUBSTR function as can be seen next:

SELECT SUBSTR(time1,1,30) "Time1",
       SUBSTR(time2,1,30) "Time2",
       SUBSTR((time2-time1), INSTR((time2-time1),' ')+7,2) "SS",
       SUBSTR((time2-time1), INSTR((time2-time1),' ')+4,2) "MI",
       SUBSTR((time2-time1), INSTR((time2-time1),' ')+1,2) "HH",
       TRUNC(TO_NUMBER(SUBSTR((time2-time1),1, INSTR(time2-time1,' ')))) "Days"
  FROM date_table;

Time1                          Time2                          SS MI HH       Days
------------------------------ ------------------------------ -- -- -- ----------
17-DEC-80 12.00.00.000000 AM   03-DEC-04 10.34.24.000000 AM   24 34 10       8752

Query 2:

  SELECT
  FEED_FILE_OID,
  FEED_INSTANCE_OID,
  FEED_INSTANCE_RUN_OID,
  FEED_PROFILE_OID,
  FEED_FILE,
  FEED_FILE_NAME,
  FILE_STATUS,
  INSTANCE_DATE,
  UPDATED_BY,
  UPDATED_DATE,
  UPD_ID,
Replace(FTPRetrievedTime,'::','') FTPRetrievedTime ,
Replace(FileProcessingTime,'::','') FileProcessingTime ,
Replace(DBStoringTime,'::','') DBStoringTime ,
Replace(ScheduleDiffTime,'::','') ScheduleDiffTime ,
Replace(DBRetrieveTime,'::','') DBRetrieveTime,
Replace(ReleaseTime,'::','') ReleaseTime,
Replace(TotalProcessingTime,'::','')TotalProcessingTime

FROM
(
select
  FEED_FILE_OID,
  FEED_INSTANCE_OID,
  FEED_INSTANCE_RUN_OID,
  FEED_PROFILE_OID,
  FEED_FILE,
  FEED_FILE_NAME,
  FILE_STATUS,
  INSTANCE_DATE,
  UPDATED_BY,
  UPDATED_DATE,
  UPD_ID,
lpad(extract (HOUR FROM AFTER_FTP-BEFORE_FTP),2,0) ||':'||
lpad(extract (MINUTE FROM AFTER_FTP-BEFORE_FTP),2,0)  ||':'||
lpad(extract (SECOND FROM AFTER_FTP-BEFORE_FTP),2,0) FTPRetrievedTime ,
lpad(extract (HOUR FROM AFTERFEEDPROC-BEFOREFEEDPROC),2,0) ||':'||
lpad(extract (MINUTE FROM AFTERFEEDPROC-BEFOREFEEDPROC),2,0)  ||':'||
lpad(extract (SECOND FROM AFTERFEEDPROC-BEFOREFEEDPROC),2,0) FileProcessingTime ,
lpad(extract (HOUR FROM AFTERDBSTORE-BEFOREDBSTORE),2,0) ||':'||
lpad(extract (MINUTE FROM AFTERDBSTORE-BEFOREDBSTORE),2,0)  ||':'||
lpad(extract (SECOND FROM AFTERDBSTORE-BEFOREDBSTORE),2,0) DBStoringTime ,

lpad(extract (HOUR FROM BEFOREDBRETRIVE-AFTERDBSTORE),2,0) ||':'||
lpad(extract (MINUTE FROM BEFOREDBRETRIVE-AFTERDBSTORE),2,0)  ||':'||
lpad(extract (SECOND FROM BEFOREDBRETRIVE-AFTERDBSTORE),2,0) ScheduleDiffTime ,

lpad(extract (HOUR FROM AFTERDBRETRIVE-BEFOREDBRETRIVE),2,0) ||':'||
lpad(extract (MINUTE FROM AFTERDBRETRIVE-BEFOREDBRETRIVE),2,0)  ||':'||
lpad(extract (SECOND FROM AFTERDBRETRIVE-BEFOREDBRETRIVE),2,0) DBRetrieveTime ,

lpad(extract (HOUR FROM AFTER_PUB-BEFORE_PUB),2,0) ||':'||
lpad(extract (MINUTE FROM AFTER_PUB-BEFORE_PUB),2,0)  ||':'||
lpad(extract (SECOND FROM AFTER_PUB-BEFORE_PUB),2,0) ReleaseTime ,


lpad(extract (HOUR FROM AFTER_PUB-BEFORE_FTP),2,0) ||':'||
lpad(extract (MINUTE FROM AFTER_PUB-BEFORE_FTP),2,0)  ||':'||
lpad(extract (SECOND FROM AFTER_PUB-BEFORE_FTP),2,0) TotalProcessingTime 

from VENDOR_FEED_FILE_DATA 

WHERE   BEFORE_PUB IS NOT NULL

)
 

 Related Site : http://www.akadia.com/services/ora_date_time.html

 

BY 

 SANA