Link to home
Start Free TrialLog in
Avatar of esotericmee

asked on

Find Difference between Start and End timestamps

Hi I have a table which has start and end time timestamp columns. The table has entries for files and the start and end times for them. These are basically the load times for that file into a table.
I want to find the time between the loading of 2 files.

Let me explain

 Col1   StartTime                                     Endtime
     1    2010-11-09 20:12:22.503          2010-11-09 20:12:22.800
     2    2010-11-09 20:12:27.923      2010-11-09 20:12:28.203
     3     2010-11-09 20:12:33.350      2010-11-09 20:12:33.617
     4     2010-11-09 20:12:38.580           2010-11-09 20:12:38.863
     5    2010-11-09 20:12:43.890      2010-11-09 20:12:44.170

I want to write a query that will give me time difference between

start time of 2 and endtime of 1
start time of 3 and endtime of 2
start time of 4 and endtime of 3  
and so on

the difference will be very small..

So the time between completion of 1st file and start of loading of 2nd file is in this case
   2010-11-09 20:12:27.923  
 - 2010-11-09 20:12:22.800

= 00:00:00.123 sec   (923-800    only the milisec part in this case )

Similarly I want to find time between loading for all the files

I have a column "duration" that gives me difference between the start and endtime of the same row.
Now I want to find out the time lapse between the end of row1 and start of row2 and so on for all the rows.

All the help is greatly appreciated


Avatar of TommySzalapski
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If Col1 isn't reliable then do it like
SELECT datediff(ms, A.EndTime, (SELECT min(StartTime) FROM Times B WHERE B.StartTime > A.StartTime) AS TimeDiff
FROM Times A
Avatar of esotericmee


That helped..Thanks a lot...I appreciate it