CODEWITHSUNDEEP
oracle-database
I Roy
I Roy

Reputation: 11

Difference between two Year Month and Day values in Oracle

I have two sets of Year, Month and Day values. Eg. 15 years 7 months and 23 Days and 7 years 9 months and 12 days. Can the difference between two such values be found in terms of Years, Month and Days? I have thought of converting the values to days, compute the difference and again convert the result back to Years, Months and Days? For this, I have to assume 30 days/month and 365 days/year. Will this approach be ok?

Upvotes: 0

Views: 308

Answers (1)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

When you think about it thoroughly, you'll realize that you can't calculate a difference of two "time intervals" when months are in place; simply because a subtraction of months can result in different number of days. You can subtract years, you can subtract weeks, you can subtract days,... you can subtract days-to-seconds, you can subtract years-to-months. However, you can't subtract years-to-days.

Example:

SQL> select timestamp'1915-07-23 00:00:00' - timestamp'1907-09-12 00:00:00' as diff_day_to_second_interval from dual;

DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000002871 00:00:00

This is your 15 years, 7 months, 23 days minus 7 years, 9 months, 12 days when based on January 1, 1900. This gave us 2871 days difference.

However, consider the following two examples, simply shifted by 1 and 6 months to past

select timestamp'1915-06-23 00:00:00' - timestamp'1907-08-12 00:00:00' as diff_day_to_second_interval from dual;

DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000002872 00:00:00

select timestamp'1915-01-23 00:00:00' - timestamp'1907-03-12 00:00:00' as diff_day_to_second_interval from dual;

DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000002874 00:00:00

SQL>

These now gave us 2872 and 2874 days of difference.

Now, speaking of possible subtractions...

(a) subtracting year-to-month intervals

SQL> select interval'1915-07' year(4) to month - interval'1907-09' year(4) to month as diff_year_to_month_interval from dual;

DIFF_YEAR_TO_MONTH_INTERVAL
---------------------------
+000000007-10

select interval'1915-06' year(4) to month - interval'1907-08' year(4) to month as diff_year_to_month_interval from dual;

DIFF_YEAR_TO_MONTH_INTERVAL
---------------------------
+000000007-10

select interval'1915-01' year(4) to month - interval'1907-03' year(4) to month as diff_year_to_month_interval from dual;

DIFF_YEAR_TO_MONTH_INTERVAL
---------------------------
+000000007-10

SQL>

All three correctly produce a difference of 7 years and 10 months.

(b) subtracting day-to-second intervals

SQL> select interval'15 01:02:03' day(2) to second - interval'07 02:03:04' day(2) to second as diff_day_to_second_interval from dual;

DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000000007 22:58:59

select interval'14 00:01:02' day(2) to second - interval'06 01:02:03' day(2) to second as diff_day_to_second_interval from dual;

DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000000007 22:58:59

select interval'09 11:12:13' day(2) to second - interval'01 12:13:14' day(2) to second as diff_day_to_second_interval from dual;

DIFF_DAY_TO_SECOND_INTERVAL
---------------------------
+000000007 22:58:59

SQL>

All three produce the same results, as all three are subtractions of day-to-second intervals with consistent offsetting of the day/hour/minute/second parts of the interval values.

(c) subtracting year-to-day intervals

As I said: Not possible. There even is no such thing as year-to-day interval in Oracle; makers of the DB server knew why they decided not to add those to the engine.

Upvotes: 1

Related Questions