I have these 2 dates stored in mysql :
2012-10-05
2012-10-10
I got the dates returned using php/mysql now I need to find out how many days separate them. in this example it would be 5days. any suggestion of what would be the best way to do it ?
4 Answers
SELECT DATEDIFF(date1, date2)
FROM yourtable
as per the MySQL docs: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff
datediff() returns the difference of date1 - date2 in days.
4 Comments
cppit
thanks for this approach I didnt know you could do it through sql, only knew about php.
Marc B
Better to do it in the database, rather than having to force mysql to convert its internal date format to a string, then force php to convert that string back to a php-native timestamp. pointless round-tripping should be avoided.
cppit
true. can I select more than those fields ? like SELECT DATEDIFF(date1, date2),users.email,users.id FROM mytable. I will be emailing the ones where date1-date2 is bigger than 5days
Marc B
of course, using a function as a field in a query call doesn't prevet you from selecting any other fields/function calls you need.
$date1 = strtotime("2012-10-05");
$date2 = strtotime("2012-10-10");
$days = floor(abs($date2 - $date1)/ (60*60*24));
printf("%d days",$days);
Comments
$date1 = new DateTime('2012-10-05');
$date2 = new Datetime('2012-10-10');
$interval = $date1->diff($date2);
echo $interval->format('%R%d days'); // +5 days
Comments
Try something like this :
$date1 = "2007-03-24"; $date2 = "2009-06-26";
$diff = abs(strtotime($date2) - strtotime($date1));
$years = floor($diff / (365*60*60*24)); $months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24)); $days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));
printf("%d years, %d months, %d days\n", $years, $months, $days);
strtotime()accepts a string and returns an a unix timestamp. If you didstrtotime()for each and subtracted, you'd have the difference between the two dates in seconds. Divide appropriately and you're on your way.