If you ever try to format date in PHP and MySQL, using date (datetime or timestamp) value directly from MySQL to PHP date() function, then you’ll get a Notice level error, something like this:
Notice: A non well formed numeric value encountered in … .. .
Additionally, PHP date() will always return January, 01 1970 if MySQL date is directly used. It means, there is a difference between date in PHP and date in MySQL (i.e. they are not compatible). PHP date function accepts Unix Timestamp, which is different from MySQL date types (DATE, TIME, DATETIME, TIMESTAMP etc.)
So if you want to change your default MySQL date format (the way it looks) to a more suitable formatting using PHP date() function, then you’ll have to convert MySQL date to Unix Timestamp.
In this PHP MySQL tutorial, I’ll show you how to use MySQL DATETIME or TIMESTAMP data in php date() function correctly to achieve different types of formatting.
The first task is to choose MySQL date type correctly, so that it can be used in PHP date() function without error.
As I already mentioned, MySQL DATETIME or TIMESTAMP data is not saved as UNIX Timestamp, which is required for PHP date function. So, first of all, in your SQL query, you’ll have to select MySQL date properly. There is a MySQL function called UNIX_TIMESTAMP(), use that function in you MySQL query to make PHP-MySQL date compatible. For example:
SELECT UNIX_TIMESTAMP(`Your_Date_Column_Name`) AS date FROM `Your_Table_Name` WHERE 1
// replace Your_Date_Column_Name and Your_Table_Name with appropriate table name and column name.
Your MySQL date is now in correct shape to be used in PHP date() function (in the second parameter).
The second task is to use the formatting string correctly. PHP date() function takes a formatting string as the first parameter, which is a combination of several predefined key characters and other redundant characters (not predefined) of our need. For example, to achieve a specific date format, say, Month, date year Hours:min:sec, use the following PHP code:
// the variable $date_from_mysql should have the date value from MySQL which is shown above
$date = date(‘F, d Y H:i:s’, $date_from_mysql);
// this returns something like: January, 03 2011 03:45:17
In the above CODE, the following characters are predefined key characters: F|d|Y|H|i|s, on the other hand space, comma and colon characters are for our desired formatting only. In the above example, F returns the full month name (January), d returns two digit date (03), Y returns four digit year (2011) and so on. Combining with comma, colon and space the complete date becomes January, 03 2011 03:45:17. Meaning of all the key characters are listed in PHP date() function manual.
Let’s use another example. Say, we want to achieve the following date formatting: Hours:mins AM/PM, Day (Month Date, Year)
$date = date(‘h:i A, l (F d, Y)’, $date_from_mysql);
This will return a date string like this: 11:53 AM, Wednesday (January 12, 2011)
Or, say, we want to represent a date like this, 12th January, 2011
$date = date(‘jS F, Y’, $date_from_mysql);
Off course, if you don’t provide the second parameter in PHP date() function, then it will show the current date-time
$date = date(‘M jS, Y g:i A [e]‘);// e stands for timezone.
// this will return something like: Jan 18th, 2011 2:32 PM [UTC]
Addition Resources for further reading:
- MySQL Date-Time data type manual: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html
- PHP date() function manual: http://php.net/manual/en/function.date.php