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

Store everything in the database in a datetime field in UTC. For PHP manipulation, use the PEAR Date library. I'm not a big PEAR user, but this library is fantastic and will handle all of annoying date conversion issues that you should not be spending your time worrying about.

Answer 2

I would recommend you keep everything in mysql format until you need to display it to the user, then use strtotime() to get a unix timestamp and date() to format it.

If you throw in some Hungarian Notation it's even harder to go wrong:

$ymdDateAdded = date('Y-m-d');
$timeDateAdded = strtotime($ymdDateAdded);
$userDateadded = date('j F Y', $timeDateAdded);

Answer 3

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.

Answer 4

You could make a small date object which simply converts the date as you need it

$Date_p = new MagicDate($php_date);
$Date_m = new MagicDate($mysql_date);

The $Date_p and $Date_m are just showing that you can seed the object anyway you need to. When you want a mysql date you would have code like. Realistically it would be something pretty generic like $Date.

$query = "UPDATE SET Date='".$Date_p->toMysql()."' "...

and vice versa when you need the opposite. You can use the functions you've already created. Just add a "sniffer" in the construct method like:

public function __construct($date)
{
    $phpdate = strtotime($date);
    if($phpdate) 
    {
        $this->phpdate = $phpdate;
        $this->mysqldate = $this->mysql_date($phpdate);
    }
    else
    {
        $this->phpdate = $this->php_date($phpdate);
        $this->mysqldate = $phpdate;
    }
}

Throw some error handling in to catch the things that go bad. Add the getter for the two dates. And it's a set it and forget it situation. Just pull the right date out when you need it.

There could be some optimizations, but this is to just show you how it could work.

Answer 5

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 6

I think it would be a better ideea to store unix timestamps in the DB field. When you need to display dates to human language, you can always use php's date() function. For everything else, just use the numeric timestamp.

Answer 7

You can just format the date in MySQL when doing the query. This site will help you format the date http://www.mysqlformatdate.com