CODEWITHSUNDEEP
sqloracle11g
hadoo
hadoo

Reputation: 213

Difference between timestamps in Oracle

I am identifying the difference between two timestamp fields in Oracle.

(TIMESTAMPa-TIMESTAMPb)

Result: +09 19:16:04.747000

I am attempting to note whether the resulting value is positive or negative.

My current solution works, but I think there has to be a better way.

Solution:

CASE
WHEN SUBSTR((TIMESTAMPa-TIMESTAMPb),1,1)='+' 
THEN 'AFTER'
ELSE 'BEFORE'
END AS DIFF

Upvotes: 1

Views: 212

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

interval '0' day can be replaced by any other '0' interval

select  case 
            when (timestamp '2017-02-01 20:07:09' - timestamp '2017-02-01 21:35:02') > interval '0' day 
            then 'AFTER' 
            else 'BEFORE' 
        end

from    dual

BEFORE

... but why not use a direct compare?

select  case 
            when timestamp '2017-02-01 20:07:09' > timestamp '2017-02-01 21:35:02' 
            then 'AFTER' 
            else 'BEFORE' 
        end

from    dual

BEFORE

Upvotes: 2

Related Questions