Time Calculations

1. Junior Member
Join Date
Dec 1969
Posts
10

## Time Calculations

Can someone help with this time calculation problem? I have a query that gets two times, a start time and a stop time. I need to calculate how long the duration of the run was. I am able to subtract the start time from the stop time to receive either a decimal or varchar showing the minutes. How do I add the minutes then?<BR><BR>Example: The processing line is down 3 times in 1 hour. Each time, it logs to the database writing the start & stop time of the down time. I subtract the numbers to arrive at ????? so I can add them to calculate the total down time for that hour?<BR><BR>Please fill in the ?????? area with the appropriate response, then explain how I get a result set back in minutes please...<BR><BR>Thanks,<BR><BR>Bret Hill<BR>Ocean Spray Cranberries

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Which DB?

If it&#039;s either SQL Server or Access, then the following should work:<BR><BR>SELECT Sum( DateDiff(&#039;n&#039;,startTime,endTime) ) AS totalDownTime<BR>FROM table<BR>WHERE ...<BR><BR>** BUT **<BR><BR>But I don&#039;t think this works well.<BR><BR>One problem you have: Suppose the startTime is 3:58 PM and the stopTime is 4:03 PM.<BR><BR>You can&#039;t simply subtract the two and get 5 minutes, because the right answer is 2 minutes in the 3PM hour and 3 minutes in the 4PM hour, yes?<BR><BR>And I don&#039;t think you are going to do that with a simple SQL query.<BR><BR>I think you will actually need a tad bit of moderate VBScript coding to make the report come out right.<BR><BR>What do you think?<BR><BR>p.s.: Where are you? Washington or East Coast? <BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## I played with it some...

