I have a table visits
that looks like this:
+--------------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| vis_id | int(11) | NO | MUL | NULL | |
| unit | int(11) | NO | MUL | NULL | |
| time_in | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
| time_out | timestamp | NO | MUL | 0000-00-00 00:00:00 | |
| in_username | varchar(16) | NO | MUL | NULL | |
| out_username | varchar(16) | NO | MUL | NULL | |
+--------------+-------------+------+-----+---------------------+----------------+
I use this to keep track of visitors coming in and out of my building.
What I'd like is to be able to see the "average length of visit" in hours by a particular visitor (defined by vis_id
). If the average visit length is less than an hour it should round up to 1.
Here's an example of what I'd like for output:
+--------+------+----------+
| vis_id | unit | avg_time |
+--------+------+----------+
| 156 | 216 | 5 |
| 1230 | 103 | 2 |
| 533 | 112 | 1 |
| 802 | 201 | 3 |
| 1445 | 431 | 4 |
+--------+------+----------+
Suggestions?