How to find the difference between two timestamp column

Question

I have table name Record which has the following columns, Empid in number column, dat in timestamp

Which has the following values

empid    dat
======   ====
101      4/9/2012 9:48:54 AM
101      4/9/2012 9:36:28 AM
101      4/9/2012 6:16:28 PM
101      4/10/2012 9:33:48 AM
101      4/10/2012 12:36:28 PM
101      4/10/2012 8:36:12 PM
101      4/11/2012 9:36:28 AM
101      4/11/2012 4:36:22 PM

Here I need to display the following columns,

empid,min(dat) as start,max(dat) as end and difference(max(dat)-min(dat) for each day

Here 3 different days are exists so It should return 3 records with the above mentioned columns.

Please give some ways to get this.

score 2 · Answer 1 · 2012-04-25T12:36:23.577

2

Simply subtract them: max(dat) - min(dat)

SELECT empid,
       min(dat) as strt,
       max(dat) as end,
       max(dat) - min(dat) as diff
FROM the_table
GROUP BY empid;

If you want to group by the day instead of the empid, use this one:

select trunc(dat),
       min(dat) as strt,
       max(dat) as end,
       max(dat) - min(dat) as diff       
from the_table
group by trunc(dat)

edited Apr 25 '12 at 12:36

answered Apr 25 '12 at 11:30

Sorry it showing the following error, ORA-00923 From keyword not found where expected. – Gurujothi.D Apr 25 '12 at 12:02
There was a missing `,`. Fixed it. – Apr 25 '12 at 12:06
2

@Gurujothi.D the above answer has a slight syntax error but surely the least you can do is *try* to correct it? – Sathyajith Bhat Apr 25 '12 at 12:06
Hi Sathya,Thank you for your reply,But Your code seems to be show only one outputfor the min(dat) and max(dat) from the entire record,actually my table contains 3000rows, for example if date is 12-4-2012 there will be many time/date for same date, from that the min and max has to be find out and the difference should be calculated.please tel me for this one. Thank you. – Gurujothi.D Apr 25 '12 at 12:22
1

@Gurujothi.D: it will show the difference between the earliest and latest value of dat **per empid**. This is how `group by` works. If you want something different you have to be **a lot** more detailed in your question. – Apr 25 '12 at 12:29
Hi Sathya, I tried the second one which you mentioned but am getting the difference like this 0.175138888888889, I need this in hours please. – Gurujothi.D Apr 25 '12 at 13:03
I tried like this select trunc(dt), min(dt) as strt, max(dt) as end, to_number(max(dt) - min(dt))*24 as diff from trans group by trunc(dt) – Gurujothi.D Apr 25 '12 at 13:04
Hi Sathya, I tried this one select trunc(dt), min(dt) as strt, max(dt) as end, to_number(max(dt) - min(dt))*24 as diff from trans group by trunc(dt) and am getting the difference like this 9.80027777777778,This also not a valid hours – Gurujothi.D Apr 25 '12 at 13:05

score 1 · Answer 2 · answered May 02 '12 at 10:53

Date arithmetic is pretty straightforward in Oracle: the difference betwwen two dates is returned as the number of days. Values of less than a day are returned as *decimals". That is, 75 minutes is 1.25 hours not 1.15. If you want it as hours and minutes you need to work with an interval.

The inner query calculates the difference between the minimum and maximum data for each employee for each day, and converts it to a DAY interval. The outer query extracts the HOUR and MINUTES from that interval.

select empid
       , tday
       , sdt
       , edt
       , extract(hour from diff) diff_hours
       , extract (minute from diff) diff_minutes
from (
    select empid
           , trunc(dat) tday
           , min(dat) sdt
           , max(dat) edt
           , numtodsinterval(max(dat) - min(dat), 'DAY') diff
    from t42
    group by empid, trunc(dat)
)

How to find the difference between two timestamp column

2 Answers2