CodeIgniter Forums

Full Version: [solved] MySQL date manipulation
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

Quick question.

If I have object properties which are dates in MySQL format - =>string(10) "2007-10-01"

What is the fastest way to add 1 to the date and then split it into year month day.

Thanks in advance.


El Forum

OK, This is what I did, I hope it helps someone else

In my SELECT statement

select DATE_ADD(EndDate, INTERVAL 1 DAY) as NewDate

where EndDate is a field in the table that gets selected. If you do this you can't select * from the table, you have to specify all of the fields you want.

This will return the date in EndDate plus 1 day.

drop this in a helper file

function makeLongDateFromMySQLDate($indate)
  // $indate should be in the format 'YYYY-MM-DD' as per the Date format in MySQL
  $x = explode('-',$indate);
  $timestamp = mktime(0,0,0,$x[1],$x[2],$x[0]);
  $outdate=date('l jS \of F Y',$timestamp);
  return $outdate;

I am sure someone could make it more flexible if they need to.

This returns the date in a format

"Monday 1st of October 2007"

El Forum

I use TIMESTAMP, for "short" dates (only works 1970 forward)

TIMESTAMP gives me a integer, so i can work easier with it. (add a day, month, hour(s))

and doppo, i still use the date() php function!

$sql = "SELECT UNIX_TIMESTAMP(datefield) as datefield, * FROM table";

echo date('d/m/Y', $row_returned->datefield);

Of course, birthday date will not work!