Timestamp help

[Anon]

well i am using MySQL with timestamp so I know when people submitted a tutorial (or when i posted it). MySQL does timestamp all fine and stuff (in mysql itself) and I am using the code print"date("m/d/y", $a_row[date]"; in a while loop where date would be the date row (obviously). For some reason it keeps messing up the numbers. I tried it about 15 mins ago and it said it was Jan/14/1969 and I know thats not right and I know the server time is right. Anyways, the timestamp itself is 0112280000.

[mithril]

Are you using a TIMESTAMP datatype? If so, that's your problem. MySQL's TIMESTAMP is not a Unix timestamp, rather it's the following date/time representation: YYMMDDHHMMSS. That is for the TIMESTAMP(12) type BTW, the TIMESTAMP(14) gives the year in 4 digits.

Since the date() function expects a Unix timestamp, the date gets loused up because it's interpreting the above representation as a Unix timestamp (which it's not). The solution is to store Unix timestamps in an INT field. You'd use either PHP's mktime() function in the SQL statement or one of the MySQL time functions, like UNIX_TIMESTAMP() to insert the time as tutorials are added. Once stored, these timestamps can be used successfully with the date() function. HTH.

-geoff