Welcome to WebmasterWorld Guest from 54.161.118.57

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Date/Timestamp help

     
11:23 pm on Feb 14, 2010 (gmt 0)

Junior Member

10+ Year Member

joined:June 30, 2009
posts:74
votes: 0


Hi all,

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...

Thanks in advance for any help.
1:33 am on Feb 15, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


select username from chat_users where login_time >= date_sub(now(), interval 5 minute) order by username asc, login_time desc;

Like that?

In mysql the only difference between timestamp and datetime is the range of dates you can enter into a timestamp field, and of course, timestamp is automatically added. The formats are identical.