MySQL Date Functions

wxdqz

New Member
Hey guys. I've been trying to figure out how to implement a system that can count how many active sessions there are on my site and display it back to the user in a format like "currently 15 users logged in". I'm using PHP4 sessions and using MySQL to store the session data. In my session table, i have a field (time_stamp) of format (TIMESTAMP(14)).

Now, i've come to the conclusion that the best way to count the users online, is to try to figure out how many active sessions there are in a certain amount of time. Its not the most accurate, but when dealing with sessions, you can never be sure just how many you do have active due to the inherent problems with using sessions. So anway, i figured if I can figure out how many active sessions there were in the last 5 minutes, I can then present that to the user. Its not accurate, but it will serve the purpose. (and no this doesn't need to be an acurate number of users).

After looking at the MySQL date functions, it seems that I might be able to get what I want from a simple SQL statment. Avoiding the work on the PHP side. So basicaly I want (and this is not actual code just the flow)

"SELECT time_stamp FROM sessions where time = 5 minutes ago"

Then I would just have to count the rows returned, and I would get my user count.

<!-- m --><a class="postlink" href="http://www.mysql.com/documentation/mysql/commented/manual.php?section=Date_and_time_functions">http://www.mysql.com/documentation/mysq ... _functions</a><!-- m -->

MySQL has a DATE_SUB() function that would seem to do the trick, but unfortunatly I can't get it to work. Here's the statement that has produced mixed results:

"SELECT time_stamp, DATE_SUB(t_stamp, INTERVAL "5:00" MINUTE_SECOND) AS diff FROM sessions"

This almost works... except it gives me the stored date-time values with 5 minutes added.

Has anyone tried to do this the way I'm going about it? Or am I going totally the wrong way about this? Any sugestions would be appriciated. Thanks.
 
Back
Top