How to convert MySQL timestamp to PHP date type

Let’s say you have the following PHP code that extracts the date from the times table in your MySQL database. The date is of timestamp type which has the following format: ‘YYYY-MM-DD HH:MM:SS’ or ‘2008-10-05 21:34:02.’

  1. $res = mysql_query("SELECT date FROM times;");
  2. while ( $row = mysql_fetch_array($res) ) {
  3.    echo $row['date'] . "<br />";
  4. }

This date format that is in the output is in the timestamp format, 2008-10-05 21:34:02, which is not surprising, but you want something that is more “user-friendly” or “readable” as in “9:34 pm October 5, 2008.”

Let’s go back to the drawing board and try again:

  1. $res = mysql_query("SELECT date FROM times;");
  2. while ( $row = mysql_fetch_array($res) ) {
  3.    echo date("g:i a F j, Y ", strtotime($row["date"])) . "<br />";
  4. }

A sample output from this PHP code would be 9:34 pm October 5, 2008 which is much more user-friendly.

The PHP strtotime function parses the MySQL timestamp into a Unix timestamp which can be utilized for further parsing or formatting in the PHP date function.

Here are some other sample date output formats that may be of practical use:

  1. echo date("F j, Y g:i a", strtotime($row["date"]));                  // October 5, 2008 9:34 pm
  2. echo date("m.d.y", strtotime($row["date"]));                         // 10.05.08
  3. echo date("j, n, Y", strtotime($row["date"]));                       // 5, 10, 2008
  4. echo date("Ymd", strtotime($row["date"]));                           // 20081005
  5. echo date('\i\t \i\s \t\h\e jS \d\a\y.', strtotime($row["date"]));   // It is the 5th day.
  6. echo date("D M j G:i:s T Y", strtotime($row["date"]));               // Sun Oct 5 21:34:02 PST 2008

If you found that my code was helpful in any way, shape, or form and would like to buy me a beer, please use the Donate button below =) Cheers!

Published by

Allen Liu

Building websites is a passion of mine. During the course of my work, I have created some snippets of code that I hope will prove useful to other developers.

22 thoughts on “How to convert MySQL timestamp to PHP date type”

  1. Just wanted to say “thank you” for the quick help with mysql-to-unix timestamp conversion!
    Guys like you make the web … work!

    1. Hi Michael,

      You have posted your CSS code but I will actually need your MySQL code that queries your database.


  2. Well that worked for me. I spent days trying to get my “date” to change from just returning the date in the myqsl table. However my rows are triplicated now!

    1. Hi Michael,

      I’m glad to hear this helped. If you are having any number of rows replicated, it sounds like you are using multiple tables and not joining them correctly. If you give me your MySQL code, I may be able to help out.


  3. thanks I was really spinning around (at something completely basic) and am happy to finally found an answer for this old VB programmer such as myself.

  4. This is a great explanation. I was looking everywhere for an answer to what seemed like such a basic question and after reading this, it is now!! Thanks.

  5. Thanks for the clear and nicely written explanation.
    My next adventure is to try and figure out how to calculate time difference between this result and now.
    I want to display strings like “2 hours ago”, “1 week ago” etc.

    Thanks again.

Leave a Reply

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