# Difference between times for each ID

**URL:** <https://www.phphelp.com/t/difference-between-times-for-each-id/15630>\
**Category:** MySQL database\
**Created:** [January 20, 2012, 10:21pm UTC](https://www.phphelp.com/t/difference-between-times-for-each-id/15630 "2012-01-20T22:21:44Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tony](https://www.phphelp.com/user_avatar/www.phphelp.com/tony/32/429_2.png) [@Tony](https://www.phphelp.com/u/Tony)\
**Post date:** [January 20, 2012, 10:21pm UTC](https://www.phphelp.com/t/difference-between-times-for-each-id/15630/1 "2012-01-20T22:21:44Z")

</div>

Hello guys I need help cause I’m not really an expert with complex queries in MySql… What I need is this:

I am developing a small analytics system… what I have is one table like this

±-----------±------------±-----------±------------±-----------+  
| ID | IP | page | lastpage | time |  
±-----------±------------±-----------±------------±-----------+  
| 1 | x.x.x.x | … | … | 0001 |  
±-----------±------------±-----------±------------±-----------+  
| 2 | x.x.x.x | … | … | 1111 |  
±-----------±------------±-----------±------------±-----------+  
| 3 | x.x.x.x | … | … | 1113 |  
±-----------±------------±-----------±------------±-----------+  
| 3 | x.x.x.x | … | … | 1114 |  
±-----------±------------±-----------±------------±-----------+  
| 3 | x.x.x.x | … | … | 1120 |  
±-----------±------------±-----------±------------±-----------+  
| 2 | x.x.x.x | … | … | 1114 |  
±-----------±------------±-----------±------------±-----------+  
| 1 | x.x.x.x | … | … | 0004 |  
±-----------±------------±-----------±------------±-----------+

Ok ID is the session id it’s one for each user… IP, page and lastpage are not relevant… what i’m interested in is the difference between the min time and the max time for each distinct ID… how do I do that?

example:

±-----------±------------±-----------±------------+  
| ID | MIN | MAX | DIFF |  
±-----------±------------±-----------±------------+  
| 1 | 0001 | 0004 | 3 |  
±-----------±------------±-----------±------------+  
| 2 | 1111 | 1114 | 3 |  
±-----------±------------±-----------±------------+  
| 3 | 1113 | 1120 | 17 |  
±-----------±------------±-----------±------------+

The time column is unix timestamp btw…

Thank you guys!

---

<div class="post-metadata">

**Author:** ![phphelp](https://www.phphelp.com/letter_avatar_proxy/v4/letter/p/3ec8ea/32.png) [@phphelp](https://www.phphelp.com/u/phphelp)\
**Post date:** [January 20, 2012, 10:47pm UTC](https://www.phphelp.com/t/difference-between-times-for-each-id/15630/2 "2012-01-20T22:47:23Z")

</div>

So you need to group records by ID and use [MySQL aggregate functions](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html) MAX() and MIN(). Your sql query will look like this:

```auto
SELECT ID, MAX(`time`) - MIN(`time`) AS diff FROM MyTable GROUP BY ID
```

---

<div class="post-metadata">

**Author:** ![Tony](https://www.phphelp.com/user_avatar/www.phphelp.com/tony/32/429_2.png) [@Tony](https://www.phphelp.com/u/Tony)\
**Post date:** [January 21, 2012, 12:05am UTC](https://www.phphelp.com/t/difference-between-times-for-each-id/15630/3 "2012-01-21T00:05:38Z")

</div>

You are the man! Thank you so much!

---

<div class="post-metadata">

**Author:** ![Tony](https://www.phphelp.com/user_avatar/www.phphelp.com/tony/32/429_2.png) [@Tony](https://www.phphelp.com/u/Tony)\
**Post date:** [January 21, 2012, 12:11am UTC](https://www.phphelp.com/t/difference-between-times-for-each-id/15630/4 "2012-01-21T00:11:37Z")

</div>

Oh I forgot one thing though… I also have to get the average of all those values returned… so for example if i have 5 rows 15, 57, 67, 54, 16 under the new ‘column’ diff now i need their average…

---

<div class="post-metadata">

**Author:** ![Tony](https://www.phphelp.com/user_avatar/www.phphelp.com/tony/32/429_2.png) [@Tony](https://www.phphelp.com/u/Tony)\
**Post date:** [January 21, 2012, 1:01am UTC](https://www.phphelp.com/t/difference-between-times-for-each-id/15630/5 "2012-01-21T01:01:22Z")

</div>

Sorry I figured it… 😛

```auto
SELECT AVG(diff) FROM (SELECT ID, MAX(time) - MIN(time) AS diff FROM MyTable GROUP BY ID) AS NewTable
```

Thanks again