SQL query to find difference between two time data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL query to find difference between two time data

  1. #1
    Join Date
    Feb 2006
    Posts
    86

    SQL query to find difference between two time data

    my requirement is :
    Time1 :=' 09:12 '
    Time2 :=' 18:24'

    now kind give me a query to find difference between time1 and time2 in
    hour minute and second form.

    for ex (2 hrs 35 mins 45 sec)

    Thanx in advance

  2. #2
    Join Date
    Sep 2007
    Posts
    36
    I'm not sure you can do this 'nicely' in one query.
    Something like this:

    SELECT (SYSDATE - (SYSDATE - 0.1)) * 24*60*60
    FROM dual;

    will get you the interval in seconds. Then something like this:

    SELECT TO_CHAR(TRUNC(:time_in_seconds/60/60),'09') ||'hrs'||
    TO_CHAR(TRUNC(MOD(:time_in_seconds,3600)/60),'09') ||'mins'||
    TO_CHAR(MOD(MOD(:time_in_seconds,3600),60),'09')||'secs'
    FROM dual;

    ...will format. You might consider writing a function for this.
    J

  3. #3
    Join Date
    Apr 2001
    Location
    Vadodara, India
    Posts
    249
    I have attached one file.

    This function will return hours and minutes. e.g. 22:30. You have to modify it as per your requirement.
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2007
    Posts
    36
    bhattnirav offers an excellent solution.

    This achieves the same, but in a different way.


    CREATE OR REPLACE FUNCTION j_get_time_interval (p_start_time IN DATE, p_end_time IN DATE)
    RETURN VARCHAR2 IS
    nTimeIntervalInSeconds NUMBER;

    BEGIN

    SELECT (p_end_time - p_start_time) * 24*60*60
    INTO nTimeIntervalInSeconds
    FROM dual;

    RETURN TO_CHAR(TRUNC(nTimeIntervalInSeconds/60/60),'09') ||'hrs'||
    TO_CHAR(TRUNC(MOD(nTimeIntervalInSeconds,3600)/60),'09') ||'mins'||
    TO_CHAR(MOD(MOD(nTimeIntervalInSeconds,3600),60),'09')||'secs';

    END j_get_time_interval;
    /

  5. #5
    Join Date
    Oct 2009
    Location
    Banglore
    Posts
    8
    Hi Binoy,
    This will also achieves the same, but in a single query.

    SELECT TO_CHAR(TRUNC((( to_date(to_char(sysdate,'DD-MON_YYYY')||' 10:30:00','DD-MON_YYYY HH24:MI:SS' ) -
    to_date(to_char(sysdate,'DD-MON_YYYY')||' 09:45:00','DD-MON_YYYY HH24:MI:SS' ) ) * 24*60*60)/60/60),'09') ||':'||
    TO_CHAR(TRUNC(MOD((( to_date(to_char(sysdate,'DD-MON_YYYY')||' 10:30:00','DD-MON_YYYY HH24:MI:SS' ) -
    to_date(to_char(sysdate,'DD-MON_YYYY')||' 09:45:00','DD-MON_YYYY HH24:MI:SS' ) ) * 24*60*60),3600)/60),'09') ||':'||
    TO_CHAR(MOD(MOD((( to_date(to_char(sysdate,'DD-MON_YYYY')||' 10:30:00','DD-MON_YYYY HH24:MI:SS' ) -
    to_date(to_char(sysdate,'DD-MON_YYYY')||' 09:45:00','DD-MON_YYYY HH24:MI:SS' ) ) * 24*60*60),3600),60),'09') as "HH:MI:SS"
    FROM dual;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width