Formatting Dates within the MySQL Query

Before displaying dates from a MySQL database, do you change the format? If so, how are you formatting those dates? For me, I typically went straight to PHP for the answer. That is until it was brought to my attention that MySQL has a built in function for formatting dates. Let's talk about the date_format() function.

Formatting Dates with PHP

MySQL typically stores dates as "YYYY-MM-DD". Before displaying those dates online, I usually change them to something like "Jan 24, 2012" with the following PHP code:

<?php
$dateFromMySQL = '2012-01-24';
list($tempYear, $tempMonth, $tempDay) = explode('-', $dateFromMySQL);
$formattedDate = date('M j Y', mktime(0,0,0, $tempMonth, $tempDay,$tempYear));
?>

The date(s) may be formatted as expected, but this can be more easily accomplished within MySQL.

Formatting Dates with MySQL

Instead of preparing the date after the fact, the dates could be formatted using MySQL's date_format() function. The following query also gives us the same format (Jan 24, 2012):

SELECT DATE_FORMAT(updated, '%b %e %Y') AS updated, title FROM news_articles

Be mindful that the date_format() function may affect other portions of the query. If we, for example, are sorting by the column being formatted, it will sort alphabetically by month instead of reverse chronological order.

SELECT DATE_FORMAT(updated, '%b %e %Y') AS updated, title FROM news_articles ORDER BY updated DESC

To prevent the conflict, the formatted date could be assigned to an alias that differs from the original column name.

SELECT DATE_FORMAT(updated, '%b %e %Y') AS updated_formatted, title FROM news_articles ORDER BY updated DESC

Conclusion

As with most things in web development, there are many potential solutions. However, some may not be as obvious since we're used to doing something a certain way. MySQL has many useful ways to get to the solution faster. We just need to keep that in mind as problems arise.