MySQL time since one minute ago query returning weird results?

Forum Moderators: open

Message Too Old, No Replies

MySQL time since one minute ago query returning weird results?

Chat room list showing folks obviously not signed in.

JAB Creations

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

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();";

$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. ;)