Calculating Date Differences

Discussion in 'Database' started by pradeep, Jan 30, 2007.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    You have two dates (either in your code or in your database columns). How to calculate the difference between them in years, months, weeks etc!

    This is one of the most common questions on many forums, and the logic in general is quite simple. Most programming languages give you the ability to get "epoch times" (number of seconds/millioseconds since 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 for popular databases and PHP. The PHP logic should be applicable to many programming languages unless they already provide handy in-built functions.

    MYSQL



    Take a look at the following functions:

    PERIOD_DIFF
    DATE_ADD
    DATE_SUB

    From: http://www.mysql.com/doc/en/Date_and_time_functions.html

    POSTGRESQL



    No functions provided, you can use + and - operators.
    More detailed info about this - http://techdocs.postgresql.org/techdocs/faqdatesintervals.php

    SQL SERVER



    Code:
    ldDate1=CTOD(12/01/2002)
    ldDate2=CTOD(02/15/2003) ? (YEAR(ldDate2)+MONTH(ldDate2)/;
    12-YEAR(ldDate1)-MONTH(ldDate1)/12)*12

    ORACLE



    Use this function:

    Code:
    CREATE OR REPLACE
    PROCEDURE
    print_date_diff(p_dte1 IN DATE, p_dte2 IN DATE)
    IS
      v_diff NUMBER := 0;
      v_hrs NUMBER := 0;
      v_min NUMBER := 0;
      v_sec NUMBER := 0;
    BEGIN
      v_diff := ABS(p_dte2 - p_dte1);
      v_hrs := TRUNC(v_diff, 0)*24;  -- start with days portion if any
      v_diff := (v_diff - TRUNC(v_diff, 0))*24;  -- lop off whole days, convert
    to hrs
      v_hrs := v_hrs + TRUNC(v_diff, 0);  -- add in leftover hrs if any
      v_diff := (v_diff - TRUNC(v_diff, 0))*60;  -- lop off hrs, convert to mins
      v_min := TRUNC(v_diff, 0);  -- whole mins
      v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);  -- lop off mins,
    convert to secs
      DBMS_OUTPUT.put_line(
        TO_CHAR(v_hrs) || ' HRS ' ||
        TO_CHAR(v_min) || ' MIN ' ||
        TO_CHAR(v_sec) || ' SEC');
    END print_date_diff;

    MS EXCEL



    This method does not use the day of the month in its calculations. For example, given a start date of 10/31/00 (EDate) and an end date of 11/2/00 (LDate), one month is returned even though only two days elapsed.

    For this method, use the following formula

    Code:
    =(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)

    MSACCESS



    Use the DATEDIFF function.

    PHP



    PHP:
    <?
    $dateDiff mktime(12,0,0,04,20,2003) - mktime(11,0,0,04,20,2003);
    printf("Difference in seconds:  %d<br />",$dateDiff);
    printf("<br />Years Difference   = %d",floor($dateDiff/365/60/60/24));
    printf("<br />Months Difference = %d",floor($dateDiff/60/60/24/7/4)) ;
    printf("<br />Weeks Difference   = %d",floor($dateDiff/60/60/24/7)) ;
    printf("<br />Days Difference    = %d",floor($dateDiff/60/60/24));
    printf("<br />Hours Difference   = %d",floor($dateDiff/60/60)) ;
    printf("<br />Minutes Difference = %d",floor($dateDiff/60));
    ?>

    NOTES



    A. 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)

    B. 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.

    C. 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.
     
  2. vikas1234

    vikas1234 New Member

    Joined:
    Aug 21, 2008
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    0
    Nice article .... please post more article related to mysql & php
     
  3. GaryWatcock

    GaryWatcock New Member

    Joined:
    Oct 1, 2008
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    hawaii
    hi,

    Nice article .... please post more article related to mysql & php
    01-30-2007 09:36 PM
    :crazy: :crazy: :crazy:
     
  4. chickspox

    chickspox New Member

    Joined:
    Feb 1, 2009
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    How do I calculate the difference between a recorded date in SQL and current date?

    Let's say, in my sql table my date is 2-12-2008.
    So I want to use a function in sql that will return the different between that date and the current date.

    How do I do that?
     
  5. wondershare

    wondershare New Member

    Joined:
    Jan 20, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    wonderful!

    so wonderful, I am looking for it.

    thanks
     
  6. utkarshdshah

    utkarshdshah New Member

    Joined:
    Aug 18, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Hi!

    Nice Article.

    Thanks
     
  7. jhonackerman

    jhonackerman Banned

    Joined:
    Oct 13, 2011
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    Really nice and great post for calculating data difference,thanks for the great post.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice