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

# Difference between two dates in Oralce 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

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

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

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

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

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

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

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

Hi,

Thanks for the link. I helped me.

Regards,

Ranagal

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

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

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

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

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

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

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

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 Назад Вперед