1 2 3 4 Назад Вперед 45 Ответы Последний ответ: 07.08.2019 17:08, автор: Stew Ashton

    Difference between two dates in Oralce SQL

    Ranagal

      Hello experts,

       

      DB Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

       

      I am trying to ge the difference between two dates in years:months:days:hours:minutes:seconds. I am able to do it for all except for days. Please help me on this.

       

       

      with inputs(id, s_dt, e_dt) as

      (

          select 1, date'2014-07-28', sysdate from dual union all

          select 2, date'2014-07-30', sysdate from dual union all

          select 3, to_date('20140728231324','yyyymmddhh24miss'), date'2015-12-30' from dual

      ),

      calculate as

      (

          select  t.*,

                  floor(m_bet/12) as years,

                  floor(m_bet) - (floor(m_bet/12) * 12) as months,

                  floor(e_dt - trunc(e_dt,'mm')) + 1 as days, --Incorrect

                  extract(hour from d_int) as hours,

                  extract(minute from d_int) as minutes,

                  extract(second from d_int) as seconds

          from

          (

              select t.*, months_between(e_dt, s_dt) as m_bet, numtodsinterval(e_dt - s_dt,'day') as d_int from inputs t

          ) t

      )

      select  id, s_dt, e_dt, years||':'||months||':'||days||':'||hours||':'||minutes||':'||seconds as time_diff

      from    calculate;

       

       

      Regards,

      Ranagal

        • 1. Re: Difference between two dates in Oralce SQL
          mathguy

          MONTHS_BETWEEN is probably not the best tool for this kind of exercise; for example, do you understand that there are more months between 28 February 2019 and 30 March 2019, than there are between 28 February 2019 and 31 March 2019? Indeed, the first result is 1.0645 while the latter is 1.0000 even. As you take the TRUNC() of the result, perhaps in this case it won't make a lot of difference; but if you use it with other, more random dates, you are bound to run into issues.

           

          Further than that I can't help, because the problem is ambiguous. What result would you like for the difference between 28 February 2019 and 31 March 2019? Is it a month? Or is it a month and three days? Also, separately (but similar): What is the difference between 31 May and 30 June? Is it a month? Or is it 0 months and 30 days? Presumably the difference between 30 May and 30 June is one month; if so, is the difference between 31 May and 30 June also one month?

           

          If you can give a very precise definition of the representation of the desired output as Years:Months:Days:Hours:Minutes:Seconds, perhaps we can help; but as I tried to illustrate, that definition is not clear (yet). A "very precise definition" should allow us to answer my questions from the second paragraph by following the rules without ambiguity.

           

          EDIT:

           

          ... and, by the way, another oddity about the MONTHS_BETWEEN function, which again makes it quite unsuitable for your requirement: the function simply ignores time-of-day and assumes the dates are already truncated to midnight.

           

          For example: if the input dates are 1 January and 1 February, but the time-of-day is 10:00:00 for the first date and 06:00:00 for the second date... there is no meaningful definition of the difference in months, days, hours and minutes by which the difference is one month, or more than one month. The difference should be 30 days and 20 hours. (Right?) Yet MONTHS_BETWEEN applied to those two dates will return 1.0000 even; the time-of-day is discarded before the computation is even performed.

          1 пользователь считает эту информацию полезной
          • 2. Re: Difference between two dates in Oralce SQL
            mathguy

            Here is a possible "unambiguous definition" of difference in years, months, ... , seconds, and a way to do the calculation.

             

            First, find the largest integer MM_DIFF so that ADD_MONTHS(S_DT, MM_DIFF) is less than or equal to E_DT. This will give you the years and the months (trivial computations).

             

            Then subtract ADD_MONTHS(S_DT, MM_DIFF) from E_DT. This will give you days, hours, minutes and seconds. You can get all of this at once if you cast the two terms as TIMESTAMP; then the difference (which will always be non-negative and strictly less than one month) will be an interval day to second.

             

            The key computation is that of MM_DIFF. One way to find it is to first truncate both dates to the beginning of their respective months, and then take MONTHS_BETWEEN of the results. The month difference between the dates themselves is either this number, or this number MINUS ONE. This determination (computation) is shown in PREP2 in the WITH clause below.

             

            The output is not exactly in the format you wanted, but you can finish it yourself - either with string functions, or with the EXTRACT function (which works on intervals just as it does on dates and timestamps) as you were trying to do. I will leave all that to you.

             

            with

              inputs (id, s_dt, e_dt) as (

                select 1, date'2014-07-28'                             , sysdate                                       from dual union all

                select 2, date'2014-07-30'                             , sysdate                                       from dual union all

                select 3, to_date('20140728231324', 'yyyymmddhh24miss'), date'2015-12-30'                              from dual union all

                select 4, to_date('20190320103000', 'yyyymmddhh24miss'), to_date('20190420082200', 'yyyymmddhh24miss') from dual

              )

            , prep1 (id, s_dt, e_dt, mm_bet) as (

                select id, s_dt, e_dt, months_between(trunc(e_dt, 'mm'), trunc(s_dt, 'mm')) from inputs

              )

            , prep2 (id, s_dt, e_dt, mm_diff) as (

                select id, s_dt, e_dt, mm_bet - case when add_months(s_dt, mm_bet) > e_dt then 1 else 0 end from prep1

              )

            select id, s_dt, e_dt, trunc(mm_diff / 12) as yy, mod(mm_diff, 12) as mm,

                   cast(e_dt as timestamp) - cast(add_months(s_dt, mm_diff) as timestamp) as dd_to_ss

            from   prep2

            ;

             

              ID S_DT                E_DT                  YY   MM DD_TO_SS        

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

               1 2014-07-28 00:00:00 2019-07-15 23:44:34    4   11 +17 23:44:34.000000

               2 2014-07-30 00:00:00 2019-07-15 23:44:34    4   11 +15 23:44:34.000000

               3 2014-07-28 23:13:24 2015-12-30 00:00:00    1    5 +01 00:46:36.000000

               4 2019-03-20 10:30:00 2019-04-20 08:22:00    0    0 +30 21:52:00.000000

            • 3. Re: Difference between two dates in Oralce SQL
              BluShadow

              As Dude says, your question is ambiguous and doesn't really make sense when you consider that months have different numbers of days.

              Please see the community document: PL/SQL 101 : DataTypes - DATE section 10 which explains the issue with the months/days boundary.

              i.e. you can have years to months, and you can have days to seconds, but you cannot span the months to days boundary, simply because different months have different numbers of days.

               

              Oracle does make some assumptions about months when looking at the interval between dates, such that the interval between the last day of different months is considered a whole month even if that's between e.g. the last day of February (28th/29th) and the last day of July (31st), where the actual date is different.

              1 пользователь считает эту информацию полезной
              • 4. Re: Difference between two dates in Oralce SQL
                Mustafa KALAYCI

                if today is 0 degree and tomorrow will be colder twice as of today, how many degrees will the weather be tomorrow? you can not arithmetical operation on that and what you are trying to do is kind of this because "month" is not a strict length of time. it could be 28,29,30,31 so this kind of calculation will be always ambiguous as others said. why do you need after all?

                • 5. Re: Difference between two dates in Oralce SQL
                  Ranagal

                  Hi mathguy,

                   

                  Thanks a lot. It is simply superb. That is exactly what I wanted. And I apologize for being ambiguous in defining years, months, ...seconds. But I got small doubt though.

                   

                  If I try to use to_timestamp instead of casting, would it be something else or the same ? Because the following code after prep2 gives a different result. And I don't know understand it. Request you to explain. Thanks in advance.

                   

                  res as

                  (

                  select  id, s_dt, e_dt,

                          trunc(mm_diff / 12) as yy,

                          mod(mm_diff, 12) as mm,

                          cast(e_dt as timestamp) - cast(add_months(s_dt, mm_diff) as timestamp) as dd_to_ss,

                          to_timestamp(e_dt) - to_timestamp(add_months(s_dt, mm_diff)) as dd_to_ss_1

                  from prep2

                  )      

                  select  r.*,

                          dd_to_ss - dd_to_ss_1

                  from    res r;

                   

                  Regards,

                  Ranagal

                  • 6. Re: Difference between two dates in Oralce SQL
                    Ranagal

                    Right.

                     

                    But I don't know why. It is just that I need to calculate and move on. I mean can't question the business decisions in my limits.

                     

                    Regards,

                    Ranagal

                    • 7. Re: Difference between two dates in Oralce SQL
                      Ranagal

                      Hi,

                       

                      Thanks for the link. I helped me.

                       

                      Regards,

                      Ranagal

                      • 8. Re: Difference between two dates in Oralce SQL
                        Stew Ashton

                        If you want to use Oracle's way of computing these differences, try getting the "year to month" interval between the dates, then getting the "day to second" interval that remains. You need to watch out for rounding: if the difference between two dates is 20 days, the INTERVAL YEAR TO MONTH between them will be '+00-01'.

                         

                        In the following code:

                        • lines 1 through 29 create test data with different combinations of differences
                        • line 32 gets the rounded difference in years and months between the two dates
                        • lines 33 through 36 correct for the rounding
                        • line 41 gets the remaining "day to second" interval
                        • You can now use the EXTRACT function to get all the bits you want.

                         

                        with iyms(iym) as (
                          select interval '0' year from dual union all
                          select interval '2' month from dual union all
                          select interval '1' year from dual union all
                          select interval '1-2' year to month from dual
                        )
                        , idss(ids) as (
                          select interval '+00 00:00:00' day to second from dual union all
                          select interval '+03 00:00:00' day to second from dual union all
                          select interval '+00 04:00:00' day to second from dual union all
                          select interval '+03 04:00:00' day to second from dual union all
                          select interval '+00 00:05:00' day to second from dual union all
                          select interval '+03 00:05:00' day to second from dual union all
                          select interval '+00 04:05:00' day to second from dual union all
                          select interval '+03 04:05:00' day to second from dual union all
                          select interval '+00 00:00:06' day to second from dual union all
                          select interval '+03 00:00:06' day to second from dual union all
                          select interval '+00 04:00:06' day to second from dual union all
                          select interval '+03 04:00:06' day to second from dual union all
                          select interval '+00 00:05:06' day to second from dual union all
                          select interval '+03 00:05:06' day to second from dual union all
                          select interval '+00 04:05:06' day to second from dual union all
                          select interval '+03 04:05:06' day to second from dual
                        )
                        , dates as(
                          select date '2019-02-28' date_from, 
                            date '2019-02-28' + iym + ids date_to
                          from iyms, idss
                        )
                        , with_iym as (
                          select date_from, date_to,
                            (date_to - date_from) year to month
                              - case when date_from + (date_to - date_from) year to month > date_to
                                  then interval '1' month
                                  else interval '0' month
                                end
                            iym
                          from dates
                        )
                        select with_iym.*,
                        (date_to - (date_from + iym)) day to second ids
                        from with_iym
                        order by 3,4;
                        
                        DATE_FROM            DATE_TO              IYM     IDS                   
                        2019-02-28 00:00:00  2019-02-28 00:00:00  +00-00  +00 00:00:00.000000    
                        2019-02-28 00:00:00  2019-02-28 00:00:06  +00-00  +00 00:00:06.000000    
                        2019-02-28 00:00:00  2019-02-28 00:05:00  +00-00  +00 00:05:00.000000    
                        2019-02-28 00:00:00  2019-02-28 00:05:06  +00-00  +00 00:05:06.000000    
                        2019-02-28 00:00:00  2019-02-28 04:00:00  +00-00  +00 04:00:00.000000    
                        2019-02-28 00:00:00  2019-02-28 04:00:06  +00-00  +00 04:00:06.000000    
                        2019-02-28 00:00:00  2019-02-28 04:05:00  +00-00  +00 04:05:00.000000    
                        2019-02-28 00:00:00  2019-02-28 04:05:06  +00-00  +00 04:05:06.000000    
                        2019-02-28 00:00:00  2019-03-03 00:00:00  +00-00  +03 00:00:00.000000    
                        2019-02-28 00:00:00  2019-03-03 00:00:06  +00-00  +03 00:00:06.000000    
                        2019-02-28 00:00:00  2019-03-03 00:05:00  +00-00  +03 00:05:00.000000    
                        2019-02-28 00:00:00  2019-03-03 00:05:06  +00-00  +03 00:05:06.000000    
                        2019-02-28 00:00:00  2019-03-03 04:00:00  +00-00  +03 04:00:00.000000    
                        2019-02-28 00:00:00  2019-03-03 04:00:06  +00-00  +03 04:00:06.000000    
                        2019-02-28 00:00:00  2019-03-03 04:05:00  +00-00  +03 04:05:00.000000    
                        2019-02-28 00:00:00  2019-03-03 04:05:06  +00-00  +03 04:05:06.000000    
                        2019-02-28 00:00:00  2019-04-28 00:00:00  +00-02  +00 00:00:00.000000    
                        2019-02-28 00:00:00  2019-04-28 00:00:06  +00-02  +00 00:00:06.000000    
                        2019-02-28 00:00:00  2019-04-28 00:05:00  +00-02  +00 00:05:00.000000    
                        2019-02-28 00:00:00  2019-04-28 00:05:06  +00-02  +00 00:05:06.000000    
                        2019-02-28 00:00:00  2019-04-28 04:00:00  +00-02  +00 04:00:00.000000    
                        2019-02-28 00:00:00  2019-04-28 04:00:06  +00-02  +00 04:00:06.000000    
                        2019-02-28 00:00:00  2019-04-28 04:05:00  +00-02  +00 04:05:00.000000    
                        2019-02-28 00:00:00  2019-04-28 04:05:06  +00-02  +00 04:05:06.000000    
                        2019-02-28 00:00:00  2019-05-01 00:00:00  +00-02  +03 00:00:00.000000    
                        2019-02-28 00:00:00  2019-05-01 00:00:06  +00-02  +03 00:00:06.000000    
                        2019-02-28 00:00:00  2019-05-01 00:05:00  +00-02  +03 00:05:00.000000    
                        2019-02-28 00:00:00  2019-05-01 00:05:06  +00-02  +03 00:05:06.000000    
                        2019-02-28 00:00:00  2019-05-01 04:00:00  +00-02  +03 04:00:00.000000    
                        2019-02-28 00:00:00  2019-05-01 04:00:06  +00-02  +03 04:00:06.000000    
                        2019-02-28 00:00:00  2019-05-01 04:05:00  +00-02  +03 04:05:00.000000    
                        2019-02-28 00:00:00  2019-05-01 04:05:06  +00-02  +03 04:05:06.000000    
                        2019-02-28 00:00:00  2020-02-28 00:00:00  +01-00  +00 00:00:00.000000    
                        2019-02-28 00:00:00  2020-02-28 00:00:06  +01-00  +00 00:00:06.000000    
                        2019-02-28 00:00:00  2020-02-28 00:05:00  +01-00  +00 00:05:00.000000    
                        2019-02-28 00:00:00  2020-02-28 00:05:06  +01-00  +00 00:05:06.000000    
                        2019-02-28 00:00:00  2020-02-28 04:00:00  +01-00  +00 04:00:00.000000    
                        2019-02-28 00:00:00  2020-02-28 04:00:06  +01-00  +00 04:00:06.000000    
                        2019-02-28 00:00:00  2020-02-28 04:05:00  +01-00  +00 04:05:00.000000    
                        2019-02-28 00:00:00  2020-02-28 04:05:06  +01-00  +00 04:05:06.000000    
                        2019-02-28 00:00:00  2020-03-02 00:00:00  +01-00  +03 00:00:00.000000    
                        2019-02-28 00:00:00  2020-03-02 00:00:06  +01-00  +03 00:00:06.000000    
                        2019-02-28 00:00:00  2020-03-02 00:05:00  +01-00  +03 00:05:00.000000    
                        2019-02-28 00:00:00  2020-03-02 00:05:06  +01-00  +03 00:05:06.000000    
                        2019-02-28 00:00:00  2020-03-02 04:00:00  +01-00  +03 04:00:00.000000    
                        2019-02-28 00:00:00  2020-03-02 04:00:06  +01-00  +03 04:00:06.000000    
                        2019-02-28 00:00:00  2020-03-02 04:05:00  +01-00  +03 04:05:00.000000    
                        2019-02-28 00:00:00  2020-03-02 04:05:06  +01-00  +03 04:05:06.000000    
                        2019-02-28 00:00:00  2020-04-28 00:00:00  +01-02  +00 00:00:00.000000    
                        2019-02-28 00:00:00  2020-04-28 00:00:06  +01-02  +00 00:00:06.000000    
                        2019-02-28 00:00:00  2020-04-28 00:05:00  +01-02  +00 00:05:00.000000    
                        2019-02-28 00:00:00  2020-04-28 00:05:06  +01-02  +00 00:05:06.000000    
                        2019-02-28 00:00:00  2020-04-28 04:00:00  +01-02  +00 04:00:00.000000    
                        2019-02-28 00:00:00  2020-04-28 04:00:06  +01-02  +00 04:00:06.000000    
                        2019-02-28 00:00:00  2020-04-28 04:05:00  +01-02  +00 04:05:00.000000    
                        2019-02-28 00:00:00  2020-04-28 04:05:06  +01-02  +00 04:05:06.000000    
                        2019-02-28 00:00:00  2020-05-01 00:00:00  +01-02  +03 00:00:00.000000    
                        2019-02-28 00:00:00  2020-05-01 00:00:06  +01-02  +03 00:00:06.000000    
                        2019-02-28 00:00:00  2020-05-01 00:05:00  +01-02  +03 00:05:00.000000    
                        2019-02-28 00:00:00  2020-05-01 00:05:06  +01-02  +03 00:05:06.000000    
                        2019-02-28 00:00:00  2020-05-01 04:00:00  +01-02  +03 04:00:00.000000    
                        2019-02-28 00:00:00  2020-05-01 04:00:06  +01-02  +03 04:00:06.000000    
                        2019-02-28 00:00:00  2020-05-01 04:05:00  +01-02  +03 04:05:00.000000    
                        2019-02-28 00:00:00  2020-05-01 04:05:06  +01-02  +03 04:05:06.000000
                        

                         

                        Best regards,

                        Stew Ashton

                        • 9. Re: Difference between two dates in Oralce SQL
                          Stew Ashton

                          Warning: the solution above has problems when DATE_FROM is at the end of the month. I'm looking for a workaround.

                           

                          Regards, Stew

                          • 10. Re: Difference between two dates in Oralce SQL
                            mathguy

                            Stew Ashton wrote:

                             

                            Warning: the solution above has problems when DATE_FROM is at the end of the month. I'm looking for a workaround.

                             

                            Regards, Stew

                             

                             

                            Instead of a workaround, you may find a different (and, I believe, correct) implementation of the same idea in Reply #2 in this thread.

                            • 11. Re: Difference between two dates in Oralce SQL
                              mathguy

                              Ranagal wrote:

                               

                              Hi mathguy,

                               

                              Thanks a lot. It is simply superb. That is exactly what I wanted. And I apologize for being ambiguous in defining years, months, ...seconds. But I got small doubt though.

                               

                              If I try to use to_timestamp instead of casting, would it be something else or the same ? Because the following code after prep2 gives a different result. And I don't know understand it. Request you to explain. Thanks in advance.

                               

                              res as

                              (

                              select id, s_dt, e_dt,

                              trunc(mm_diff / 12) as yy,

                              mod(mm_diff, 12) as mm,

                              cast(e_dt as timestamp) - cast(add_months(s_dt, mm_diff) as timestamp) as dd_to_ss,

                              to_timestamp(e_dt) - to_timestamp(add_months(s_dt, mm_diff)) as dd_to_ss_1

                              from prep2

                              )

                              select r.*,

                              dd_to_ss - dd_to_ss_1

                              from res r;

                               

                              Regards,

                              Ranagal

                               

                               

                              Using TO_TIMESTAMP as you did, instead of casting, would simply be wrong, and I won't spend too much time explaining this. TO_TIMESTAMP takes a string as an argument, not a date. What is happening in your query is that the date is implicitly converted to string first, using your NLS_DATE_FORMAT session parameter, and then that is converted to timestamp using your NLS_TIMESTAMP_FORMAT parameter. If the two are not identical, then, depending on the data, you may either get an error message (the best outcome, really!), or you may get incorrect results (still OK if you realize the results are wrong, look for the reason, figure it out, and fix the problem), or you may get the right results by accident WITH YOUR TEST DATA. This is really unfortunate; you may get wrong results in the future and not even notice it.

                               

                              For what it's worth, on my machine I do get the same results, because I have the same format model for both; but that's just a coincidence. I change both formats all the time, depending on what I need; in half an hour I may get different results, or I may get an error - on exactly the same data.

                               

                              The correct way to convert a date to a timestamp is with the CAST function. Why do you need another way?

                              • 12. Re: Difference between two dates in Oralce SQL
                                Ranagal

                                Hi mathguy,

                                 

                                I just wanted to understand it that's why I tried but got different results. And hence, thought of asking you to know more about it. Thanks for your time.

                                 

                                Regards,

                                Ranagal

                                • 13. Re: Difference between two dates in Oralce SQL
                                  Ranagal

                                  Hi Stew,

                                   

                                  Thanks for the alternative solution. I'm waiting for the correct one that handles the corner scenarios as well.

                                   

                                  Regards,

                                  Ranagal

                                  • 14. Re: Difference between two dates in Oralce SQL
                                    EdStevens

                                    Ranagal wrote:

                                     

                                    Right.

                                     

                                    But I don't know why. It is just that I need to calculate and move on. I mean can't question the business decisions in my limits.

                                     

                                    Regards,

                                    Ranagal

                                    Don't you think the 'decision makers' need to at least be made aware of flaws in there assumptions, lest they make crucial business decisions based on flawed data?

                                    1 2 3 4 Назад Вперед