LAG Function in Oracle

Question

I have a table (incident) that has column Create_date(DataType=Date). I want to get difference in Days OR Hours from Previous Record. Like the screenshot below.

From Second Record Create_Date I want to minus First Create_Date and from Third Create Date to Second and so on. I'm using LAG function in Oracle, but not sure how its calculating there. Could any one please help me regarding that issue.

incident.create_date - lag(incident.create_date,1) OVER (ORDER BY incident.create_date) AS CREATEDATE_DIFF,

RN 1 We have Create_date (05/01/017 10:40:17 AM

enter image description here

score 3 · Answer 1 · answered May 24 '17 at 09:25

3

Date differences in Oracle are calculated in numbers of days. If the difference is less than a day, you're going to get a value of less than 1 returned.

If you want to convert that into hours, you'll have to multiply the result by 24, for minutes multiply by 24*60 and for seconds it's 24*60*60.

e.g.:

select sysdate - trunc(sysdate) diff_in_days,
       (sysdate - trunc(sysdate))*24 diff_in_hours,
       (sysdate - trunc(sysdate))*24*60 diff_in_mins,
       (sysdate - trunc(sysdate))*24*60*60 diff_in_secs
from   dual;

DIFF_IN_DAYS DIFF_IN_HOURS DIFF_IN_MINS DIFF_IN_SECS
------------ ------------- ------------ ------------
0.4342245370 10.4213888888 625.28333333        37517

You may then wish to apply ROUND (or maybe TRUNC/CEIL) depending on how you want the output to look like (e.g. to 2 d.p., to nearest minute, etc).

answered May 24 '17 at 09:25

Boneist

22,910
1
25
40

Thanks for your reply But How to do second createdate minus to FirstDate you not using any where LAG Function.The Screenshot which I have attached Its calculating difference but not sure how its calculating.For Ex. Second Record(05/01/017 10:49:37) - 05/01/017 10:40:17 AM(First Record) that means around 0 hours, 55 minutes and 34 seconds but I it showing something (0.001388888).even if i do round its 0.0 * 24 (IN Days). – Shilpi May 24 '17 at 09:46
You would do the rounding after finishing all your calculations (if you round early, you make your calculations less accurate!). So, `round(( - )*24*60, 2)` would take your two dates, find the difference, multiply that by 24 and 60 and then rounds it to two decimal places. – Boneist May 24 '17 at 09:54
If you need the answer in terms of 0 days, 3 hours, 29 minutes and 10 seconds, I suggest you look at MT0's answer, btw. – Boneist May 24 '17 at 12:48

Utsav · Answer 2 · 2017-05-24T10:46:31.210

0

Use

......
(incident.create_date - 
 lag(incident.create_date,1) OVER (ORDER BY incident.create_date))
 *24*60
 AS CREATEDATE_DIFF_IN_MINS,.....

to get the output in minutes, which seams suitable for your sample data. Or multiply further by 60 to get output in seconds.

edited May 24 '17 at 10:46

answered May 24 '17 at 09:30

Utsav

7,914
2
17
38

Thanks But Still I'M not getting As I"m using what ever you posted. (incident.create_date - lag(incident.create_date,1) OVER (ORDER BY incident.create_date)) *24*60 AS CREATEDATE_DIFF, Second Date(05/01/017 10:58:54 AM)-FirstDate(04/01/017 01:53:04 PM) its getting 1.86666 the diff is around 21 hours 5 min but result I'm getting as 1.866 after using 24*60 as above code .Please help me – Shilpi May 24 '17 at 12:18
Not sure what you mean. I just suggested to multiply your already existing result by `24*60` if you want to get output in minutes. What is your current output and what are you expecting? – Utsav May 24 '17 at 12:21

score 0 · Answer 3 · answered May 24 '17 at 09:58

0

If you subtract one date from another you will get the difference in days (or fractions thereof) as a number.

You can get the days/hours/minutes/seconds of this using an interval:

SELECT EXTRACT( DAY    FROM createdate_diff ) AS days,
       EXTRACT( HOUR   FROM createdate_diff ) AS hours,
       EXTRACT( MINUTE FROM createdate_diff ) AS minutes,
       EXTRACT( SECOND FROM createdate_diff ) AS seconds,
       createdate_diff
FROM   (
  SELECT NUMTODSINTERVAL(
           create_date - lag(create_date) OVER (ORDER BY create_date),
           'DAY'
         ) AS CREATEDATE_DIFF
  FROM   incident
);

Or you can perform the same calculations manually:

SELECT TRUNC( createdate_diff ) AS days,
       TRUNC( MOD( createdate_diff * 24, 24 ) ) AS hours,
       TRUNC( MOD( createdate_diff * 24 * 60, 60 ) ) AS minutes,
       MOD( createdate_diff * 24 * 60 * 60, 60 ) AS seconds,
       createdate_diff
FROM   (
  SELECT create_date - lag(create_date) OVER (ORDER BY create_date)
           AS CREATEDATE_DIFF
  FROM   incident
);

answered May 24 '17 at 09:58

MT0

143,790
11
59
117

To clarify, this solution will produce the difference split across days, hours, minutes and seconds, rather than as a figure with a single unit (ie. 0 days, 1 hour, 53 minutes and 30 seconds, vs 6810 seconds or 113.5 minutes). I'm not sure it's exactly what the OP is after, but it's not necessarily clear from the information they provided! – Boneist May 24 '17 at 10:26
@Boneist From the [OP's comment](https://stackoverflow.com/questions/44154136/lag-function-in-oracle/44155086?noredirect=1#comment75326096_44154312) they appear to want (even if their values are incorrect for the inputs in their example) an output like "0 hours, 55 minutes and 34 seconds". – MT0 May 24 '17 at 10:31
Which doesn't tally with the `days OR hours` in their original post! I'm not saying this is a wrong answer; I just wanted to clarify it for the OP, as it might be exactly what they want. – Boneist May 24 '17 at 10:36
Instead of `NUMTODSINTERVAL` you can also use `CAST(create_date as TIMESTAMP) - LAG(create_date) OVER (ORDER BY create_date) ` if you prefer. – Wernfried Domscheit May 24 '17 at 10:41

LAG Function in Oracle

3 Answers3