Converting Dates from MySQL to PHP

"Object-relational impedance mismatch" is a term that was invented in software development to describe the extra conversion work you have to go through when going between two different data formats. Most often, it relates to the fact that relational database systems store data differently than languages store data in objects and arrays. Of course, I'm thinking of MySQL and PHP.

There are a bunch of functions and classes written into PHP to help with these mismatches. I'm thinking of PDO, PHP Data Objects, for help with conversion from one database to another, and ORM, Object Relational Mapper, for taking database data and making them into PHP objects.

One specific area that always seems to have object-relational impedance mismatch is dates. Specifically, how to convert MySQL dates to PHP dates.

MySQL dates can be stored as either a string, or an integer. The available date formats in MySQL are: DATE, TIME, DATETIME, TIMESTAMP, and YEAR. The possible range of dates and time for each format are:

DATE
yyyy-mm-dd
1000-01-01 to 9999-12-31

DATETIME
yyyy-mm-dd hh:mm:ss
1000-01-01 00:00:00 to 9999-12-31 00:00:00

TIMESTAMP
yyyy-mm-dd hh:mm:ss
1970-01-01 00:00:00 to 2037-12-31 23:59:59

TIME
hh:mm:ss
-838:59:59 to 838:59:59

YEAR (2 or 4)
yy or yyyy
1970 to 2069 or 1901 to 2155

If we want to convert these dates and times to PHP to do things like date calculations, you begin to see some of the problems.

TIME and TIMESTAMP base their formats on the Unix Epoch date, 1970-01-01, as does PHP. However, TIMESTAMP resets its value every time you update a row, because of this, you most likely should capture date and time information in MySQL with the DATETIME format. DATETIME is formatted as a string in MySQL.

There are many advantages to storing dates in MySQL. There are a multitude of date functions within MySQL, and you can output the date, in any format, to the front of the page. What else do you need, MySQL takes care of everything.

Unfortunately, there are times when you want to do some date calculation in your PHP code, and then use the result of the calculation in a conditional statement based on a date. If you do, you need to do a MySQL to PHP date conversion. How do you do it?

PHP dates are stored as a 64-bit number that range from 292 Billion years in the past to the 292 Billion years in the future, and dates are different depending in what time zone you set in your php.ini file. Like in MySQL, there are multitude of functions and classes you can use to handle PHP dates within PHP.

What are our options for handling this Object-relational impedance mismatch with dates?

First, we could do everything in MySQL, store the DATETIME, do all the calculations, and formatting. This works a lot of the time, except of course, when you need to do some special date calculation in PHP.

We could use MySQL's help with the conversion with two MySQL commands: "UNIX_TIMESTAMP" and "FROM_UNIXTIME".

Use UNIX_TIMESTAMP to convert MySQL dates to PHP dates, like so:

    $SQLquery = "SELECT UNIX_TIMESTAMP(MySQL datetime)...";

FROM_UNIXTIME would be used in the opposite direction for database UPDATES.

    $SQLreturnQuery = UPDATE xtable SET
    datecol = FROM_UNIXTIME($PhpDate) WHERE...    

Second, we could store the date in the database as a PHP integer, and output the date directly from the database to a PHP variable or array. Unfortunately, you give up all the MySQL date calculations and formatting. You also have problems if your crossing time zones. This is not a good idea.

The last option is to do the conversions when you need to, and this is probably the way most folks do it. Let's walk through it.

We'll use two PHP functions to get the correct date.

If we get a DATETIME formatted date from the database, and assign it to a variable, say $SqlDate, when we echo it out, we'll get a string that looks like this: "2011-05-23 09:29:20," which is how it is stored in the database.

We need to convert this string value to a time integer. PHP can do this with a "strtotime" function, that's used like this:

	$PhpDate = strtotime($Sql_Date);

Strtotime is a PHP function that expects a string as a parameter with an English date format. It will try to parse this string into a Unix timestamp, taking into account the current timezone.

If we echo out $PhpDate, we get an integer like this: "1306157360"

We now need to format this integer into a date we can understand. We'll use the PHP date function.

	$FormattedPhpDate = date('M d, Y', $PhpDate );

The purpose of the date function is to format the output of a date integer into our desired date format. If we echo this out we get: "May 23, 2011"

In summary, once we get the date out of the database, we're talking about two lines of code.

	$PhpDate = strtotime($Sql_Date);
	$FormattedPhpDate = date('M d, Y', $PhpDate );

Not that difficult, after all.

Comments

Converting Dates from MySQL to PHP — 4 Comments

  1. Excellent! Thanks very much. The “strtotime” function was what I was missing. This is much appreciated.

  2. Thank you very much!!! That is exactly what i was looking for! I have been looking for the solution 2 days!!

  3. Omg thank you so much, i have been looking for a solution to this for almost a week everything i read is very vague you have explained this in detail that i have not seen on the web. Your code worked great and its perfect Much Love ~Many Blessings~