Entry

How do I format a MySQL timestamp into a readable date?

May 16th, 2000 10:40
Kay Crowley,

I spent a couple of hours figuring this out even -with- plenty of 
available resources. Maybe this will help someone...

Change MySQL Timestamp (8char) into readable date

First, a UNIX_TIMESTAMP is not the same as a MySQL Timestamp. 
If you are using a MySQL Timestamp, you can use MySQL's DATE_FORMAT() 
Function to properly format it when you query your database.

As an example, DATE_FORMAT will render 'May 16, 2000' From a timestamp 
of '20000516'(and more per your own timestamp specs).

*YOUR QUERY:
 $query = "SELECT Field1,Field2,date_format(Timestamp_Field_Name, '%M %
d, %Y') AS Readable_Date,Field4 from Table_Name"; 
 
 [Note:  
 "date_format(Timestamp_Field_Name, '%M %D, %Y') AS Readable_Date" 
 is the critical piece here. Replace Timestamp_Field_Name with your 
own  timestamp field name.]
 
*YOUR IDENTIFICATION AS VARIABLE 
 [Massage to match your own coding/variables]
 
 $Field2 = mysql_result($result,$counter,"Field2"); 
 $Timestamp_Field_Name = mysql_result($result,$counter,"Readable_Date");
 $Field4 = mysql_result($result,$counter,"Field4"); 
 
*DISPLAY IN HTML PAGE
 PRINT  "$Timestamp_Field_Name";

For more information, refer to the MYSQL Docs:
http://web.mysql.com/Manual_chapter/manual_Reference.html
Do a find for DATE_FORMAT