Heya,

I have a table that looks like the following:


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:

IP - SECONDS
86.11.133.28 - 420
23.12.392.1 - 0

So you want to group by IP address, then get the difference between the earliest date and the latest date?


SELECT TIMEDIFF(MAX(datetime_column), MIN(datetime_column))
FROM table1
GROUP BY ip_address

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?

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.