homepage Welcome to WebmasterWorld Guest from 54.237.125.89
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Date/Timestamp help
Marked




msg:4080186
 11:23 pm on Feb 14, 2010 (gmt 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.

 

rocknbil




msg:4080220
 1:33 am on Feb 15, 2010 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved