homepage Welcome to WebmasterWorld Guest from 174.129.80.166
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe and Support WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL time since one minute ago query returning weird results?
Chat room list showing folks obviously not signed in.
JAB Creations




msg:4053668
 11:25 am on Jan 4, 2010 (gmt 0)

This past new year's day I noticed an odd bug where people who were not in the chat room (since I test most stuff at localhost right?) were in the room.

At first I thought I was creating an incorrect date with PHP however after I checked it seemed to be just fine. Here is the PHP code just in case I've got it wrong though...

$date_past = date('o-m-d H:i:s',time() - 1 * 30);

Here are some queries I've tried all of which return oddball results which are unquestionably wrong...

$query = "SELECT username, chat_date FROM accounts WHERE chat_date BETWEEN '".$date_past."' AND NOW()";

$query = "SELECT username, chat_date FROM accounts WHERE chat_date <= '".$date_past."' AND chat_date >= NOW()";

$query = "SELECT username, chat_date FROM accounts WHERE chat_date > '".$date_past."'";

Either I'm doing something wrong or encountered a bug of some sort...I'm putting my money that I am doing something wrong here though. Thoughts please?

- John

 

whoisgregg




msg:4054056
 11:39 pm on Jan 4, 2010 (gmt 0)

I've always used "Y-m-d H:i:s" in the date function. Have you tested to see if it's the use of "o" instead of "Y" for the year that causes the bug?

I also find that mixing PHP generated timestamps and MySQL timestamps (comparing NOW() against time()) can cause trouble. Wherever possible, I try to generate all timestamps in the same way (pseudocode):

$query = "SELECT username, chat_date FROM accounts WHERE chat_date BETWEEN (NOW() - INTERVAL 30 SECONDS) AND NOW();";

Or

$query = "SELECT username, chat_date FROM accounts WHERE chat_date BETWEEN '".date('Y-m-d H:i:s', strtotime("-30 seconds"))."' AND '".date("Y-m-d H:i:s")."';";

Which I pick is entirely based on whether I'm feeling more sql-y that day or php-y. ;)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved