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
eg
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
Thanks
I want to find the time between the loading of 2 files.
Let me explain
eg
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
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That helped..Thanks a lot...I appreciate it
SELECT datediff(ms, A.EndTime, (SELECT min(StartTime) FROM Times B WHERE B.StartTime > A.StartTime) AS TimeDiff
FROM Times A