Jelly and Custard

Dates & MySQL

February 13th, 2006

I recently heard about someone who used to store dates in their DB, retrieve them, and then use mktime(), date() etc to then use that date in a more readable format! So here's a quick look into some of the ways to retrieve dates from MySQL and format them.

Timestamps are your Friend

The UNIX timestamp is a count of the number of seconds since 1st January 1970. The date() function in PHP can format a UNIX timestamp into a large number of user-defineable formats:

You can get MySQL to give you a UNIX timestamp by specifying so in your query:

SELECT id,name,UNIX_TIMESTAMP(date) FROM table

This will give you a timestamp that you can then use with the date() function to format.

Let MySQL Do The Hard Work

I'm a believer that you should let MySQL do the work if possible. You can in fact get MySQL to format the date within your SELECT query, and return the date as you want it:

mysql> SELECT DATE_FORMAT(date, '%W %M %Y') FROM table;
-> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT(date, '%H:%i:%s') FROM table;
-> '22:23:00'

More information on DATE_FORMAT can be found in the manual.

 Add to del.icio.us    Digg this    Technorati

Related Posts:

• Finding the difference between dates
• Getting totals in MySQL
• MySQL Events
• ARCHIVE-ing Data in MySQL
• Retrieve valid entries for a MySQL ENUM/SET column

Entry Filed under: PHP, MySQL