Msg#: 4080184 posted 11:23 pm on Feb 14, 2010 (gmt 0)
I am trying to make a section on my site which displays users currently online in a chatroom in the last 5 minutes.
Each time a user logs into chat, or types a message, the time is stored in the database. The only problem is that the time is stored in this format: 2010-02-15 11:56:26
So basically i need to select all the rows from the table where the stored time is less than the current time minus 5mintues.
I could turn that into a timestamp, but to do that i would have to run a query, and then another to get the online users in the last 5 minutes. The problem is that I want the original query to give me the online users in the last 5 minutes.
So i need advice on the most efficient to go about this. There is obviously a username field as well as a time field in the database table.
My initial thoughts were to somehow convert the current time timestamp(minus 5 minutes) into the same format as that stored in the database, subtract the two values in the query and get all the rows larger than the result of that subtraction. But i dont think you can subtract this format anyway...