[SOLVED] Mysql datetime/timestamp comparison with NOW() not working...

[Niccaman]

This has been driving me nuts for the last 2 hours. I have a timestamp column with a timestamp earlier than now.

It always returns the row to me, when it shouldnt. It should just come up with no results, but it keeps giving me the bloody row!!

 

My statement is:

$query = "SELECT * FROM `stuff` WHERE `daterenewal` > NOW() AND ...";
$query = "SELECT * FROM `stuff` WHERE `daterenewal` < NOW() AND ...";

 

Ive tried everything i can think of, including:

$query = "SELECT * FROM `stuff` WHERE `daterenewal` > CURDATE() AND ...";

$t = time();
$query = "SELECT * FROM `stuff` WHERE UNIX_TIMESTAMP(`daterenewal`) > $t AND ...";

 

Everything i tried failed. Ive even tried converting the column to datetime instead.

[rhodesa]

what is the DataType of daterenewal?

[Niccaman]

erm.. if im not right when i say timestamp, i dont know what u mean.

its got like: "0000-00-00 00:00:00" as its value.

 

Ive tried putting the date within the range that timestamp is targeted too, with no luck.

Ive also tried changing the column type to datetime

[rhodesa]

if it's a DATETIME column, then

$query = "SELECT * FROM `stuff` WHERE `daterenewal` > NOW() AND ...";

should return nothing and

$query = "SELECT * FROM `stuff` WHERE `daterenewal` < NOW() AND ...";

should return the row

 

can you post more code, maybe the problem is elsewhere...

[Niccaman]

$query = "SELECT * FROM `stuff` WHERE `daterenewal` > NOW() AND ...";
$res=mysql_query($query);
  if (mysql_num_rows($res))
  {
       while ($row=mysql_fetch_object($res))
       {
            ... // This is always fulfilled and returning the row with date earlier than now.
       }
  }else
  {
    echo "No search results";
  }

[rhodesa]

what is the output of:

 

<?php
$result = mysql_query("SHOW COLUMNS FROM `stuff`")
  or die('Could not run query: ' . mysql_error());
if (mysql_num_rows($result) > 0) {
  while ($row = mysql_fetch_assoc($result))
    print_r($row);
}
?>

[Niccaman]

Array ( [Field] => daterenewal [Type] => datetime [Null] => NO [Key] => [Default] => 0000-00-00 00:00:00 [Extra] => )

[rhodesa]

Everything looks good. The only thing I can think of is differences in Timezones. Is the output of this what you expect it to be?

 

<?php
$result = mysql_query("SELECT NOW()")
  or die('Could not run query: ' . mysql_error());
print_r(mysql_fetch_assoc($result));
?>

[Niccaman]

Array ( [NOW()] => 2009-06-30 12:36:09 )

 

... Yeh its all good. This is so annoying. Why is it happening!? The condition shouldn't be fulfilled...

[rhodesa]

can you post the FULL SQL query?

[Niccaman]

One step ahead of you. I simply tested it without the extras after "..." so full sql query:

 

SELECT * FROM `stuff` WHERE `daterenewal` > NOW()

 

Conditions is still fulfilled.

 

// Cancel, i forgot to put the date a lesser value (from previous testing i changed it to above NOW())

and it now works.

 

My statement had some ORs after the "..." and i believe order of precedence meant it was like bracketing the first 2 conditions, whilst leaving the rest independent.

 

So,

"WHERE `x` = 'y' AND `x` = 'y' OR `x` = 'y'  ..."

became

"WHERE (`x` = 'y' AND `x` = 'y') OR `x` = 'y'  ..."

[rhodesa]

i had a feeling that was the case...which is why i asked for the full statement. you should get in the habit of always using parenthesis if the statement is going to have more then just a few simple ANDs