We help IT Professionals succeed at work.

# Find Difference between Start and End timestamps

on
795 Views
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

Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT
Awarded 2010
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
CERTIFIED EXPERT
Awarded 2010
Top Expert 2013

Commented:
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

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

###### Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

Deciding to stick with EE.

Mohamed Asif

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
###### Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Connect with Certified Experts to gain insight and support on specific technology challenges including:

• Troubleshooting
• Research
• Professional Opinions
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.