Length of time

[Canman2005]

Hi all

 

I'm building a very simple stats package for a website im doing, currently I hold the

 

Time

Date

URL

IP Address

 

Each time someone goes onto a page, then the above details are captured and stored in an sql database.

 

What I want to do is to store the length of time someone has been on each page, so as well as capturing the "Time", "Date", "URL" & "IP Address", I want to capture "Length" and store this like 00:04:23.

 

Is it possible to store the length of time someone has been on a page?

 

Thanks in advance

 

Ed

[Canman2005]

Can anyone help?

[Wildbug]

Here's one way:

mysql> DESCRIBE temp;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| ts    | int(10) unsigned | YES  |     | NULL    |       |
| url   | varchar(255)     | YES  |     | NULL    |       |
| ip    | varchar(64)      | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> SELECT * FROM temp;
+------------+---------------------------------------------------------------+-------------+
| ts         | url                                                           | ip          |
+------------+---------------------------------------------------------------+-------------+
| 1185903285 | http://www.example.com/index.php                              | 127.0.0.1   |
| 1185903297 | http://www.example.com/~dingo/ate-my-baby.html                | 127.0.0.1   |
| 1185903381 | http://www.example.com/~dingo/about.html                      | 127.0.0.1   |
| 1185903469 | http://www.example.com/store/dingo-traps.php                  | 127.0.0.1   |
| 1185903200 | http://www.example.com/splash.aspx                            | 127.0.0.1   |
| 1185903270 | http://www.example.com/splash.aspx                            | 192.168.0.1 |
| 1185903310 | http://www.example.com/index.php                              | 192.168.0.1 |
| 1185903315 | http://www.example.com/~mp3s/index.html                       | 192.168.0.1 |
| 1185903355 | http://www.example.com/~mp3s/Dream%20Theater/index.html       | 192.168.0.1 |
| 1185903382 | http://www.example.com/~mp3s/Dream%20Theater/Awake/index.html | 192.168.0.1 |
| 1185903333 | http://www.example.com/store/shopping_cart.php                | 127.0.0.1   |
+------------+---------------------------------------------------------------+-------------+
11 rows in set (0.00 sec)

mysql> SET @a:=0,@b:=0; SELECT a.url,SEC_TO_TIME(AVG(b.ts-a.ts)) AS "Average Duration",COUNT(*) AS Hits
FROM
(SELECT url,ip,@a:=@a+1 AS count,ts FROM temp ORDER BY ip,ts) a 
JOIN (SELECT ip,@b:=@b+1 AS count,ts FROM temp ORDER BY ip,ts) b 
ON a.ip=b.ip AND b.count=a.count + 1
GROUP BY a.url;
Query OK, 0 rows affected (0.00 sec)

+---------------------------------------------------------+------------------+------+
| url                                                     | Average Duration | Hits |
+---------------------------------------------------------+------------------+------+
| http://www.example.com/index.php                        | 00:00:08         |    2 |
| http://www.example.com/splash.aspx                      | 00:01:02         |    2 |
| http://www.example.com/store/shopping_cart.php          | 00:00:48         |    1 |
| http://www.example.com/~dingo/about.html                | 00:01:28         |    1 |
| http://www.example.com/~dingo/ate-my-baby.html          | 00:00:36         |    1 |
| http://www.example.com/~mp3s/Dream%20Theater/index.html | 00:00:27         |    1 |
| http://www.example.com/~mp3s/index.html                 | 00:00:40         |    1 |
+---------------------------------------------------------+------------------+------+
7 rows in set (0.00 sec)

 

Don't store date and time separately.  Use either an INT UNSIGNED and store as a Unix timestamp or use DATETIME.  Also, storing IPs as integers is more efficient (although I didn't do that in the example).  See INET_ATON() in the MySQL manual.

[Canman2005]

thanks for that. I have tried your suggestion, but I get the error

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Av (SELECT url,ip,@a:=@a+1 AS count,ts FROM temp ORDER BY ip,ts) a JOIN (SELECT' at line 1

 

What am I doing wrong?

 

Thanks

 

Ed

[hitman6003]

When you copy and pasted the above, did you get all of the query?  cause it looks like you didn't.

 

SET @a:=0,@b:=0; 
SELECT a.url,SEC_TO_TIME(AVG(b.ts-a.ts)) AS "Average Duration",COUNT(*) AS Hits
FROM
(SELECT url,ip,@a:=@a+1 AS count,ts FROM temp ORDER BY ip,ts) a 
JOIN (SELECT ip,@b:=@b+1 AS count,ts FROM temp ORDER BY ip,ts) b 
ON a.ip=b.ip AND b.count=a.count + 1
GROUP BY a.url

[Canman2005]

Hi

 

Is there anyway to speed up this query? It seems to crash the server when run on a table with anymore than 40,000 records