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 4 of 4
  1. #1
    New Coder
    Join Date
    Jan 2007
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Angry Complex difference between two dates

    Heya,

    I have a table that looks like the following:

    Code:
    IP - DATETIME
    86.11.133.28 - 2009-02-02 23:11:11
    23.12.392.1 - 2009-02-03 23:14:11
    86.11.133.28 - 2009-02-02 23:18:11
    What I would like to do, is find out the difference between the dates, based on IP address. Also, its worth noting that its not a simple difference between Date A and date B, as they may be date C and date D too to add up for that ip address.

    What I would like is the results to be something like this:

    Code:
    IP - SECONDS
    86.11.133.28 - 420
    23.12.392.1 - 0
    Last edited by jimjamjo1; 03-10-2009 at 03:06 AM.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    So you want to group by IP address, then get the difference between the earliest date and the latest date?

    Code:
    SELECT TIMEDIFF(MAX(datetime_column), MIN(datetime_column))
    FROM table1
    GROUP BY ip_address
    Last edited by Fumigator; 03-10-2009 at 12:31 AM.

  • #3
    New Coder
    Join Date
    Jan 2007
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Fumigator - That is exactly what I'm after! Thank you.

    Would it be possible to add in the functionality of only finding out the difference when the gap is less than a certain time, 15 minutes for example.

    So if the MIN(time) is older than 15 minutes, then it will try to find the MIN(time) within the 15 mins of the MAX. Does that make sense?

    Another thing is, that I need to be able to work out the total time for an IP address, but split it into blocks.

    For example:
    IP - DATE
    1.1 2009-02-02 00:00:01
    1.1 2009-02-02 00:03.01
    1.1 2009-02-02 00:43:01
    1.1 2009-02-02 00:47:01

    That would return something like:

    1.1 3 mins
    1.1 4 mins

    Hope that makes sense?

  • #4
    New Coder
    Join Date
    Jan 2007
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Dont worry about it dude, i seem to have figure out a simplier solution.

    When I add a row into the database, I assign a cookie code to the row, then i group by the cookie code rather than the IP address. Works really well, thanks for your help dude.


  •  

    Posting Permissions

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