Calculating date differences

You have two dates (either in your code or in your data­base columns). How do you cal­cu­late the dif­fer­ence between them — in years, months, weeks etc?
This is one of the most com­mon ques­tions on FAQ sites, and the logic in gen­eral is quite simple.

You have two dates (either in your code or in your data­base columns). How do you cal­cu­late the dif­fer­ence between them — in years,
months, weeks etc?

This is one of the most com­mon ques­tions on FAQ sites, and the logic in gen­eral is quite sim­ple. Most pro­gram­ming lan­guages give you the abil­ity to get "epoch times" (num­ber of seconds/millioseconds from Jan 1, 1970) from a date. Once you have these for both the dates, you get the dif­fer­ence in num­ber of sec­onds. Then you can sim­ply divide this num­ber by the num­ber of sec­onds in each day (60×60×24) to get the num­ber of days between the two dates, and so on.

Please note how­ever that as a rule of thumb, if you intend to per­form these cal­cu­la­tions on val­ues from data­base columns, you should con­sider cal­cu­lat­ing within the data­base itself as that may be the fastest and most effi­cient way of doing it. Below are some exam­ples (or point­ers) for pop­u­lar data­bases and PHP. The PHP logic should be applic­a­ble to many pro­gram­ming lan­guages unless they already pro­vide handy in-built functions.


MYSQL:


    o Take a look at the fol­low­ing func­tions:

    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 func­tions pro­vided, you can use + and — oper­a­tors.

    o More detailed info about this –

    http://techdocs.postgresql.org/techdocs/faqdatesintervals.php

SQL SERVER:

    o Code sam­ple:

    ldDate1=CTOD(12/01/2002)

    ldDate2=CTOD(02/15/2003) ? (YEAR(ldDate2)+MONTH(ldDate2)/;

    12-YEAR(ldDate1)-MONTH(ldDate1)/12)*12

ORACLE:

    o An inter­est­ing arti­cle on INTER­VALs: http://snipurl.com/171l

    o A ready­made pro­ce­dure: http://snipurl.com/171m

MS EXCEL:

    o http://support.microsoft.com/?kbid=214134


MSACCESS:

    o DATEDIFF

PHP:

<?

$date­D­iff = mktime(12,0,0,04,20,2003) — mktime(11,0,0,04,20,2003);

echo 'Dif­fer­ence in sec­onds: ' . $date­D­iff . '<br />';

echo '<br />Years Dif­fer­ence   = '. floor($dateDiff/365/60/60/24);
echo
'<br />Months Dif­fer­ence = '. floor($dateDiff/60/60/24/7/4);
echo
'<br />Weeks Dif­fer­ence   = '. floor($dateDiff/60/60/24/7);

echo '<br />Days Dif­fer­ence    = '. floor($dateDiff/60/60/24);

echo '<br />Hours Dif­fer­ence   = '. floor($dateDiff/60/60);

echo '<br />Minutes Dif­fer­ence = '. floor($dateDiff/60);

?>

NOTES

1. The two dates are inside the mktime func­tions in the first line. Just

for your ref­er­ence, for­mat of mktime func­tion is as follows:

    mktime(HOURS, MINUTES, SECONDS, MONTH, DAY, YEAR)

2. Months dif­fer­ence may not be totally accu­rate as my code above

approx­i­mates 4 weeks in a month (think of Feb­ru­ary etc). How­ever, it

should suf­fice for all prac­ti­cal pur­poses, try it.

3. For all the dif­fer­ences, if you want pre­cise num­bers instead of

rounded inte­gers, please remove the "floor()" func­tions on the

cal­cu­la­tions. In gen­eral though, rounded num­bers are what you are

