Welcome to WebmasterWorld Guest from 54.163.34.237

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Date/Timestamp help

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

Junior Member

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

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members