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...

i hope this make sense....

Thank you in advance.

[raydar2000]

Hi!

I have done it! ( I THINK....)

thank you for your help... timediff!!!!!

below is the query in case you or anyone who stumbles across the thread is interested..

SELECT
 MIN(`vehiclehistory`.`Time`),
MAX(`vehiclehistory`.`Time`) ,
 
timediff(MAX(`vehiclehistory`.`Time`) ,MIN(`vehiclehistory`.`Time`) ) as idle,
 
`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`
 
 
GROUP BY
`vehiclehistory`.`CourseAv`
 
ORDER BY
`vehiclehistory`.`Time` ASC

I know the group clause is incorrect.... will sort that out now...

[ronverdonk]

Glad you sorted that out by yourself. Good luck!

Ronald :cool: