473,237 Members | 1,281 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

Calculating 'time difference' between two records....




I have a data set like so:
UTC_TIME Timestamp NodeID Message Flag
Line
Station
11/19/2005 10:45:07 1132397107.91 1 3 5 1028
1034
11/3/2005 21:05:35 1131051935.20 2 3 5 1009
1043
11/25/2005 21:12:16 1132953136.59 3 3 5 1037
1049
I added the UTC_TIME column in as aconversion of the unix timestamp in

the TIMESTAMP column.
Keeping things simple and straightforward, I need to be able to
calculate the difference from one record to the next (ordered by
TIMESTAMP or UTC_TIME) and output the result into another column in the

table.
NODEID is the unique id.
First, what is the function to do so if, say, I only wanted to
calculate the difference between 2 records as just a basic SELECT
statement. That way I can answer quick question based on any one or two

NODEID's.
Second, how would I further that to continually calculate (as stated
above)?
WOuld this be a stored procedure? A trigger? A cursor?
I am learning as I go here. Any help is greatly appreciated.
R.

Dec 21 '05 #1
4 31158
DATEDIFF ( datepart , startdate , enddate ) is the function that will
return the interval between two dates.
Check the help in QA for the datepart arguments.

As far to tell the difference between two rows: this isnt perfect.
I wrote this using the NORTHWIND database, but it I think it is exacly
what you are looking for:
SELECT
ORDERID,
ORDERDATE,
DATEDIFF(d,orderdate, (SELECT TOP 1 ORDERDATE FROM ORDERS WHERE
ORDERID > O.ORDERID ORDER BY ORDERID )) AS DAYS
FROM ORDERS O

Dec 21 '05 #2
Your narative is vague and does not compile; why did you not post DDL
that everyone could use for testing?

The usual way to model events is to have a (start_time, end_time) pair
in the table because time is a continuum. A NULL end_time means the
event is on-going. The druation of the event is trival at that point.
A row is not a record -- a row has to be a complete fact, while a
record does not.

Dec 22 '05 #3
actually, the way R. set it up, he is using facts in his rows. At x
time, an event occurred. I think you are ASSUMING start and end times,
and attempting to get him to change his data model to become
non-normalized.

Shame on you.

To answer more of the person's question, you can use the datediff
function against teh current time to find out how long ago an event
occured from whenever you run the report.

doug

Dec 22 '05 #4
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't say what time part (days, months, years, hours, minutes,
seconds) you want returned. My example will show hours.

SELECT NodeID, LineStation, MessageFlag, UTC_Time,
DateDiff(Hour, (SELECT MAX(UTC_Time) FROM table_name
WHERE NodeID=T.NodeID AND LineStation=T.LineStation
AND UTC_Time < T.UTC_Time),
UTC_Time) As HoursInterval

FROM table_name As T
WHERE .... < your criteria > ...
ORDER BY NoteID, LineStation, UTC_Time

The "table_name" in both the main query & the subquery should be the
same.

Your data implies that the LineStation is receiving a message
(MessageFlag) at specified times (UTC_TIME). Therefore, I set up the
query to return info on LineStations on the same NodeID. If you just
want to track messages on the NodeID, no matter the LineStation, then
remove the "AND LineStation=T.LineStation" part of the subquery's
criteria (WHERE clause), and remove the LineStation from the ORDER BY
clause.

See the SQL Server BooksOnLine for more info on DateDiff() function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ7HpM4echKqOuFEgEQJWFgCdEMfPaY7aQPYOL66ZJryJpR 4fTMMAoO0r
WEDMaPHxQiZ352eHx0ER72Ur
=YKsf
-----END PGP SIGNATURE-----

ia***********@gmail.com wrote:


I have a data set like so:
UTC_TIME Timestamp NodeID Message Flag
Line
Station
11/19/2005 10:45:07 1132397107.91 1 3 5 1028
1034
11/3/2005 21:05:35 1131051935.20 2 3 5 1009
1043
11/25/2005 21:12:16 1132953136.59 3 3 5 1037
1049
I added the UTC_TIME column in as aconversion of the unix timestamp in

the TIMESTAMP column.
Keeping things simple and straightforward, I need to be able to
calculate the difference from one record to the next (ordered by
TIMESTAMP or UTC_TIME) and output the result into another column in the

table.
NODEID is the unique id.
First, what is the function to do so if, say, I only wanted to
calculate the difference between 2 records as just a basic SELECT
statement. That way I can answer quick question based on any one or two

NODEID's.
Second, how would I further that to continually calculate (as stated
above)?
WOuld this be a stored procedure? A trigger? A cursor?
I am learning as I go here. Any help is greatly appreciated.
R.

Dec 28 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Hans Gruber | last post by:
Hi all, I have been struggling with a problem all day, I have been unable to come up with a working solution. I want to write a function which takes 2 unix timestamps and calculates the...
5
by: Ron Adam | last post by:
Hi, I'm having fun learning Python and want to say thanks to everyone here for a great programming language. Below is my first Python program (not my first program) and I'd apreciate any...
1
by: Tony Williams | last post by:
I have a table with two fields, txtvalue (a number field) and txtmonth ( a date/time field). I want to create a report that shows the difference in value between the value in txtvalue in one value...
3
by: luscus | last post by:
Thanks for all the responses on my first question. Unfortunately the answers I was given were too complicated for my small brain , and neophite condition to understand. So if you could talk down to...
1
by: pauly | last post by:
Hello All, I have been trying to create a query that extracts data from a table and calculates the elapsed time between records. The table called "Imported_table". I need to be able to calculate...
1
by: Sumon | last post by:
Can some one please help me on this? I got 2 records by individual with some fileds, one associates with the min date and the other one assocaites with max date. So lay out looks as follows: ...
8
by: King | last post by:
Hi I have following MS Acess query Here is the query ID Name Prgm ID Client ID Date Start Time End Time Minutes C4 Trisha TIP DEK0703 7 /7 /2006...
3
by: turtle | last post by:
I have a query that returns the results of a process. The start time for Step 2 is the End Time for step 1. I need to know how long each step takes. My current query Product ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.