...

View Full Version : Calculate Time Between Records



raydar2000
02-19-2007, 01:27 AM
Hi,

I have been trying to do this for days and would appreciate some help!

This database was designed by someone else and i have been lumped with writing some reports!

I have a table that stores GPS information from vehicles.
I am trying to write a report that will show a vehicles "idle time"

Below is the table Structure.

Field Type
Date date
Time time
Vehicle int(11)
Latitude char(11)
Longitude char(12)
Street char(50)
Suburb char(50)
Height int(11)
SpeedInst int(11)
SpeedMax int(11)
SpeedAv int(11)
SatCount int(11)
SatsInView int(11)
CourseInst int(11)
CourseAv int(11)

As you can imagine, it has ALOT of records

So i wrote this query to narrow down the records where the vehicle is idle...

SELECT
`vehiclehistory`.`Date`,
`vehiclehistory`.`Time`,
`vehiclehistory`.`Vehicle`,
`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`.`CourseAv` = `vehiclehistory`.`CourseInst`
GROUP BY
`vehiclehistory`.`CourseAv`
ORDER BY
`vehiclehistory`.`Time` ASC


FINALLY! What i am trying to do is calculate the time between the records that the query displays... that will give me the idle time...

Sorry for the long story but felt i should give you all the information.

Thank you!

Ray

StupidRalph
02-24-2007, 09:34 AM
Could you find the records with the Speed Instrument for that vehicle is >=1 And then use the time TIMEDIFF() or am I totally off track here?

raydar2000
02-24-2007, 09:43 PM
Hi!

Turns out i'm more of an idiot than i thought!

Here is what i am ACTUALLY trying to do...

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.

StupidRalph
02-25-2007, 12:53 AM
So are you interested in the idle time according to street which street they're on? Or just the total idle time? Have you checked out how the TIMEDIFF() works?



SELECT TIMEDIFF('LAST_RECORD','FIRST_RECORD');
SELECT TIMEDIFF('09:02:06','07:20:04') /*Will return 01:42:02*/;



Will return the total time idle....01:42:02.

Learn about the TIMEDIFF FUNCTION (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html) here.

raydar2000
02-25-2007, 02:51 AM
Hi!

Thank you for that... i would like to get the idle time based on which street they are on.... problem is that they visit the same street more than once a day so i am not sure what to do about that... perhaps another field... but street would be a great start!

raydar2000
02-25-2007, 05:02 AM
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...

thank you again for your time...

guelphdad
02-25-2007, 04:27 PM
Your GROUP BY clause is not correct when it doesn't contain all non-aggregate columns in your select statement. See GROUP BY HIDDEN FIELDS in the mysql manual for more details.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum