How to find the difference between two timestamp column

[Gurujothi D]

please Solve my issue,
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 Help me to get this.

Thank you,
Regards,
Gurujothi

[Rabbit]

Do just that in an aggregate query. Just group by the empid, year, month, and day of the date.

[Gurujothi D]

Hi Rabbit,
Thank you for your response,can you post the query here.?

[Rabbit]

I do not give out code unless the poster has shown they have attempted the solution themselves.

[Gurujothi D]

Hi Rabbit,
If I use the following query am getting the output,

select empid,trunc(dt) as Dat,
min(dt) as Firstintime,
max(dt) as Lastouttime,
to_number(max(dt) - min(dt))*24 as TotalHours 
from trans
group by trunc(dt),empid

For firstintime it displaying output as 3/29/2012 9:12:57 AM but I need only 9:12:57 AM for this column,

For lasttintime it displaying output as 3/29/2012 9:24:24 PM but I need only 9:24:24 PM for this column,

For Totalhours it displaying output as 12.190833333333 3 but I need only in hour and minute format i.e 9.12 for this column

How to get this output format?

regards,
gurujothi

[Gurujothi D]

Hi Rabbit,
this code solved my issue,

select empid,trunc(dt) as Dat, 
to_char(min(dt),'hh:mi am') as Firstintime, 
to_char(max(dt),'hh:mi am') as Lastouttime, 
to_char(trunc(sysdate) + (max(dt) - min(dt)),'hh24:mi') as TotalHours 
from trans 
group by trunc(dt),empid

Thank you,
regards,
gurujothi