CODEWITHSUNDEEP
sqloracle-database
joe.d
joe.d

Reputation: 363

Oracle SQL get difference in days between two timestamps

 event            start                             end
 event147         22-JUL-18 11.55.02.000000000  23-JUL-18 12.45.12.000000000 
 event148         24-JUL-18 13.12.45.000000000  25-JUL-18 18.33.05.000000000

Above is md_events table, I need to get difference in days (without time portion) between end of event 147 and beginning of event 148. Both are timestamps.

I have tried, but getting error (same with numtodsinterval)

select dsintervaltonum(me2.start- me1.end, 'day') as days
  from md_events me1, md_events me2
 where me1.event  = 'event147'
   and me2.event =  'event148'

This query returns time portion as well

 select to_char(me2.start- me1.end, 'DDD') as days
      from md_events me1, md_events me2
     where me1.event  = 'event147'
       and me2.event =  'event148'

Upvotes: 0

Views: 2138

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

You can also use this one:

SELECT EXTRACT(DAY FROM (me2.start- me1.end))
FROM md_events me1
    CROSS JOIN md_events me2
where
    me1.event  = 'event147' and
    me2.event =  'event148'

Upvotes: 1

Achyuta nanda sahoo
Achyuta nanda sahoo

Reputation: 455

I don't have Oracle in my system. so i am going to give a way/ logic to find your solution using SQLITE . change SQLITE syntax with ORACLE syntax with this logic !!.

SELECT y.start,x.endd, y.start-x.endd as diff FROM (SELECT a.* FROM NAMES AS a WHERE a.event='event147') AS x, (SELECT b.* FROM NAMES AS b WHERE b.event='event148') AS y;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

Cast the two timestamps to dates and then directly take their difference:

select
    cast(me2.start as date) - cast(me1.end as date)
from md_events me1, md_events me2
where
    me1.event  = 'event147' and
    me2.event =  'event148'

Upvotes: 1

Related Questions