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

[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.

[rcamarda]

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

[--CELKO--]

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.

[Doug]

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

[MGFoster]

-----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.