websss
asked on
average speed calculation - Date diff help
Hi,
I need to work out the average speed which is calculated as
Average Speed = distance ÷ time
I know the following
Start Time = '2014-09-12 07:03:36.000'
End Time = '2014-09-12 07:06:22.000'
Distance = 6km
So i need to figure out the SQL which works out the TIME as a float:
I'm having issues figuring out the Time as a float (i.e x.x),
if i use
If i change MINUTE to SECOND
if I try both these calculations to work out average speed: (distance ÷ time):
The problem is the MINUTE variation doesn't take into account seconds, so its rounded UP to 3 mins and its not accurate, and the SECONDS variation just doesn't work
I guess i need to use a combination of both, and some how get the remaining seconds and use as a point
i.e.
however i've no idea how to do this
I guess i need to use the SECOND variation which outputs 166 (seconds) and turn that into a minute variation of 2.X ?
HELP
I need to work out the average speed which is calculated as
Average Speed = distance ÷ time
I know the following
Start Time = '2014-09-12 07:03:36.000'
End Time = '2014-09-12 07:06:22.000'
Distance = 6km
So i need to figure out the SQL which works out the TIME as a float:
I'm having issues figuring out the Time as a float (i.e x.x),
if i use
declare @startTime datetime = '2014-09-12 07:03:36.000'
declare @endTime datetime = '2014-09-12 07:06:22.000'
print convert(float, DATEDIFF(minute,@startTime, @endTime))
This outputs 3 (minutes - but rounded up)If i change MINUTE to SECOND
declare @startTime datetime = '2014-09-12 07:03:36.000'
declare @endTime datetime = '2014-09-12 07:06:22.000'
print convert(float, DATEDIFF(SECOND,@startTime, @endTime))
This Outputs 166 (seconds)if I try both these calculations to work out average speed: (distance ÷ time):
print 6 / 166 -- this equals 0 (second variation)
print 6 / 3 -- this equals 2 (minute variation)
The problem is the MINUTE variation doesn't take into account seconds, so its rounded UP to 3 mins and its not accurate, and the SECONDS variation just doesn't work
I guess i need to use a combination of both, and some how get the remaining seconds and use as a point
i.e.
Print 6 / 2.8
however i've no idea how to do this
I guess i need to use the SECOND variation which outputs 166 (seconds) and turn that into a minute variation of 2.X ?
HELP
what unit of measure do you want as the outcome?
Km/hour
Km/minute
Km/second
Km/hour
Km/minute
Km/second
Is this you are looking for?
declare @startTime datetime = '2014-09-12 07:03:36.000'
declare @endTime datetime = '2014-09-12 07:06:22.000'
declare @diff float
SET @diff = DATEDIFF(second,@startTime, @endTime) / 60.0
PRINT 6/@diff
Why don't you convert to 6000 meters / 166 ?
ASKER
sorry looking for Km/hour
declare @startTime datetime = '2014-09-12 07:03:36.000'
declare @endTime datetime = '2014-09-12 07:06:22.000'
declare @distance int = 6
declare @speed float
print DATEDIFF(SECOND,@startTime, @endTime)
set @speed = ( cast(@distance as float)) / cast( DATEDIFF(SECOND,@startTime, @endTime) as float) / 3600.00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what result would you all expect to see in KM/h ?
everyones results differ lots!
from 1 km/ph to 130 km/ph
everyones results differ lots!
from 1 km/ph to 130 km/ph
I think it's you that should give us the answer for that question.
I took a simple calculation. If it took 166 seconds to make 6Km, then how much Km will do in 60 minutes?
I took a simple calculation. If it took 166 seconds to make 6Km, then how much Km will do in 60 minutes?
6/166 = Km/sec * 3600 = Km/Hr
130.1204819277108 Km/Hr
130.1204819277108 Km/Hr
Right PortletPaul. It was what I used for my solution.
if you could run 6 Km in just under 3 minutes would it be very slow or very fast?
3 minutes is 1/20 of an hour so 20*6 Km = 120 Km/Hr
and it has to be more than that figure because 166 seconds is less than 3 minutes (i.e. faster)
3 minutes is 1/20 of an hour so 20*6 Km = 120 Km/Hr
and it has to be more than that figure because 166 seconds is less than 3 minutes (i.e. faster)
Open in new window