Format MySQL Datetime in PHP

Format MySQL Datetime in PHP

If you want to format MySQL's DATETIME type field in PHP, you have several options. So, let's quickly review them.

Option 1. The first option is to use MySQL's built in DATE_FORMAT function. For example, the following SQL command:

SELECT DATE_FORMAT('2009-10-19 22:23:00', '%M %d, %Y');

will return this formatted output: October 19, 2009.

However, just in case you don't want to alter the SQL commands in your scripts, there are a couple of other options as well.

Option 2. This only works in PHP 5 or later.

$myDate = new DateTime($record->date_created);
$myDate = $myDate->format("F j, Y");
echo $myDate;

Give the date_created field contains '2009-10-19 22:23:00', the output will be the same: October 19, 2009.

Option 3. Works with any version of PHP. Here we use PHP's date() function but don't pass the MySQL field directly! Rather, we first convert the value of the date in the field to a timestamp and then use the PHP date() function. Example follows.

$dateTimeStamp = strtotime($record->date_created);
echo date("F j, Y", $dateTimeStamp);

Option 4. This is our last option. The trick is to use MySQL's UNIX_TIMESTAMP function to return a timestamp that we will directly feed into the PHP's date() function. So, this is a sort of combo of Option 1 and Option 3. A quick example is given below.

$recordset = mysql_query("SELECT UNIX_TIMESTAMP('2009-10-19 22:23:00') as date_created");
$record = fetch_as_object($recordset);
echo date("F j, Y", $record->date_created);

Again, you must get October 19, 2009 output.

Finally, here is the URL for MySQL's date and time functions: http://cut.io/wPQ6