Date Time differences calculation in Oracle

Question

I have a situation where I need to get the differences between two columns.

Column 1: 01-OCT-13 10:27:15
Column 2: 01-OCT-13 10:28:00

I need to get the differences between those above two columns. I tried using '-' operator but the output is not in an expected way.
I need output as follows: 00-00-00 00:00:45

How can a time be `10:27:60`? That would be `10:28:00` (60 seconds in a minute). Are these `DATE` columns (in which case the trailing 60 is not valid)? Or are they `timestamp` columns (in which case the trailing 60 may be intended to represent fractional seconds and you're just missing the whole number of seconds)? — Justin Cave, Oct 03 '13 at 05:02

Swapnil Patil · Accepted Answer · 2013-10-03T06:58:41.107

2

Try this query but it is not tested

SELECT extract(DAY
               FROM diff) days,
       extract(hour
               FROM diff) hours,
       extract(MINUTE
               FROM diff) minutes,
       extract(SECOND
               FROM diff) seconds,
       extract(YEAR
               FROM diff) years
FROM
  (SELECT (CAST (to_date(t.column1, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP) -CAST (to_date(t.column2, 'yyyy-mm-dd hh:mi:ss') AS TIMESTAMP)) diff
   FROM tablename AS t)

edited Oct 03 '13 at 06:58

answered Oct 03 '13 at 05:12

Swapnil Patil

971
2
18
41

But that gets only the seconds difference right..!? What if there is also difference in date as well as hours and minutes? – user2037445 Oct 03 '13 at 05:17
Yes its working fine.Can I extract all in one single query? – user2037445 Oct 03 '13 at 05:23
Yeah its working thank you so much..If at all I want to find the differences of Month and Year also Shall I follow the same procedure – user2037445 Oct 03 '13 at 05:34
@user2037445 I updated query now,check out,delete some comments or lets move to chat – Swapnil Patil Oct 03 '13 at 06:13
Yeah even now it is getting the same error – user2037445 Oct 03 '13 at 06:49
@user2037445 sorry but I changed conversion type now ,try working or not – Swapnil Patil Oct 03 '13 at 07:05
I dont know where I am going wrong,but the same error is repeating. – user2037445 Oct 03 '13 at 07:24

score 2 · Answer 2 · answered Oct 03 '13 at 05:26

Try this too,

WITH T(DATE1, DATE2) AS
(
SELECT TO_DATE('01-OCT-13 10:27:15', 'DD-MON-YY HH24:MI:SS'),TO_DATE('01-OCT-13 10:28:00', 'DD-MON-YY HH24:MI:SS') FROM DUAL
)
SELECT floor(date2 - date1)
          || ' DAYS '
          || MOD(FLOOR ((date2 - date1) * 24), 24)
          || ' HOURS '
          || MOD (FLOOR ((date2 - date1) * 24 * 60), 60)
          || ' MINUTES '
          || MOD (FLOOR ((date2 - date1) * 24 * 60 * 60), 60)
          || ' SECS ' time_difference

FROM T;

Another method to extract days, hours, minutes and seconds b/w two dates. — Dba, Oct 03 '13 at 05:36

score 0 · Answer 3 · answered Oct 03 '13 at 05:42

0

You can use the below query to find the difference seconds :

select 24*60*60*
(to_date('01-OCT-13 10:28:00', 'yyyy-mm-dd hh24:mi:ss')-
to_date('01-OCT-13 10:27:15', 'yyyy-mm-dd hh24:mi:ss')) diff_secs
from dual;

SQL Fiddle

answered Oct 03 '13 at 05:42

Pooh

107
1
5

But I need differences between dates as well including days,months,years,hours,minutes,seconds – user2037445 Oct 03 '13 at 05:43

Date Time differences calculation in Oracle

3 Answers3