Calculate time (SUM)

[raydar2000]

Hi,

 

If someone could please please help me, it would be greatly appreciated

 

I am trying to calculate the time a vehicle has stopped in one place.

 

this is the query that shows where a vehicle has stopped..

 

 

 

SELECT
`vehiclehistory`.`Vehicle`,
`vehiclehistory`.`Date`,
`vehiclehistory`.`Time`,
`vehiclehistory`.`SpeedInst`,
`vehiclehistory`.`Suburb`,
`vehiclehistory`.`Street`,
`vehiclehistory`.`Latitude`,
`vehiclehistory`.`Longitude`,
`vehiclehistory`.`SpeedMax`,
`vehiclehistory`.`SpeedAv`,
`vehiclehistory`.`CourseInst`,
`vehiclehistory`.`CourseAv`
FROM
`vehiclehistory`
WHERE
`vehiclehistory`.`Vehicle` = "1008"
AND
`vehiclehistory`.`Date`= "2007-02-14"
AND
`vehiclehistory`.`SpeedInst` < "1"
AND
`vehiclehistory`.`CourseInst` = `vehiclehistory`.`CourseAv`

ORDER BY
`vehiclehistory`.`Time` ASC

This is a snippet of the data that comes out.

 

 

 

Vehicle Date Time SpeedInst Suburb Street Latitude Longitude SpeedMax SpeedAv CourseInst CourseAv
1008 14/2/2007 07:20:04 0 Punchbowl LUMEAH AV 33.56.187S 151.03.355E 0 0 0 0
1008 14/2/2007 07:22:34 0 Punchbowl ROSSMORE AV 33.55.930S 151.03.357E 0 0 305 305
1008 14/2/2007 07:25:36 0 Punchbowl ROSSMORE AV 33.55.930S 151.03.353E 0 0 305 305
1008 14/2/2007 07:28:38 0 Punchbowl ROSSMORE AV 33.55.931S 151.03.354E 0 0 305 305
1008 14/2/2007 07:46:50 0 Padstow VANNAN L 33.57.164S 151.01.798E 0 0 49 49
1008 14/2/2007 07:49:52 0 Padstow VANNAN L 33.57.164S 151.01.799E 0 0 49 49
1008 14/2/2007 07:52:54 0 Padstow VANNAN L 33.57.164S 151.01.799E 0 0 49 49
1008 14/2/2007 07:55:56 0 Padstow VANNAN L 33.57.164S 151.01.799E 0 0 49 49
1008 14/2/2007 07:58:58 0 Padstow VANNAN L 33.57.164S 151.01.799E 0 0 49 49
1008 14/2/2007 08:01:58 0 Padstow VANNAN L 33.57.164S 151.01.799E 0 0 49 49
1008 14/2/2007 08:04:14 0 Padstow VANNAN L 33.57.164S 151.01.799E 0 0 49 49
1008 14/2/2007 08:22:36 0 Revesby North FITZPATRICK ST 33.56.205S 151.00.175E 0 0 277 277
1008 14/2/2007 08:22:40 0 Revesby North FITZPATRICK ST 33.56.205S 151.00.175E 0 0 277 277
1008 14/2/2007 08:27:30 0 Revesby North FITZPATRICK ST 33.56.197S 151.00.149E 0 0 121 121
1008 14/2/2007 08:30:32 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.149E 0 0 121 121
1008 14/2/2007 08:33:32 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.150E 0 0 121 121
1008 14/2/2007 08:36:34 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.150E 0 0 121 121
1008 14/2/2007 08:39:36 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.150E 0 0 121 121
1008 14/2/2007 08:42:38 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.149E 0 0 121 121
1008 14/2/2007 08:45:40 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.149E 0 0 121 121
1008 14/2/2007 08:48:40 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.149E 0 0 121 121
1008 14/2/2007 08:51:42 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.149E 0 0 121 121
1008 14/2/2007 08:54:44 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.149E 0 0 121 121
1008 14/2/2007 08:57:46 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.149E 0 0 121 121
1008 14/2/2007 09:00:46 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.149E 0 0 121 121
1008 14/2/2007 09:02:06 0 Revesby North FITZPATRICK ST 33.56.196S 151.00.149E 0 0 121 121

 

So what i am trying to do is group the stops together then get the total time the vehicle was stopped there.

 

So in Rossmore Ave, Punchbowl the vehicle was there from 07:22:34 to 07:28:38 = 6mins or so...

 

VANNAN L, Padstow 07:46:50 to 8:04:14 = 18 mins or so...

 

is this a job for PHP?

 

i hope this make sense....

 

Thank you in advance.

 

 

[btherl]

That's a job for php.  To do it in SQL is just too messy, and probably quite inefficient.

 

I would start by converting the timestamps into an easier format to work with (perhaps with strotime()), and then have a loop going through all the results, checking if the location is the same as the previous one.  If it's the same, then update the time for that stop.  If it's different, then finish the previous stop and start calculating for a new stop.

[fenway]

You can do this in MySQL with user variables, if you order the results correctly.

[artacus]

I wish you hadn't broken your datetime into two columns.

But this is definitely a problem for SQL.

 

What you'll do is group by each stop, either by lat/lon or description. (Be careful when  you are doing your groups that  you take into consideration the fact that a vehicle may stop at a single location more than once in a day.) Then use MIN() and MAX() of your time field to get the range.

MySQL has a TIMEDIFF() function for this. But sometimes I find it easier to use TIME_TO_SEC() and get the difference in seconds.

 

And obviously if you are going to have vehicles out on runs that go past midnight, you are going to have to merge your date and time values back into a single value before you perform these calculations.