Unix timestamp not playing well with others

delayedinsanity · 04-22-2008, 07:44 PM

If I hadn't found this place, I don't know, but I'd probably have the manuals printed out three or four times, with sticky notes everywhere and papers piled on all my animals.

Now, that said, what the hell is going on here?

PHP Code:


			
public function addNewUser ($szUsername, $szPassword, $szEmail) {



    $intTimestamp = time();

    $szVerify = substr(md5(rand(1000, 9999) . $intTimestamp), 0, 24); // verification string

 

    $intUserlevel = USER_LEVEL; // set default user level



    $q =  "INSERT INTO " . TBL_USERS . " (username, password, email, userlevel, registered_on, user_last_active, verify) ";

    $q .= "VALUES ('$szUsername', '$szPassword', '$szEmail', '$intUserlevel', now(), $intTimestamp, '$szVerify')";



    if (mysql_query($q, $this->connection)) {

        return $szVerify;

    } else {

        return 0;

    }



}

Looks fine to me, what about you? It works. The problem is it keeps inserting 255 as the timestamp. I even tried to substitute php's time() function with mysql's own native unix_timestamp(), and it still inserted 255.

I can't find any sites that document this happening, so no luck in fixing it so far.

The table column it's going into is an unsigned tinyint(11) which is the same as I've always used for my timestamps.

What the bleep?

-m

Salathe · 04-22-2008, 08:35 PM

An UNSIGNED TINYINT column can contain a maximum value of 255. The number in parentheses is only for the display width of the column and has no effect on the range of values that can be stored in it. Since timestamps are integers like 1208895830 they are way, way out of the accepted range of values (-128–127 signed; 0–255 unsigned) and therefore they get truncated to 255.

You should use an appropriate column type (either DATETIME or TIMESTAMP) to store the timestamps and use the available MySQL functions (FROM_UNIXTIME, UNIX_TIMESTAMP) to convert between the integer Unix Timestamp and other representations.

delayedinsanity · 04-22-2008, 09:01 PM

I feel... like an idiot.

I glossed over that when I changed the table structure, I had it right the first time, and I'm not sure why I changed it to a tinyint.

Oy.