Converting MySQL Timestamp to a Unix Timestamp

If you use MySQL and have a field defined as 'timestamp', then you may find problems when trying to format the time when using PHP. This tutorial will instruct you how to convert a MySQL timestamp into a Unix timestamp directly from the query.

For this example we will use a table named "EXAMPLE_TABLE" and a field named "TIME". If you try to query the MySQL database and format the TIME result in PHP, you may find that the date is completely wrong. This is because you must request the TIME field in a Unix Format. Below is an example how to pull the TIME field out of the MySQL database and format it with PHP.

<?
$result=mysql_query("SELECT UNIX_TIMESTAMP(TIME) AS FORMATED_TIME FROM EXAMPLE_TABLE");
$FORMATED_TIME=mysql_result($result,0,"FORMATED_TIME");
 
$date=date("m-d-Y H:i:sa",$FORMATED_TIME);
 
echo "$date";
?>

4-20-2002 6:30:21am