Dates in PHP and MySQL: Converting and Creating the MySQL datetime value

Working in PHP and MySQL is easy and you’re likely to be doing things with the datetime type a lot! Dates can be a little of a hoary issue, particularly the timezone aspect, but they are pretty straightforward.

Here’s a quick crib sheet I use with devs new to PHP and MySQL

1. I want to create a datetime string for use in a MySQL statement
// get the current date time and convert to a mysql datetime format
// the php time returns the current time in Unix epoch timestamp format
//i.e. the number of seconds since January 1 1970 00:00:00 UTC

$mysql_date_time = date("Y-m-d H:i:s", time() );

2. I want to convert a MySQL datetime row field so I can use in PHP

// take an mysql datetime and convert to the epoch based integer

$epoch_date_time = strtotime($mysql_date_time);

// now you can do what you like with it, convert it back to a user friendly date
// or use the int to do < > comparisons
// you should/could arguably do that in SQL

3. I want to convert the MySQL datetime to something user friendly for display

// convert epoch time to user friendly display
// see this page for more info
// this produces something like Friday 31st August 2012 09:46:02 AM

$friendly_date_time = date('l jS \of F Y h:i:s A', $epoch_date_time);