Posted by & filed under Developer Blog.

In MySQL 4.1 and up, the properties of the TIMESTAMP column type change from YYYYMMDDHHMMSS to YYYY-MM-DD HH:MM:SS. If you have built anything in PHP that relies on the old format, it will break if you upgrade from older versions of MySQL (such as this blog!).
If you want the same view of a timestamp field in 4.1.x as it was in in earlier mysql versions, you can add a “+ 0” to the column name.

SELECT ts_col + 0 FROM tbl_name;

That much has at least been documented and can be found after a quick google search. However, getting the earlier format to appear in PHP is undocumented as far as I can tell. You might just guess that you nees to use the exact same sql statement, and you’d be right for the most part:

$result = mysql_query("SELECT ts_col + 0 FROM tbl_name");
$row = mysql_fetch_array($result);

But what took some trial and error to figure out for myself, was that you then have to use the “+ 0” again when you refer to that column as such:

$date = $row['tbl_name + 0'];

I haven’t figured out how to get the date formatted yet when doing a wildcard query like this:

SELECT * + 0 FROM tbl_name;

so if you know how to do that please leave a comment here.