retrieving date/time from database

[HDFilmMaker2112]

I have my database set to insert the current time stamp when an entry is made into the table, I am then trying to retrieve via the following code:

 

$select_view_idea="SELECT * FROM $tbl_name5 WHERE message_number='$message_number'";
$result_view_idea=mysql_query($select_view_idea);
while($row_view_idea=mysql_fetch_assoc($result_view_idea)){
extract($row_view_idea);
}
date_default_timezone_set('US/Eastern');
$date=date('l, F jS Y h:i:s A T', $date);
echo $date;

 

The above is outputting:

Wednesday, December 31st 1969 07:33:31 PM EST

 

the database contains:

2011-11-18 00:47:56

[xyph]

The reason this is happening is because PHP uses Unix timestamps while MySQL uses MySQL timestamps

 

Your best solution is to use MySQL's date formatting functions

 

SELECT date, DATE_FORMAT(date, '%W %M %D, %Y') as date_formatted FROM table

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format