If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Time difference between multiple times and dates
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 |
#2
|
|||
|
|||
Time difference between multiple times and dates
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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|