6 Replies Latest reply on Jan 4, 2011 11:06 AM by Ora

    dates differences in minutes

    user10447332
      i have two dates.i need the difference of teh two dates in minutes.how to do it.
        • 1. Re: dates differences in minutes
          Nimish Garg
          try

          SELECT
               FLOOR((date1 - date2) * 24 * 60) minutes
          FROM
          dual


          Nimish Garg
          http://nimishgarg.blogspot.com/
          • 2. Re: dates differences in minutes
            Nimish Garg
            Here are some more date difference related queries

            http://nimishgarg.blogspot.com/2010/08/oracle-sql-date-difference-in-days.html
            http://nimishgarg.blogspot.com/2010/02/oracle-sql-facebook-style-time-of-post.html
            http://nimishgarg.blogspot.com/2010/02/oracle-sql-facebook-style-time-of-post_23.html



            Nimish Garg
            http://nimishgarg.blogspot.com/
            • 3. Re: dates differences in minutes
              Ora
              To retrieve the total elapsed time in minutes, you can execute the following SQL:

              select (endingDateTime - startingDateTime) * 1440
              from table_name;

              Since taking the difference between two dates in Oracle returns the difference in fractional days, you need to multiply the result by 1440 to translate your result into elapsed minutes.

              24 * 60 = 1440

              24 hours in a day * 60 minutes in an hour
              • 4. Re: dates differences in minutes
                user10447332
                select min(CREATION_DATE) ,max(CREATION_DATE)
                from xxcss_macd_detail_intf
                where header_id=500

                o/p is
                MIN(CREATION_DATE)     MAX(CREATION_DATE)
                10/22/2010 12:22:42 AM     10/22/2010 5:32:49 AM

                select (numtodsinterval(max(CREATION_DATE)-min(CREATION_DATE),'day') ) timedif
                from xxcss_macd_detail_intf
                where header_id=500


                o/p is

                TIMEDIF
                +00 05:10:06.999999


                i wanto to convert this 5 hours 10 minutes into minutes
                i am using the below query

                select (numtodsinterval(max(CREATION_DATE)-min(CREATION_DATE),'day') ) *1440 timedif_min
                from xxcss_macd_detail_intf
                where header_id=500

                the o/p is
                \
                TIMEDIF_MIN
                +310 02:47:59.999998

                but i want to get only 310 with out + symbol and removing of this value 02:47:59.999998.

                i have to get only 310.how to do it
                • 5. Re: dates differences in minutes
                  737905
                  Try this:
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  with t as (select SYSDATE col1, SYSDATE - 10 col FROM dual)
                    2  SELECT n_days * 24 * 60 In_Mins FROM
                    3* (SELECT col1-col n_days from t)
                  SQL> /
                  
                     IN_MINS
                  ----------
                       14400
                  
                  SQL> 
                  • 6. Re: dates differences in minutes
                    Ora
                    As earlier I explained, difference between two dates in Oracle returns the difference in fractional days, you need to multiply the result by 1440 to translate your result into elapsed minutes. Also there will not be any problem of '+' symbol.

                    with t1 as (select min(CREATION_DATE) a,max(CREATION_DATE) b
                    from xxcss_macd_detail_intf
                    where header_id=500)
                    select (a-b)*1440
                    from t1