Difference between two Year Month and Day values in Oracle

Question

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?

What does "be ok" mean? Your business users would need to tell you whether it is reasonable to assume that months have exactly 30 days and years have exactly 365 days and would need to validate that the calculations would be "ok". We don't know your business, we don't know what the data represents, so we don't know the right algorithm for your business. — Justin Cave
Justin, by "be ok" I wanted to mean whether the approach would provide proper results from calculation point of view. The data represents service period of an employee. And, the users could not guide on what/how should the calculation be done. — I Roy
You'd need to define "proper results". Is 2 months greater than 1 month 29 days? It depends on the month. Maybe it's fine to be potentially off by a few days for whatever problem you are trying to solve. — Justin Cave

peter.hrasko.sk peter.hrasko.sk · Accepted Answer · 2016-02-23T20:07:20

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.

Difference between two Year Month and Day values in Oracle

1 Answers