Converting Between PHP and MySQL Date Formats

Dates and times in MySQL are retrieved in a slightly different way than you might expect. Times work relatively normally, but MySQL expects dates to be entered year first. For example, the 29^th of August 2000 could be entered as either 2000-08-29 or as 00-08-29. Dates retrieved from MySQL will also be in this order by default.

To communicate between PHP and MySQL then, we usually need to perform some date conversion. This can be done at either end.

When putting dates into MySQL from PHP, you can easily put them into the correct format using the date() function as shown previously. One minor caution is that you should use the versions of the day and month with leading zeroes to avoid confusing MySQL.

If you choose to do the conversion in MySQL, two useful functions are DATE_FORMAT() and UNIX_TIMESTAMP().

The DATE_FORMAT() function works similarly to the PHP one but uses different format codes. The most common thing we want to do is format a date in MM-DD-YYYY format rather than in the YYYY-MM-DD format native to MySQL. You can do this by writing your query as follows:

					
SELECT DATE_FORMAT(date_column, '%m %d %Y')
FROM tablename;

				

The format code %m represents the month as a 2-digit number; %d, the day as a 2-digit number; and %Y, the year as a 4-digit number. A summary of the more useful MySQL format codes for this purpose is shown in Table 18.3.

Table 18.3. Format Codes for MySQL's DATE_FORMAT() Function

Code	Description
%M	Month, full text
%W	Weekday name, full text
%D	Day of month, numeric, with text suffix (for example, 1st)
%Y	Year, numeric, 4-digits
%y	Year, numeric, 2-digits
%a	Weekday name, 3-characters
%d	Day of month, numeric, leading zeroes
%e	Day of month, numeric, no leading zeroes
%m	Month, numeric, leading zeroes
%c	Month, numeric, no leading zeroes
%b	Month, text, 3-characters
%j	Day of year, numeric
%H	Hour, 24-hour clock, leading zeroes
%k	Hour, 24-hour clock, no leading zeroes
%h or %I	Hour, 12-hour clock, leading zeroes
%l	Hour, 12-hour clock, no leading zeroes
%i	Minutes, numeric, leading zeroes
%r	Time, 12-hour (hh:mm:ss [AM|PM])
%T	Time, 24-hour (hh:mm:ss)
%S or %s	Seconds, numeric, leading zeroes
%p	AM or PM
%w	Day of the week, numeric, from 0 (Sunday) to 6 (Saturday)

The UNIX_TIMESTAMP function works similarly, but converts a column into a UNIX time stamp. For example,

					
SELECT UNIX_TIMESTAMP(date_column)
FROM tablename;

				

will return the date formatted as a UNIX time stamp. You can then do as you will with it in PHP.

As a rule of thumb, use a UNIX timestamp for date calculations and the standard date format when you are just storing or showing dates. It is simpler to do date calculations and comparisons with the UNIX timestamp.