Solved

Find Difference between Start and End timestamps

Posted on 2010-11-09
3
777 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:esotericmee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 250 total points
ID: 34099484
Let's say the table is called 'Times'
Unfortunately there is no 'next_record' operator, but you could do something like this.

SELECT datediff(ms, A.EndTime, B.StartTime) AS TimeDiff
FROM Times A INNER JOIN Times B ON A.Col1 + 1 = B.Col1
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34099491
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
0
 

Author Comment

by:esotericmee
ID: 34128527
That helped..Thanks a lot...I appreciate it
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question