The AL2 Blog

It’s often annoying to some PHP developers that the format of a DATE field in MySQL does not really correspond to anything immediately useful in PHP. Firstly it’s backwards, or at least different to a ‘typical’ date format of ‘DD/MM/YY’ or ‘MM/DD/YY’ (depending on which side of the Atlantic you are on!).

Not only that, but it’s often returned as a string, and has those dashes (‘-’) in . This is not really useful.

I remember spending a lot of time as a student PHP developer trying to work out how to convert from one to the other.

So here’s how you do it…

You have a string with dashes, and the date format is YYYY-MM-DD. All you need to do is use the in-built PHP split function to create an array with the fields we will need.

Let’s assume we are working with the date of September 13th, 2010. MySQL’s DATE datatype will store this as ’2010-09-13′. Let’s get PHP to handle this and make it useable.

    $date = '2010-09-13';
    $dates = split('-', $date);

So now we have an array ($dates) which contains the individual date fields as array items. The split function splits the string using the first parameter as a separator and the second parameter as the string to split. So this divides the $date string into 3 array items – the year, month and day values. For ease of use we can assign them to more verbose variable names.

  $year = $dates[0];
  $month = $dates[1];
  $day = $dates[2];

Now we are getting somewhere, and have the values that we need. Here, you can transform this into a Unix timestamp (which is what I use for most of my date and time handling. I’d recommend steering clear of DATE, DATETIME and TIMESTAMP datatypes unless you have a specific reason to use them – and that doesn’t happen very often). You can do with it what you will, but for the purpose of this demo will will make a Unix timestamp.

    $timeStamp = mktime(0,0,0, $month, $day, $year);

PHP’s mktime function creates the Unix timestamp on the parameters you give it. The first three parameters are for hour, minute, and seconds. So this creates a timestamp for midnight (the beginning of the day) on 13th September 2010. You can change these parameters statically or dynamically to suit your needs.

This will allow you much more freedom in performing date calculations in PHP now that you have converted it from the MySQL format.

It would also be recommended to wrap all of this up into a function. Here is the full code below in a function, taking the MySQL date as a single parameter and returning a Unix timestamp.

function mysqlDateConvert($mysqlDate){
  //Split the incoming date
  $dates = split('-', $mysqlDate);

  //Assign to variables from array
  $year = $dates[0];
  $month = $dates[1];
  $day = $dates[2];

  //Create the timestamp
  $timeStamp = mktime(0,0,0, $month, $day, $year);

  //Return the timestamp
  return $timeStamp;
}

You can now use this function to easily convert your MySQL dates into an easily useable PHP format!