Calculate Time Between Records

[raydar2000]

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

[btherl]

That's a tough one for SQL.  I suggest you fetch all those records you are fetchng and do the analysis in php.  SQL works very well with things like sum, average, count, but it's not suited for things like "Find the difference between adjacent pairs of entries).

 

Well, you could use subqueries if you have approriate indexes to find the "next" entry via a subquery.  But in my experience, that's not a good approach.

[raydar2000]

Hi,

 

Thank you for the reply!!

 

I would love to sort it out in PHP... that would mean two tables right?? i am very new to this... If you could maybe point me in the right direction, that would be appreciated.

 

Thanks again

 

Ray

[fenway]

Well, you can compare the current record with the previous record with user variables, but that's about it.