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
No comments:
Post a Comment