Calculating date differences
You have two dates (either in your code or in your database columns). How do you calculate the difference between them — in years, months, weeks etc?
This is one of the most common questions on FAQ sites, and the logic in general is quite simple.
You have two dates (either in your code or in your database columns). How do you calculate the difference between them — in years,
months, weeks etc?
This is one of the most common questions on FAQ sites, and the logic in general is quite simple. Most programming languages give you the ability to get "epoch times" (number of seconds/millioseconds from Jan 1, 1970) from a date. Once you have these for both the dates, you get the difference in number of seconds. Then you can simply divide this number by the number of seconds in each day (60×60×24) to get the number of days between the two dates, and so on.
Please note however that as a rule of thumb, if you intend to perform these calculations on values from database columns, you should consider calculating within the database itself as that may be the fastest and most efficient way of doing it. Below are some examples (or pointers) for popular databases and PHP. The PHP logic should be applicable to many programming languages unless they already provide handy in-built functions.
MYSQL:
o Take a look at the following functions:
o PERIOD_DIFF
o DATE_ADD
o DATE_SUB
o From:
http://www.mysql.com/doc/en/Date_and_time_functions.html
POSTGRESQL:
o No functions provided, you can use + and — operators.
o More detailed info about this –
http://techdocs.postgresql.org/techdocs/faqdatesintervals.php
SQL SERVER:
o Code sample:
ldDate1=CTOD(12/01/2002)
ldDate2=CTOD(02/15/2003) ? (YEAR(ldDate2)+MONTH(ldDate2)/;
12-YEAR(ldDate1)-MONTH(ldDate1)/12)*12
ORACLE:
o An interesting article on INTERVALs: http://snipurl.com/171l
o A readymade procedure: http://snipurl.com/171m
MS EXCEL:
o http://support.microsoft.com/?kbid=214134
MSACCESS:
o DATEDIFF
PHP:
<?
$dateDiff = mktime(12,0,0,04,20,2003) — mktime(11,0,0,04,20,2003);
echo 'Difference in seconds: ' . $dateDiff . '<br />';
echo '<br />Years Difference = '. floor($dateDiff/365/60/60/24);
echo '<br />Months Difference = '. floor($dateDiff/60/60/24/7/4);
echo '<br />Weeks Difference = '. floor($dateDiff/60/60/24/7);
echo '<br />Days Difference = '. floor($dateDiff/60/60/24);
echo '<br />Hours Difference = '. floor($dateDiff/60/60);
echo '<br />Minutes Difference = '. floor($dateDiff/60);
?>
NOTES
1. The two dates are inside the mktime functions in the first line. Just
for your reference, format of mktime function is as follows:
mktime(HOURS, MINUTES, SECONDS, MONTH, DAY, YEAR)
2. Months difference may not be totally accurate as my code above
approximates 4 weeks in a month (think of February etc). However, it
should suffice for all practical purposes, try it.
3. For all the differences, if you want precise numbers instead of
rounded integers, please remove the "floor()" functions on the
calculations. In general though, rounded numbers are what you are
perhaps looking for.
RECENT COMMENTS