How to convert MySQL timestamp to PHP date type
October 5th, 2008 | Published in mysql, php | 5 Comments
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 2 3 4 | $res = mysql_query("SELECT date FROM times;"); while ( $row = mysql_fetch_array($res) ) { echo $row['date'] . "<br />"; } |
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 2 3 4 | $res = mysql_query("SELECT date FROM times;"); while ( $row = mysql_fetch_array($res) ) { echo date("g:i a F j, Y ", strtotime($row["date"])) . "<br />"; } |
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 2 3 4 5 6 | echo date("F j, Y g:i a", strtotime($row["date"])); // October 5, 2008 9:34 pm echo date("m.d.y", strtotime($row["date"])); // 10.05.08 echo date("j, n, Y", strtotime($row["date"])); // 5, 10, 2008 echo date("Ymd", strtotime($row["date"])); // 20081005 echo date('\i\t \i\s \t\h\e jS \d\a\y.', strtotime($row["date"])); // It is the 5th day. echo date("D M j G:i:s T Y", strtotime($row["date"])); // Sun Oct 5 21:34:02 PST 2008 |
Customize message
![[del.icio.us]](http://images.del.icio.us/static/img/delicious.small.gif){kind=small}
November 6th, 2008 at 1:14 pm (#)
Great, exactly what i was looking for!
Saved me a lot of time!
Thanks
[Reply]
May 23rd, 2009 at 7:22 am (#)
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.
[Reply]
August 23rd, 2009 at 12:25 pm (#)
These are excellent snippets.
Thank you very much for taking the time to post them!
[Reply]
September 28th, 2009 at 8:15 am (#)
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.
[Reply]
January 11th, 2010 at 11:56 pm (#)
Perfect, exactly what I needed to do! The strtotime function is fantastic! Great post!
[Reply]