Date and time fields or what we call timestamps stored in our Mysql databases has a format of “YYYY-MM-DD HH:MM:SS” or “2011-04-15 11:30:00″ . It is awkward for the users to to see dates outputted to something in that format, so for user readability and friendly purposes, we must output the date into something more readable and understandable for them. There are 2 ways to accomplish this, formatting the date in our MySQL query before outputting, or grabbing the timestamp result from the query, then formatting it using PHP.

1. Formatting using MySQL’s DATE_FORMAT function

$result = mysql_query("SELECT DATE_FORMAT( date_field , 
             '%W, %M %e, %Y @ %h:%i %p' ) AS output_date FROM table");  
while ( $row = mysql_fetch_array($result) ) {
   
   echo $row['output_date'] . "<br />";
   // outputs something like "Friday, April 15 2011 @ 03:49:42 AM"

}

You can output different kind of date formats, not just the string format stated above. See other specifiers that can be used in the format string for Mysql in this link: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-format

2. Formatting using PHP’s strtotime and date functions

$result = mysql_query("SELECT * FROM table");  
while ( $row = mysql_fetch_array($result) ) {
  
   $output_date = date("l, F j Y @ h:i:s A", strtotime($row["date"]));
   echo $output_date . "<br />";
   // outputs something like "Friday, April 15 2011 @ 03:49:42 AM"

}

You can output different kind of date formats, not just the string format stated above. See other specifiers that can be used in the format string for PHP in this link: http://php.net/manual/en/function.date.php

That’s it! It’s up to you on what kind of method you want to use in formatting your string. However, MySQL’s DATE_FORMAT function is not available in older versions of MySQL.