CODEWITHSUNDEEP
mysqlsql
matt
matt

Reputation: 2352

MySQL count time spent on site by user

I have a MySQL table like this:

+-----+---------+-------+------------+
|  Id |  UserId |  Page |  Timestamp |
+-----+---------+-------+------------+

And it contains a row for each page loaded by users:

+-------+--------+---------+--------------+
|     1 |  user1 |  page_x |  1393940340  |
|     2 |  user1 |  page_x |  1393940348  |
|     3 |  user1 |  page_x |  1393940390  |
|     4 |  user2 |  page_x |  1393940740  |
|     5 |  user2 |  page_x |  1393940781  |
|     6 |  user3 |  page_x |  1393910220  |
|     7 |  user1 |  page_x |  1393945555  |
|     8 |  user1 |  page_x |  1393945565  |
+-------+--------+---------+--------------+

I would like to calculate how much time each user has spent on the site. As you can see for row 1, 2 and 3 (that were visited by user1) are easy to calculate as clearly the user spent 50 seconds but then on row 7 the same user returned to the site later on, if I keep counting as usual it will say that the user spent several hours or days as opposed to just 60 seconds.

Anyone has any idea on how to overcome this?

I though of maybe adding some limits, like if the time spent from one page to the other is over ~20 minutes then don't count it but I'm not sure how to add this to a MySQL query.

Upvotes: 1

Views: 1741

Answers (1)

Hamidreza
Hamidreza

Reputation: 3128

You can do something like this,it can give you some clues to solve the problem:

SELECT USERID,SUM(RES) RESULT FROM
(SELECT T1.USERID,
CASE WHEN T1.USERID = T2.USERID THEN T2.TIMESTAMP - T1.TIMESTAMP ELSE 0 END RES
 FROM 
TABLE1 T1 LEFT OUTER JOIN TABLE1 T2
ON T1.ID = T2.ID - 1)TAB1
GROUP BY USERID;

SQL Fiddle

Upvotes: 1

Related Questions