Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate Time Between Records

    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

  • #2
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    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?
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #3
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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..

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


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

  • #4
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    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?

    Code:
    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 here.
    Last edited by StupidRalph; 02-25-2007 at 12:57 AM.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #5
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  • #6
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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..

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

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •