0

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

3
Contributors
2
Replies
3
Views
5 Years
Discussion Span
Last Post by diafol
0

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

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.