A (semi-?)challenging MySQL query for y'all
Date: 01/30/07
(PHP Community) Keywords: php, mysql, sql
I know this isn't a MySQL community, but I figured since PHP goes hand-in-hand with it, someone here could help me :)
I'm creating a tracking system for our labs that's recording login and computer information every time a user logs in. The schema is:
| tID | int(10) unsigned
| tUsername | varchar(32)
| tHostname | varchar(16)
| tAgree | tinyint(1)
|tIp | varchar(16)
| tLoginTime | datetime
| tLogoutTime | datetime
And some example data:
tid tuser thost tagree tip tlogintime tlogouttime
256 user1 host79 1 123.213.289.244 2007-01-25 06:44:11 2007-01-25 10:11:09
257 user5 host87 1 123.213.289.101 2007-01-25 09:14:12 2007-01-25 09:39:28
258 user3 host59 1 123.213.289.180 2007-01-25 09:27:37 2007-01-25 10:11:37
259 user4 host41 1 123.213.289.39 2007-01-25 09:41:21 2007-01-25 10:25:57
260 user2 host65 1 123.213.289.55 2007-01-25 10:05:45 2007-01-25 10:21:36
I want to find a list of hostnames that were logged in between a given time period (say, one hour). In this example, I want the 4 hostnames that were being used on 2007-01-25 between 09:00:00 and 09:59:59. What query would I use to pull out those four hostnames? I'm being thrown off by the hostname with tid=256, which was being used between 9 and 10 but it wasn't logged in or out during those times. Can I even capture the data I need with the current schema? If I need to change something, that's not a problem.
If y'all could help me out I'd appreciate it. Thanks!
Edit: A friend and I got it figured out:
select thostname from login where time(tlogintime) < time('09:59:59') AND time(tlogouttime) > time('09:00:00') AND date(tlogintime) = date('2007-01-25');
Source: http://community.livejournal.com/php/535296.html