4o3.net

This is something that slipped by me for a long time.

Like many other developers I know, I have written a function that takes a date from mysql (In any of the relevant formats, usually datetime though) and then formats it (Using substr() or strtotime() and date()) with PHP into a nice human readable format for display on the website.

There is a more elegant solution however.

MySQL has a number of functions in place for dealing with dates and times so you can return the nicely formatted date direct from the database!

Most importantly for this example we have DATE_FORMAT.

In your database, you have a table `news_stories` that has a field `date` with a type of ‘date’

SELECT DATE_FORMAT(date, '%M %D, %Y') FROM `news_stories` WHERE 1

This will return a nice easy to read date (Such as 29th June, 2008) in your result set.

Tags: date, date_format, functions, MySQL, time

This entry was posted on Sunday, June 29th, 2008 at 8:49 am and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.