Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Date/Timestamp help



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

5+ Year Member

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)

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

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.

Featured Threads

Hot Threads This Week

Hot Threads This Month