Calculate number of hours between 2 times

NoviceMe
10-12-2004, 08:54 AM
Hi,

I am totally new to SQL, and am currently trying to calculate the number of hours that have elapsed between 2 times, then multiply this by a value in a 3rd column as follows to produce the total_hours_spent

s-time e_time staff
01:00:00 03:00:00 2

I have a little knowledge of vba, but this doesn't help me at all. Can anyone show me how I do this with a query? It also needs to consider times that go past midnight i.e. s_time = 23:00:00, e_time = 01:00:00

Cheers :D

loquin
10-12-2004, 10:10 AM
What's your database?

Access, SQL Server, MySQL, Oracle, PostgreSQL, etc., ALL use a variation of SQL to access the data. But, NONE of them are 100% ANSI SQL compliant, and each offers non-standard extensions to perform various functions.

SQL Server, for instance, uses the DateDiff function, while MySQL uses a similar function called DateSub, while PostgreSQL uses the Age function.

tboltfrank
10-12-2004, 04:12 PM
Hi Noviceme,

I remember from another thread, awhile back, that you had said you were going to start learning Access, so I thought would likely be what youíre using. - If so, (as Loquin pointed out), the DateDiff function, would likely be a good way to go.

Try this:

Dim strSQL As String

strSQL = "UPDATE Your_Table SET [Ttl_Hours] = " & _
"DateDiff('h', [S_DateAndTime], [F_DateAndTime])"

DoCmd.RunSQL (strSQL)

Or, if you only need to process an individual record:

' ...
strSQL = "UPDATE Your_Table SET [Ttl_Hours] = " & _
"DateDiff('h', [S_DateAndTime], [F_DateAndTime]) " & _
"WHERE [NameField] = 'John Smith'"
' ...

I configured the Start/Finish Date-Time Fields as "Date/Time" DataType
and Formatted it "General"
ie: 12/12/2004 11:00:00 PM

With the Ttl_Hours field, I used "Number" DataType
and set the field size to: "Double"

These settings and/or the SQL, needs improvement to give you a calculation to both the hour and minute, as the way it is now, doesn't result in giving any credit for minutes past the hour. - Maybe, itíll give you a start., anyway :)

Edit: hmm ... in rereading your post, I realize now that using a standard Date-Time format, apparently isn't going be capable of giving you what you want. At least not while using any of the built in Access date/time format's. - I don't know if you can create a Custom Format, or if maybe there might be a different Function that's better suited to this.

* If you could use the Short Date Format ie: 23:15, and as long as you don't go over 23:59 you could probably use this modified version of the SQL, which would give you a result in hours and decimal minutes. That should work for further calculation's, or could be converted to minutes.

strSQL = "UPDATE Your_Table SET [Ttl_Hours] = " & _
"DateDiff('n', [S_DateAndTime], [F_DateAndTime])/60"

ie: StartTime= 00:00 FinishTime= 23:59 =TotalHours= 23.9833333333333
(23 hours) and (.9833333333333 * 60) = (23 hours and 59 minutes)
or with the example data that you gave, less the format for seconds:
StartTime=1:00 FinishTime=3:00 TotalHours= 2

Edit: After thinking this through some more, it seems to me that you would have to incorporate the Date with the Time, in order to be able to calculate example's that pass midnight.
ie: StartTime=11:15 PM FinishTime=1:00 AM
* I recommend configuring everything like I described originaly:
ie: StartTime=10/12/2004 11:15:00 PM FinishTime=10/12/2004 1:00 AM
* Use the "General Format" Date/Time settings for those fields & keep the TotalHours field size set as a "Double". - Then use the 3rd of the three SQL code examples that I gave.

NoviceMe
10-13-2004, 12:26 PM
loquin, thanks for responding,

unfortunately I am using InfoMaker and SQL Anywhere - old versions!

NoviceMe
10-13-2004, 12:56 PM
Hi Tbolt,

I am using InfoMaker and SQL Anywhere old version (5) - I have to unfortunately, but will be re-building in Access at later date, so your suggestions are excellent for that. It is the DateDiff function I need to use, so far I have got it to do the calculation for the difference and it returns the total hours (SQL Anywhere statements are slightly different to Access)

SELECT start_time, end_time, total_staff,
datediff(hour, start_time, end_time )
* total_staff FROM my_table;

But it does minuses for the hours after midnight an example of a returned value was:

s_time e_time total_staff total_hours
21:00 05:00 4 -64

I think I need to do an if statement or something that checks if the end time is less than the start, ans if it is, it adds a figure to the time if that makes sense

tboltfrank
10-13-2004, 01:13 PM
I also got results with a negative number, when I used test data that passed midnight, that's why I came up with the second idea that would give you results in hours and decimal minutes.

I'm not sure of the syntax for your SQL, but maybe something like this.

SELECT start_time, end_time, total_staff,
datediff(minute, start_time, end_time )
* total_staff FROM my_table/60;

I realize that you would still need an additional function to display your results the way that you want, but the decimal should be fine for calculation's.

NoviceMe
10-13-2004, 01:23 PM
Tbolt,

this will execute, but all results are returned as '0'

SELECT start_time, end_time, total_staff,
datediff(hour, start_time, end_time ) / 60
* total_staff FROM my_table As total_hours;

tboltfrank
10-13-2004, 01:31 PM
Not sure of the syntax, (in Access it's "n"), but with my approach you need to be working with minutes rather than hours

SELECT start_time, end_time, total_staff,
datediff(minute, start_time, end_time ) / 60
* total_staff FROM my_table As total_hours;

NoviceMe
10-13-2004, 02:13 PM
tbolt,

am now using the minutes, which produces the same results as the original - correct for all calculations before midnight, I just need to put some thought into getting it to produce corect results for after midnight.

it is treating the end time as the earlier time and counting backwards, so might something that removes the results from the datediff value (if end_time < start_time) be whats needed, currently trying this but can't get it to work

tboltfrank
10-13-2004, 02:47 PM
Are you sure that your data is in a format that distinguishes between AM and PM and/or the different date's between the start and end times ? ?

NoviceMe
10-13-2004, 03:15 PM
the data I am working with is historical, so I can't add dates to the times(mmm...maybe I can with some thought) it has a date, and the 2 time fields are hh:mm - no AM/ PM - do you think this is my problem?

tboltfrank
10-13-2004, 03:35 PM
I'm pretty sure that you would need to incorporate the dates ...
AM/ PM doesnít need to be visible, but I believe does need to been accounted for.

Other wise when subtracting the large number from the small number,
logically, or should I say mathematically, results in a negative number.

I donít think that thereís a standard function that could do what youíve been attempting.
It seems not possible to me, to even build a custom function to do it, because how will you know the difference between a time span of 11 in the morning today and 1 o'clock tomorrow morning, as different from 11 in the evening today and 1 o'clock tomorrow morning.

NoviceMe
10-13-2004, 03:41 PM
Hi tbolt,

this time calculation was a bugbear for me in some forms i created in vb, in the end I managed to solve it by (and I am sure its not best practice) setting end times that were < than start times as the following day (with other bits of code bolted on). Don't waste anymore time on this, I am sure the solution will come when I least expect it.....meanwhile back to my million other problems with this database...thanks so much for your help and advice - it has got me somewhat closer to where I wanted to be :D

tboltfrank
10-13-2004, 03:58 PM
Yeah, with the data that you've described, unless the underlining values have a date attached, or somehow are otherwise tagged as AM/PM, not necessarily visible to you, but that the database program can see and calculate from, , I don't think that thereís any other factor that you could use, to distinguish morning times, from evening times, from next morning times... :confused:

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum