9 Replies Latest reply: Sep 2, 2014 5:42 AM by Moazzam RSS

    How to calculate time difference

    Kam_oracle_apex

      Hi friends,

       

      I want to calculate and sum the time difference.

      i have two fields in a table. IN and OUT. for eg.

       

      IN          OUT          DIFFER

      01:46     15:30               ?

      01:47     15:45               ?

      02:50     17:30               ?

       

      I want minus above figure and result will appear in differ column.

       

      Please help

       

      Regards,

        • 1. Re: How to calculate time difference
          Dan Jankowski

          What is the data type of the IN and OUT fields? (hint - it's really easy if they are DATE)

          • 2. Re: How to calculate time difference
            GregV

            Hi,

             

            Assuming both the columns are of VARCHAR2 datatype and "represent" hours and minutes:

            WITH sample_data AS (SELECT '01:46' in_col, '15:30' out_col FROM DUAL

                                 UNION ALL

                                 SELECT '01:47', '15:45' FROM DUAL

                                 UNION ALL

                                 SELECT '02:50', '17:30' FROM DUAL

                                )

            SELECT in_col,

                   out_col,

                   to_char(TO_DATE((TO_DATE(out_col, 'HH24:MI')-TO_DATE(in_col, 'HH24:MI')) * 86400, 'SSSSS'), 'HH24:MI') differ

            FROM sample_data;     

             

            SQL> WITH sample_data AS (SELECT '01:46' in_col, '15:30' out_col FROM DUAL

              2                       UNION ALL

              3                       SELECT '01:47', '15:45' FROM DUAL

              4                       UNION ALL

              5                       SELECT '02:50', '17:30' FROM DUAL

              6                      )

              7  SELECT in_col,

              8         out_col,

              9         to_char(TO_DATE((TO_DATE(out_col, 'HH24:MI')-TO_DATE(in_col, 'HH24:MI')) * 86400, 'SSSSS'), 'HH24:MI') differ

            10  FROM sample_data;

             

            IN_COL OUT_COL DIFFER

            ------ ------- ------

            01:46  15:30   13:44

            01:47  15:45   13:58

            02:50  17:30   14:40

             

            SQL>

            • 3. Re: How to calculate time difference
              Roger

              and to take it one step further...

               

              WITH sample_data (in_col, out_col)

                AS (SELECT '01:46', '15:30' FROM DUAL UNION ALL

                    SELECT '01:47', '15:45' FROM DUAL UNION ALL

                    SELECT '02:50', '17:30' FROM DUAL UNION ALL

                    SELECT '23:30', '01:30' from dual)

              SELECT in_col

                    , out_col

                    , CASE WHEN out_col < in_col

                         THEN

                            TO_CHAR(TO_DATE(((TO_DATE(out_col,'HH24:MI') + 1) - TO_DATE(in_col,'HH24:MI')) * 86400, 'SSSSS'), 'HH24:MI')

                         ELSE

                            TO_CHAR(TO_DATE((TO_DATE(out_col,'HH24:MI') - TO_DATE(in_col,'HH24:MI')) * 86400, 'SSSSS'), 'HH24:MI')

                         end differ

                 FROM sample_data;

               

              hth

              • 4. Re: How to calculate time difference
                Kam_oracle_apex

                Hi Roger,

                 

                Fields type are varchar2

                 

                Regards

                • 5. Re: How to calculate time difference
                  Nimish Garg

                  try this

                   

                  SQL> WITH sample_data AS
                    2  (
                    3  SELECT '01:46' in_col, '15:30' out_col FROM DUAL
                    4  UNION ALL
                    5  SELECT '01:47', '15:45' FROM DUAL
                    6  UNION ALL
                    7  SELECT '02:50', '17:30' FROM DUAL
                    8  )
                    9  SELECT in_col,
                   10  out_col,
                   11  to_char(TO_DATE(out_col, 'HH24:MI')-TO_DATE(in_col, 'HH24:MI') + trunc(sysdate),'hh24:mi') differ
                   12  FROM sample_data;
                  
                  
                  IN_CO OUT_C DIFFE
                  ----- ----- -----
                  01:46 15:30 13:44
                  01:47 15:45 13:58
                  02:50 17:30 14:40
                  
                  • 6. Re: How to calculate time difference
                    Kam_oracle_apex

                    Hi Nimish,

                     

                    Yes. Its working. Thanks a lot.

                     

                    Regards,

                    • 7. Re: How to calculate time difference
                      Kam_oracle_apex

                      Hi Nimish

                       

                      can i sum differ?

                       

                      Regards,

                      • 8. Re: How to calculate time difference
                        BluShadow

                        I'm sure you could but it would depend what you want the output to look like.

                        Post appropriate example data and expected output so that people can help properly.

                        • 9. Re: Re: How to calculate time difference
                          Moazzam

                          May be this can help you:

                           

                          WITH sample_data AS 
                              ( 
                              SELECT '01:46' in_col, '15:30' out_col FROM DUAL 
                              UNION ALL 
                              SELECT '01:47', '15:45' FROM DUAL 
                              UNION ALL 
                              SELECT '02:50', '17:30' FROM DUAL 
                              ) 
                          , tmp AS
                            (
                            SELECT in_col, 
                            out_col, 
                            to_char(TO_DATE(out_col, 'HH24:MI')-TO_DATE(in_col, 'HH24:MI') + trunc(sysdate),'hh24:mi') differ  ,
                            to_char(TO_DATE(out_col, 'HH24:MI'),'sssss') - to_char(TO_DATE(in_col, 'HH24:MI'),'sssss') differ_sec
                            FROM sample_data
                            )
                            SELECT in_col,
                                    out_col,
                                    differ,
                                    trunc(sum(differ_sec) OVER()/3600) hh,
                                    mod(sum(differ_sec) OVER (),3600)/60 mi
                            FROM tmp 
                          

                           

                          IN_COL OUT_COL DIFFER         HH         MI

                          ------ ------- ------ ---------- ----------

                          01:46  15:30   13:44          42         22

                          01:47  15:45   13:58          42         22

                          02:50  17:30   14:40          42         22


                          3 rows selected.