Making Timestamps User-Friendly

When we set our MySQL tables to have a "created" field of the type TIMESTAMP with the CURRENT_TIMESTAMP option selected, this records the exact date and time that each row is created in the table.

The format that MySQL uses to record this data is "YYYY-MM-DD HH:MM:SS". However, this is not usually the format that you want to use to display the data on the web page. It's not so easy to read, and definitely is not how most people think of dates.

In order to convert the MySQL TIMESTAMP to a more user-friendly date format, you want to use PHP's built-in date() function. However, PHP's date() function only allows you to change the format of dates that are in Unix Timestamp, which is significantly different from MySQL's TIMESTAMP format.

So, in order to get PHP's date() function to work with MySQL's TIMESTAMP, you first have to convert MySQL's TIMESTAMP format to Unix Timestamp format. To do that, you use PHP's strtotime() function.

Here's the code that does this, from my blog example:

<p>posted <?= date("F j, Y, g:ia", strtotime($post['created'])) ?></p>

This displays on the page as, for example, "posted November 8, 2008, 12:19pm". You can use the date() function to format the date in a wide variety of other formats.