per­haps look­ing for.

  • mandy

    thank you for the code. and the redesign!

  • Ron

    if you want i can share an ora­cle stored pro­ce­dure with you which does this extensively.

  • / snip­tools

    Ron, thanks. Please feel free to post the code or a URL here.

  • joseph

    can i have the the func­tion for get­ing the cur­rent
    date in ASP.

  • / snip­tools

    Joseph, the "date" com­mand should do the job for you? For instance,

    <%= Date() %>

    HTH

  • Dedy

    fiu­uhhh…
    great formula.

    i really need it…

    Thanks.…

  • jagadees

    I Want to check the Date Func­tion in Between the Two date function(i mean given date is already exist or not),and same as to check Feb­ru­ary month not exceed the date is 28.and also check the leap year or not.

  • mas­tero

    jagadess which lan­guage you using?

  • Jonas

    Thanks for the tuto­r­ial. Con­cise and to the point. I like that.

  • Ganesh

    Hello,
    I have one prob­lem with DATE dif­f­ence logic. i am using fron­tend ASP back­end SQL Server.
    I retrive date field in ASP Ex:06/14/2004 13:15:00 and another one date field Ex: 05/14/2004.
    I used this logic but error displayed.

    DateDiff("d",date1,date2)

    how to get two date diffence(no. of days) using ASP.

    Thanks in Advance!

    Regards,
    Ganesh. M

  • Sam

    hi just want to know if has any­body have a code snip­pet where you get dis­play a days and hours remain­ing from from date­d­iff fuc­tion in sql because i can only get either the day or hour but i dont know how to dis­play both at same time for exam­ple Days Remain­ing 2 days 8 hours if any­body has any answer it will really aprecciated.Thank you

  • Angelus

    Hello! I just want to thank all of you for the codes it helps me very much.…

  • matthew

    Hi there im using php to try and cal­cu­late whether or not a cer­tain date obtained by a posted value falls between 2 dates that are stored in a mysql data­base. Any help or advice regard­ing this would be much appre­ci­ated
    Best Regards
    Mat

  • / snip­tools

    Hi Matthew, you could do that in two ways:

    1. Using PHP: Con­vert the three dates into a sim­i­lar time vari­able ("str­to­time" func­tion for instance) and then do sim­ple math.

    2. Using MySQL: Com­pare the dates with the "date­d­iff" func­tion in sql itself by com­pos­ing the sql dynamically.

    HTH!

  • Dave

    Great stuff, only one prob­lem that i can see, one can eas­ily as do as you sug­gest and cal­cu­late the num­ber of days between two dates, but how (given a lan­guage like java) can you then split those days by months to form a sched­ule, or cal­en­dar. e.g. last 7 days in Sepetem­ber, all octo­ber and novem­ber and first 17 in decem­ber. Any advice?

    Cheers

    Dave

  • Dejf

    Hello, nice and sim­ple code, but there is small mis­take. How you could cal­cu­late dif­fer­ence between dates in months or years using only div function???

    Every hour has 60 min. that's OK. Every week has 7 days but not every month has 28 (or 30, 31…) days. Also not every year has 365 days. Cal­cu­lat­ing these dif­fer­ences is far more complex.

    It's non­sense to cal­cu­late these dif­fer­ences and using rule every month has 28 days. Etc.

    Best Regards

    Dejf

  • jad

    how can i cal­cu­late the dif­fer­ence between 2 dates (end­date and startdate)which both are fields in mysql database,all i want is to return the num­ber of days.
    using php

  • Rama

    Thank u very much, u made my work very simple(in php get­ting the days dif­fer­ence between dates).

  • Dharm Shankar

    Had u checked with 2038 and above?

  • nagesh

    i want to replace the null value in date col­umn while writ­ing to a file with an empty space with out chang­ing the type of column

  • soso

    To find num­ber of days, round() would be bet­ter than floor() i think. For dates includ­ing a sum­mer or win­ter hour change (i.e. march). i had this prob­lem on php/windows.

  • adams

    How does one cal­cu­late dif­fer­ence of two dates when one of the two dates is null ?

    eg. select date1-date2 from dual;

    Here if date2 is null, how do we man­age to cal­cu­late the dif­fer­ence ? Same if date1 con­tains a null value ?

  • / snip­tools

    Hi Adams,

    You men­tion "dual" so I will assume you are work­ing with Oracle.

    Let me think of a more grace­ful way, but you should be able to achieve what you need with case state­ments in Oracle's PL/SQL. E.g.,

    SELECT
    case when date2 is null then date 1
    when date1 is null then date2
    else date1-date2
    end
    FROM dual;

    Or some­thing like that, hope that gets you started on the right track.

  • Mika Lim

    The code is look like easy and use­ful, but the mktime() func­tion only can sup­port between 2 Jan­u­ary 1970 until
    18 Jan­u­ary 2038, may be this is a lim­i­ta­tion for unix date for­mat. So any­one can be solve this problem?

  • Alan

    Will some­one please help me in writ­ing the Pl/SQL code.. I wanna a code.…
    I want to cal­cu­late the num­ber of days between the two dates provided.

  • murry

    how to get the age from date of birth and todays date in access 2003

    plz give answer as soon as possible

  • John H

    Thanks for post­ing this example.

    I used it as a basis to cal­cu­late the dif­fer­ence in weeks, days, hours and min­utes between two Unix date stamps.

    $diff = $end_date — $start_date;
    $weeks = floor($diff/60/60/24/7);
    echo "Weeks Dif­fer­ence = ". $weeks;
    $diff -= $weeks ? $weeks * (60×60×24×7) : 0;
    $days = floor($diff/60/60/24);
    echo "Days Dif­fer­ence = ". $days;
    $diff -= $days ? $days * (60×60×24) : $dif­fer­ence;
    $hours = floor($difference/60/60);
    echo "Hours Dif­fer­ence = ". $hours;
    $dif­fer­ence -= $hours ? $hours * (60×60) : $dif­fer­ence;
    $min­utes = floor($difference/60);
    echo "Min­utes Dif­fer­ence = ". $minutes;