Mysql and UNIX billions

crimsontech

I'm not sure if this is a PHP bug or a MySQL bug or what, but here goes:

Script:

$timenow = time();
$red = ($timenow-345600);

$ticket_query = mysql_query("SELECT * FROM opentickets WHERE AskDate < '$red'");

This query returns all tickets where AskDate is earlier than the $red date, PLUS all tickets above 1 billion on the time stamp! Ack! I have resolved this by removing the single quotes from around '$red' in my query, so it reads:

$ticket_query = mysql_query("SELECT * FROM opentickets WHERE AskDate < $red");

Just thought I would share this.

saloon12yrd

Haha yeah I got a similar thing. The reason is obvious though: A timestamp of say 10000435501 is stored as 0010010435
Don't ask me why though.

If you chnge the columntype to INT(10) it works though. I'll use that until I've found the error.

Dominique

saloon12yrd

The manual says it all... mysql converts unix timestamps according to context. if the context is "string" you get YYYYMMDDHHMMSS" if its "numeric" you get the timestamp we all know.

sigh

Dominique

crimsontech

I believe it's because when we had it in single quotes, it was treating it as "text", and of course 1 comes before 9 in alphabetical order. (well, it's not really the alphabet, but you know what I mean)

It was much clearer in the light of day. But I still want to share it for anyone else having the same problem. We found about 8 queries in our site that used this syntax that we have to correct.