Usually, MySQL and PHP go together like peanut butter and chocolate. As the most popular open source database and the world’s most popular open source server-side scripting language, MySQL are designed to work together in most circumstances. However, when it comes to working with dates and timestamps, there is some risk of data becoming lost in translation. Luckily, there are functions and commands within PHP and MySQL that can help you solve the discrepancies between MySQL date formats and PHP date formats.
MySQL vs. PHP Dates
PHP stores dates and timestamps as a simple integer that equals the amount of seconds that have passed since January 1, 1970. This is known as Unix time. For displaying PHP dates on webpages, you can use the date() function to format it to something readable and meaningful to users. But when the date is stored or referred to, it is handled in the Unix time format.
In MySQL, timestamps are stored in the format YYYY-MM-DD HH:MM:SS. For dates only, the HH:MM:SS portion is omitted. For example, September 26, 1984 would be represented as 1984-09-26. But within PHP, this very same date would be represented as 465004800. As you can imagine, this will create issues when storing and retrieving dates to and from MySQL databases.
Converting PHP Dates to MySQL Dates
Luckily, there is a simple way to convert PHP dates to MySQL dates. In fact, there are two ways. The first way is to pre-process the PHP date using a PHP function. The date() function mentioned above is specifically designed for parsing PHP dates into readable formats. All you have to do is use the date() function to match the MySQL format. So, your code would look like this:
$date_for_mysql = date(‘Y-m-d H:i:s', $date_from_php)
You can then insert $date_for_mysql into your SQL query.
Alternately, you can use an SQL function to process the date. This is a function you’d use directly within your query. The proper function is called FROM_UNIXTIME(). So, using this your query may look a bit like this:
$query = “UPDATE table SET date = FROM_UNIXTIME($date_from_php) WHERE row = column” ;
Remember: You should use one of these functions or the other, not both. Otherwise, you’ll be processing the date twice.
Converting MySQL Dates to PHP
Once you are ready to pull your date out of MySQL and use it in your PHP script, you’ll have to convert it from MySQL’s format back into Unix time. To do this, you can use PHP’s strtotime() function (which stands for “string to time”). It would look like this:
$date_for_php = strtotime($date_from_mysql)
This bit of code will convert the SQL time into Unix time. If you want to display it to the user, you’ll still need to use the date() function to format it.
To convert a MySQL date to PHP using an SQL function, use UNIX_TIMESTAMP(). You would most often use this in conjunction with a SELECT command. For example:
$query = “SELECT UNIX_TIMESTAMP(mysqldatefield) FROM table WHERE condition”;
The value that your query returns would already be in PHP Unix time format, and wouldn’t require the strtotime() function for further processing.