Forum Stats

  • 3,759,024 Users
  • 2,251,493 Discussions
  • 7,870,471 Comments

Discussions

Difference between two dates in Oralce SQL

Ranagal
Ranagal Member Posts: 635 Bronze Badge
edited Aug 7, 2019 1:08PM in SQL & PL/SQL

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

Mustafa_KALAYCISven W.mathguy

Best Answer

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond
    edited Jul 16, 2019 2:54AM Accepted Answer

    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_ssfrom   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
«1345

Answers

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond
    edited Jul 16, 2019 2:40AM

    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.

    Ranagal
  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond
    edited Jul 16, 2019 2:54AM Accepted Answer

    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_ssfrom   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
  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    edited Jul 16, 2019 2:55AM

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

    Ranagal
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,366 Bronze Crown
    edited Jul 16, 2019 4:55AM

    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?

  • Ranagal
    Ranagal Member Posts: 635 Bronze Badge
    edited Jul 16, 2019 5:19AM

    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

  • Ranagal
    Ranagal Member Posts: 635 Bronze Badge
    edited Jul 16, 2019 5:21AM

    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

  • Ranagal
    Ranagal Member Posts: 635 Bronze Badge
    edited Jul 16, 2019 5:22AM

    Hi,

    Thanks for the link. I helped me.

    Regards,

    Ranagal

  • Stew Ashton
    Stew Ashton Member Posts: 2,861 Gold Trophy
    edited Jul 16, 2019 6:49AM

    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 idsfrom with_iymorder 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

  • Stew Ashton
    Stew Ashton Member Posts: 2,861 Gold Trophy
    edited Jul 16, 2019 7:57AM

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

    Regards, Stew

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond
    edited Jul 16, 2019 9:04AM
    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.