Time difference between multiple times and dates

[dietmarhannam]

I am having a problem working out the logic for sorting out a date
series that includes times as well.
I have some data that looks at computer uptime, and I only want to see
how long the server is off for when rebooted to work out the total
time over the year.

Here's some of the data I have:

07/02/2008 09:52:22
07/02/2008 09:56:10
11/02/2008 19:59:54
11/02/2008 20:03:43
12/02/2008 14:43:57
12/02/2008 14:47:53
22/02/2008 09:47:46
22/02/2008 09:51:51
12/03/2008 19:29:51
12/03/2008 19:33:59

As you can see, all server reboots usually happen within the same day.
What I am after is a way to select a date (say 11/02/2008 for the
example) and then subtract the time it was rebooted (19:59:54) from
the time it was back up and running again (20:03:43) to get a time in
mins that the computer was unavailable. This would then need to be
done throughout the list.

Any ideas of where I should start? I have some experience with vb but
most of the work I have done is within Access itself.

Dietmar

 

[Allen Browne]

Assuming the date and time are in a Date/Time field named LogDT, use a
subquery to get the previous reboot time from the same table. Then use
DateDiff() to calculate the difference in minutes.

Something like this:

SELECT DateDiff("n", [LogDT]
(SELECT Max(LogDT) As LastBoot
FROM Table1 AS Dupe
WHERE Dupe.LogDT < Table1.LogDT)
AS MinutesSinceLastReboot
FROM Table1;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html#AnotherRecord