Managing date formats differences between PHP and MySQL

Question

I'm writing my first PHP app that has to directly deal with dates, and thus to directly deal with the fact that PHP and MySQL have different date formats.

My question is: what's the most elegant way to manage this difference?

I have the following two functions to manage the difference using php:

function mysql_date($php_date)  {
    return date( 'Y-m-d H:i:s', $php_date );
}

function php_date($mysql_date) {
    $val = explode(" ",$mysql_date);
    $date = explode("-",$val[0]);
    $time = explode(":",$val[1]);
    return mktime($time[0],$time[1],$time[2],$date[1],$date[2],$date[0]);
}

is there a simpler way to manage this directly within my SQL queries?

Or could you suggest any other more elegant way to manage this?

Answer 1

Since (around) PHP 5.2, PHP has had a built in class/object for dealing with Dates and Times, called DateTime. In a void, it's always better to use a built-in than to wrangle with the messy details yourself.

The DateTime constructor (or the date_create function) accepts a date in any format understood by strToTime. All you need to know about strToTime is it's magic voodoo that will correctly recognize a date in almost any string format. When I first encountered strToTime I had the same internal reaction you're having now ("that's bullshit/seems unreliable"). It's not. It Just Works in a way that your own fragile understanding of dates never will (and if you think you understand dates, you don't. Trust Me.)

So, pull the information from MySQL as a Date/Time string, and immediately create a PHP date Object. Use the date_format method (with some handy constants) when/if you need the date again as a string.

Answer 2

You can replace php_date with strtotime.

$php = strtotime($mysql);

The MySQL equivalent would be UNIX_TIMESTAMP.

Though, if you want to handle formatting in SQL, try MySQL's DATE_FORMAT.