...and I *think* that something like this works.<BR><BR>This if for Access. The IIF stuff has to be replaced with CASE WHEN for SQL Server.<BR><BR>ANYWAY...<BR><BR>*IF* you add another table to your DB (it will be a FIXED table, but it&#039;s needed) that looks like this:<BR> TABLE: Blocks<BR> startBlock : DateTime<BR> endBlock : DateTime<BR>and the contents of the table are simply 24 records, one per hour, that look like this:<BR> 0:00:00 AM -- 0:59:59 AM<BR> 1:00:00 AM -- 1:59:59 AM<BR> ... etc. ...<BR> 11:00:00 PM -- 11:59:59 PM<BR><BR>And then you will JOIN that table to your logs table and use it to drive the reporting by time block (by hourly period)...<BR><BR>SELECT DateDiff(&#039;n&#039;, <BR> IIF( startTime&#062;startBlock, startTime, startBlock ),<BR> IIF( endTime&#060;endBlock, endTime, endBlock )<BR> ) AS downTime, logDate, startBlock, endBlock<BR>FROM downTimeLog, timeBlocks<BR>WHERE startTime BETWEEN startBlock AND endBlock<BR> OR endTime BETWEEN startBlock AND endBlock<BR>ORDER BY logDate, startBlock, startTime<BR><BR>Then this *ALMOST* does the trick.<BR><BR>The only place is falls down is if a downtime period slops across two days. That is, from (say) 11:58:00 PM on one day until 0:05:15 AM on the next day. Then you will get a totally bogus result.<BR><BR>With a *LOT* more complication to the query, that could be fixed, but the better way to fix it (if you can!) is to fix the data entry process so that it never happens. If there&#039;s a breakdown across midnight, have it entered as two separate breakdowns.<BR><BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## Actually, if this is Access...

...then I think there&#039;s an easy way to fix that "across midnight" problem.<BR><BR>But it requires that the startTime and endTime values in the log are *complete* date/time values (that is, #1/14/2003 11:48:00PM#, for example).<BR><BR>SELECT DateDiff(&#039;n&#039;, <BR> IIF( TimeValue(startTime)&#062;=startBlock, startTime, (DateValue(startTime)+startBlock) ), <BR> IIF( TimeValue(endTime)&#060;=endBlock, endTime, (DateValue(startTime)+endBlock) ) <BR> ) AS downTime, DateValue(startTime), startTime, endTime, startBlock, endBlock <BR>FROM downTimeLog, timeBlocks <BR>WHERE TimeValue(startTime) BETWEEN startBlock AND endBlock <BR> OR TimeValue(endTime) BETWEEN startBlock AND endBlock <BR>ORDER BY DateValue(startTime), startBlock, startTime <BR><BR>That still won&#039;t work if you have a downtime that extends across *3* days (for example, from Thursday at 10PM until Saturday at 3AM), but I *think* it solves the across-midnight problem.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Scratch both of those...

Bogus.<BR><BR>Tried them both. Not even close. Sigh.<BR><BR>Sure you don&#039;t want to do the processing in VBScript, instead?<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Okay, this worked...

(1) I created an auxiliary table, named "Hours" that had only one field in it, "startHour" which is an integer numeric field.<BR><BR>And then I have 24 records in the table, with each possible value for startHour ranging from 0 to 23.<BR><BR>That is:<BR> startHour<BR> 0<BR> 1<BR> 2<BR> 3<BR> ...<BR> 22<BR> 23<BR><BR>(2) This still doesn&#039;t handle the case of a log period that crosses from one date to the next.<BR><BR>(3) The query:<BR><BR>SELECT DateValue(startTime) AS theDate, startHour, SUM( IIF( Hour(startTime) &#062; startHour OR Hour(endTime) &#060; startHour, 0,<BR> IIF( Hour(startTime) &#060; startHour AND Hour(endTime) &#062; startHour, 60,<BR> IIF( Hour(startTime) = startHour AND Hour(endTime) &#062; startHour, 60-Minute(startTime),<BR> IIF( Hour(startTime) &#060; startHour AND Hour(endTime) = startHour, Minute(endTime),<BR> Minute(endTime) - Minute(startTime) ) ) ) ) ) AS outage<BR>FROM Hours, Log<BR>GROUP BY DateValue(startTime), startHour<BR>ORDER BY DateValue(startTime), startHour;<BR><BR>******<BR><BR>Naturally, you could add a WHERE clause to that to restrict the range of dates you wanted (or even the range of dates and hours, I guess).<BR><BR>

7. Junior Member
Join Date
Dec 1969
Posts
10

## RE: Scratch both of those...

I may have to process it with VBScript. I would like to do all the work in a SQL Stored Procedure, however I am running across similar problems. If it spans multiple hours, you have to do extra work, and if it spans multiple days it becomes extremely complicated very quickly....<BR><BR>Thank you for your input!<BR><BR>Bret Hill

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## Did you see my other msg...

..."Okay, this worked"???<BR><BR>Except for spanning multiple days, it worked great.<BR><BR>I *can* get it to work spanning days, but I wonder if that is worth it.<BR><BR>Since you mentioned Stored Proc&#039;s I assume you are using SQL Server.<BR><BR>If so, the conversion from IIF to CASE WHEN is easy. The DateValue thing is a bit more of a pain, but CONVERT can do it for you.<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## SQL SERVER VERSION...

...100% untested, but...<BR><BR>SELECT CONVERT(DateTime, CONVERT(VarChar, startTime, 102), 102) AS theDate, <BR> startHour, <BR> SUM( CASE WHEN Hour(startTime) &#062; startHour OR Hour(endTime) &#060; startHour THEN 0<BR> WHEN Hour(startTime) &#060; startHour AND Hour(endTime) &#062; startHour THEN 60<BR> WHEN Hour(startTime) = startHour AND Hour(endTime) &#062; startHour THEN 60-Minute(startTime)<BR> WHEN Hour(startTime) &#060; startHour AND Hour(endTime) = startHour THEN Minute(endTime)<BR> ELSE Minute(endTime) - Minute<BR> END<BR> ) as outage<BR>FROM Hours, Log<BR>GROUP BY theDate, startHour<BR>ORDER BY theDate, startHour<BR><BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•