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

Recommended Answers

All 2 Replies

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.

Member Avatar for diafol

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.