Solved

Calculate the difference between two timestamps

Posted on 2011-03-07
12
1,038 Views
Last Modified: 2012-05-11
I am really new to SQL so I am asking for some assistance.

I would like to have a query that would calculate the difference between two timestamps.

The timestamp data is unsorted and stored in two columns as shown in TABLE RandomData.
Given the following data in TABLE RandomData it should produce the results shown.

The timestamp is calculated as (Weeks * 604800) + Seconds (probably promote to a BIGINT)
to account for seconds rolling over at the beginning of a new week. So I will need to sort
by the timestamp then calculate the TimeDiff as next row timestamp minus current row timestamp,
the last row timestamp ends up null.

This should be doable without producing any temporary tables but I don't know enough yet
how to structure the query. It would also be nice to have an all-in-one query that identifies
only the differences that exceed a threshold value.

Any assistance is greatly appreciated.

CREATE TABLE RandomData
(
    ID          INT             -- ID
    Weeks       INT,            -- Weeks since beginning of epoch
    Seconds     INT             -- Time since start of week
);

--Data
ID  Weeks   Seconds
1   161 161983
2   161 161998
3   161 161985
4   161 161999
5   161 161987
6   161 162001
7   161 161988
8   161 162002
9   161 161990
10  161 162004
11  161 161991
12  161 162005
13  161 161993
14  161 162007
15  161 161996
16  161 162009
17  161 161997
18  161 162010

-- Expected results
ID  Week    Time    TimeDiff
1   161 161983  2
3   161 161985  2
5   161 161987  1
7   161 161988  2
9   161 161990  1
11  161 161991  2
13  161 161993  3
15  161 161996  1
17  161 161997  1
2   161 161998  1
4   161 161999  2
6   161 162001  1
8   161 162002  2
10  161 162004  1
12  161 162005  2
14  161 162007  2
16  161 162009  1
18  161 162010  NULL

0
Comment
Question by:Mr_Difficult
  • 6
  • 5
12 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35063035
Can you explain how did you calculate the TimeDiff?
0
 

Author Comment

by:Mr_Difficult
ID: 35063342
...then calculate the TimeDiff as next row timestamp minus current row timestamp,
the last row timestamp ends up null.


TimeDiff is calculated by subtracting the timestamp in one row from the timestamp in the next row and so on until the end of the table is reached. (The table needs to be sorted for any of the TimeDiff values to make sense.)

So the sorted table (based on the calculated timestamp) for the first two rows is ID 1 and ID 3.
Take the calculated timestamp of the second row in the sorted table (ID 3) and subtract the calculated timestamp of the first row in the sorted table (ID 1) to get the TimeDiff.

so we have ((161*604800)+161985) - ((161*604800)+161983) = 2 which is the TimeDiff for the first row (ID = 1)

timestamp is not a column in the table, it is a calculated value based on Week and Time per the formula

timestamp = (Week*604800)+Time


ID  Week    Time    TimeDiff
1   161 161983  2
3   161 161985  2


Hopefully this is a little more clear now, if not I will try to rephrase it.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35063409
which SQL version you are running?
0
 

Author Comment

by:Mr_Difficult
ID: 35063431
Using Microsoft SQL Server 2008 R2 64-bit

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35063473
;WITH Sorting AS (
SELECT ID, Weeks, Seconds, ROW_NUMBER() OVER (ORDER BY Weeks, Seconds) AS RN
FROM RandomData
--ORDER BY Weeks, Seconds
)
SELECT A.*, ((B.Weeks - A.Weeks) * 604800) + B.Seconds - A.Seconds
FROM Sorting A
LEFT JOIN Sorting B ON A.RN = B.RN - 1
ORDER BY A.RN

Open in new window

0
 

Author Comment

by:Mr_Difficult
ID: 35063978
Works like a charm, I will bump up the points a bit more if you can modify the query to only return the TimeDiff over a threshold.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35064014
simply put WHERE condition inside the "Sorting" query

for ex

;WITH Sorting AS (
SELECT ID, Weeks, Seconds, ROW_NUMBER() OVER (ORDER BY Weeks, Seconds) AS RN
FROM RandomData
WHERE Weeks = 161
-- or WHERE Weeks BETWEEN 153 AND 154     etc.
) SELECT ....

Open in new window

0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 35064039
Ar.. sorry, mis-translate "over threshold"


Then should put WHERE condition at the select query

;WITH Sorting AS (
SELECT ID, Weeks, Seconds, ROW_NUMBER() OVER (ORDER BY Weeks, Seconds) AS RN
FROM RandomData
--ORDER BY Weeks, Seconds
)
SELECT A.*, ((B.Weeks - A.Weeks) * 604800) + B.Seconds - A.Seconds
FROM Sorting A
LEFT JOIN Sorting B ON A.RN = B.RN - 1
WHERE ((B.Weeks - A.Weeks) * 604800) + B.Seconds - A.Seconds > 200 -- or >= @Threshold  which must declare/set earlier.
--ORDER BY A.RN
0
 

Author Comment

by:Mr_Difficult
ID: 35064262
Last Question: The ORDER BY A.RN is commented out, is that by design or an accident?
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35064294
It's depend, since to compare by Threshold, order might not important.
However, you can remain the ORDER BY to have the resultset sorting by Weeks, Seconds
0
 

Author Comment

by:Mr_Difficult
ID: 35064357
Cool, I will retain it then. Awesome job by the way.  The code exactly what I want with no goofing around with temporary tables.

Thanks.
0
 

Author Closing Comment

by:Mr_Difficult
ID: 35064393
Awesome job, quick, short and to the point solution.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now