A common issue most PHP developers run into is what date format should be used between PHP and MySQL. Converting can be a big pain, and their favorite formats could not be any different.

CodeIgniter has a lot of great date functions (aka, “helpers“), but none really fit the bill to make it really simple to convert PHP time (seconds since Unix Epoch) to MySQL’s datetime format, which is really the best way to store dates (so you can still leverage all of MySQL’s great date functionalities). So, I made one to extend CodeIgniter’s date helper…

Add “MY_date_helper.php” to ‘/application/helpers/’ with the following code:

<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');
/**
 * Extends date helper to return current time in MySQL datetime format
 * By Dave Rogers
 * Shinytype.com
 *
 * @access	public
 * @param	string (optional)
 * @return	string
 */
function mysql_datetime($date=null) {
	if(!$date) {
		// use now() instead of time() to adhere to user setting
		$date = now();
		}
	if(is_numeric($date) && strlen($date)==10) {
		return mdate("%Y-%m-%d %H:%i:%s", $date);
		}	else	{
		// try to use now()
		return mdate("%Y-%m-%d %H:%i:%s", now());
		}
	}

/* End of file MY_date_helper.php */
/* Location: ./application/helpers/MY_date_helper.php */

Note that it goes in your application helper stack rather than system helper. This will still extend the default helper, but since it’s in your application folder (which I personally abstract from the system folder, which I rename to it’s corresponding version number, to make upgrading CodeIgniter simple as well downgrading in the case of a bug) it will not get lost or overwritten when you upgrade CodeIgniter. Also note the “MY_” prefix. This is the default custom helper prefix, if you have changed it (“$config['subclass_prefix']” in config.php) then cascade that prefix accordingly.

The functionality of this helper is as simple as it looks: take PHP time and reformat it to MySQL’s datetime format. If no date is passed to the function, it simply uses the current time with CodeIgniter’s “now()” function (also available in the date helper), which is essentially a simple wrapper for PHP’s own “time()” function except it takes into account your master time reference setting (“$config['time_reference']” in config.php) and sets the time in accordance.

How you reference this helper:

$this->load->helper('date');
echo "the current datetime is ".mysql_datetime();

Which displays as:

the current datetime is 2010-06-23 00:12:30

Example with a date passed in:

mysql_datetime('1277277150');

Datetime to PHP’s Time Format

If you want to turn a datetime into PHP’s unix epoch format (the opposite of the above), you can add this function to the same file (before the end of file lines):

// --------------------------------------------------------------------

/**
 * Take a MySQL datetime var and turn it into PHP's Unix Epoch time
 *
 * @access	public
 * @param	string
 * @return	int
 */
function datetime_to_unix($date) {
	if(!$date) {
		return false;
		}	else	{
		return date('U', strtotime($date));
		}
	}

With this function, a parameter is required…

$this->load->helper('date');
echo datetime_to_unix('2010-06-23 00:22:23'); // returns 1277277743

Pretty painless, right?

Full Source

Here’s the full file if you’re just looking for a quick copy and paste…

MY_date_helper.php

<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');
/**
 * Extends date helper to return current time in MySQL datetime format
 * By Dave Rogers
 * Shinytype.com
 *
 * @access	public
 * @param	string (optional)
 * @return	string
 */
function mysql_datetime($date=null) {
	if(!$date) {
		// use now() instead of time() to adhere to user setting
		$date = now();
		}
	if(is_numeric($date) && strlen($date)==10) {
		return mdate("%Y-%m-%d %H:%i:%s", $date);
		}	else	{
		// try to use now()
		return mdate("%Y-%m-%d %H:%i:%s", now());
		}
	}

// --------------------------------------------------------------------

/**
 * Take a MySQL datetime var and turn it into PHP's Unix Epoch time
 *
 * @access	public
 * @param	string
 * @return	int
 */
function datetime_to_unix($date) {
	if(!$date) {
		return false;
		}	else	{
		return date('U', strtotime($date));
		}
	}

/* End of file MY_date_helper.php */
/* Location: ./application/helpers/MY_date_helper.php */