table name:student enroll
id name date_of_admission
1 kiran 2008-11-11
how to minus date of admission column in current date in phpmysql?
i want year difference and month difference separately
I do not know whether this is done professionally but I'll give it a try.
// change date of admission to unix timestamp $adm = strtotime($row['date_of_admission']); // get current time in a form of a timestamp $now = strtotime("now"); // get the difference in seconds $diff = $now - $adm; // get years from the difference $years = floor($diff / (60 * 60 * 24 * 365)); // get months from the difference $months = floor(($diff % (60 * 60 * 24 * 365)) / (60 * 60 * 24 * 30) ); // echo and see if it is OK echo "<p>The difference is $years years and $months months.</p>";
Note for improvements: wherever there is a division also check for division by zero. I also calculated months assuming 30 days in a month which is not exactly correct. Check if the dates are in correct format (see http://www.php.net/manual/en/datetime.formats.php). Make sure timezone is correctly set (see http://www.php.net/manual/en/function.date-default-timezone-set.php). Check whether $now is greater than $adm.
Edited by broj1: n/a
You could do it purely in mysql:
SELECT FLOOR(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM mydates))/12) AS years, FLOOR(MOD(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM mydates)),12)) AS months FROM mytable;
Just change the mydates field and mytable to your names.
However, I may be wrong here, but I think mysql calculations are a little slower than php.