Use PHP MySQL date format correctly

Learn how to handle PHP and MySQL date formatting correctly.

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:

.

11 thoughts on “Use PHP MySQL date format correctly”

  1. Pingback: synapseindia php development-PHP/MySQL Tutorials for Building Web Applications | Synapseindia Php Development
  2. Pingback: 27 PHP/MySQL Tutorials for Building Web Applications « Resource Sharing ::: . . . . .
  3. Pingback: 27 PHP/MySQL Tutorials for Building Web Applications | Jason Chew

  5. hi, i have a concern about querying data by date. i had posted this from other forums yet they aren't giving any assistance. i am wishing that you can help me with this matter. i am a newbie in php and mysql, i had already created database and table. i had already inserted my table1 with variable. I had used the column and row. I successful loaded it into database. I had used timestamp. I wished to query using date, from user input date. but i dont know how. i used this code:

    $result = mysql_query("select * from table1 where date between '2011-10-28' and '2011-10-29'");

    echo "";
    for($i = 0; $i < mysql_num_fields($result); $i++){ echo "".mysql_field_name($result, $i).""; } echo ""; while($row = mysql_fetch_array($result)){ echo ""; for($i = 0; $i < mysql_num_fields($result); $i++){ echo "". $row[$i] .""; } echo ""; } the code is working yet i am thinking of a system in were the user can input their desire date to query for example, 2011-10-28 as starting date, and 2011-10-29 as its ending date. any help would be appreciated. thanks guys. i had also tried to use: SELECT TIMESTAMP(`date_in`) AS date FROM `table` WHERE 1 but all data from my database were being displayed. what i am trying to do in this is to display data on its specific date.

    Reply

  6. Hi,
    Is it better to use DATETIME, or INT(11) and then work with unixtime number in php, since many times mysql server time differs of php server time?
    If DATETIME is better,, in this case what to use, NOW(), or UTC_TIMESTAMP()

    Reply

  7. Hello, Shaikh Mahbubur Rahman:
    There are several PHP an MySQL way you can do that. It's preferable that you handle most of the processing in PHP end and also handle security issues when you are getting values from user input ($_GET, $_POST etc.)

    Any way, if you want to do it purely using MySQL, then here is how to do it:

    SELECT * FROM news Where DATE_FORMAT(`postdate`, '%b-%Y') = 'Aug-2011'

    Again, please remember, this is just an example, handle all the security issues yourself.

    Reply

  8. hi every one i need to how how can i get result from a table using date passing month and year

    $getmonth = $_GET['month'];

    $SQL ="SELECT * FROM news Where MONTH(postdate) = $getmonth";
    $execute = mysql_query($SQL) or die(mysql_error());

    here getmonth is a Aug-2011

    Reply

  10. I've been using strtotime in php which converts timestamp/date from mysql into php compatible object. It's more convenient to me in fact.

    date("M d,Y", strtotime($date_from_mysql));

    This is more widely used (even in this wordpress blog). Would be nice if some sort of benchmark could be shown between these two methods 🙂

    Reply

    1. Actually this is what someone asked in yahoo answers.

      To be honest, I too was used to with strtotime(), until recently, after reading a few articles from MySQL and MySQL always tells people to use MySQL functions whenever possible; But you are right, a benchmark would be better. I'll update the post when I get one or perhaps create one of my own 🙂

      Although, there are various issues with MySQL and PHP time, especially with timezone setting, I guess that's why PHP developers use strtotime() more often. Also if you investigate, then you'll see that both PHP and MySQL has changed their time calculating functions multiple times due to bugs. I had issues with using strtotime() on MySQL (4) TIMESTAMP. MySQL select query returns TIMESTAMP in different format in MySQL 4 and 5. Not to mention, the eminent issue with TIMESTAMP on January 19, 2038.

      However, if your MySQL and PHP timezone is in sync and both are updated to bug-fixed versions, then I'll vote for using UNIX_TIMESTAMP function, especially if you are using TIMESTAMP as data type.

      I'll investigate this issue further and update my post accordingly 🙂

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *