Calculating running time difference between two records

  • Thread starter Thread starter iamonthisboat
  • Start date Start date
I

iamonthisboat

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.
 
Create a query into this table.

In a fresh column of the Field row, enter a subquery like this:
NextTime: (SELECT TOP 1 Dupe.UTC_TIME
FROM Table1 AS Dupe
WHERE Dupe.UTC_TIME > Table1.UTC_TIME
ORDER BY Dupe.UTC_Time, Dupe.NodeID)

Assuming that UTC_TIME is a Date/Time field, you can then use DateDiff() to
calculate the difference in time, e.g.:
Seconds: DateDiff("s", [UTC_TIME], [NextTime])

The crucial thing is NOT to store this in the table, but to calculate it as
needed. If that's a new concept, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

The results of the subquery will be read-only. If you need editable results,
use DMin() to get the next time. Post a reply if you need more info on that.
 
Back
Top