[solved] MySQL date manipulation

[solved] MySQL date manipulation - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: [solved] MySQL date manipulation (/showthread.php?tid=3716)

[solved] MySQL date manipulation - El Forum - 10-18-2007

[eluser]obiron2[/eluser]
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.

Obiron

[solved] MySQL date manipulation - El Forum - 10-18-2007

[eluser]obiron2[/eluser]
OK, This is what I did, I hope it helps someone else

In my SELECT statement

Code:
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

Code:
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"

[solved] MySQL date manipulation - El Forum - 10-18-2007

[eluser]ELRafael[/eluser]
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!

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

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

Of course, birthday date will not work!