# Interval year to month - What is the point?

I have a question relating TO the interval datatypes IN Oracle 9i.  Basically what is the point of the interval year to month? You see, all of the examples I've found relating to computing the difference between two dates using the interval year to month
data type have been to calculate someones age, or find the length of service of an employee, using something similar to the below query:

SELECT EXTRACT(YEAR FROM ((TO_TIMESTAMP('01/01/1990', 'DD/MM/YYYY') - TO_TIMESTAMP('16/01/1980', 'DD/MM/YYYY')) YEAR TO MONTH))
FROM dual;

However, if you actually run the above query, it will return 10 years (of service), when in fact it should be 9 years, 11 months...
(if you extract months it returns 0).  So I can't really see the point in using this as it will return incorrect results (as in the
length of service example, the employee would be down as 10 years and would possibly get a greater bonus than if they had done 9).

I know as an alternative you can use the below, which works fine (I have no problems with the day TO second):

SELECT FLOOR(EXTRACT(DAY FROM ((TO_TIMESTAMP('01/01/1990', 'DD/MM/YYYY') - TO_TIMESTAMP('16/01/1980', 'DD/MM/YYYY')) DAY TO SECOND)) / 365.25)
FROM dual;

But this seems considerably more complex than the pre-9i way (AND what I would still USE) OF:

SELECT FLOOR(MONTHS_BETWEEN(TO_DATE('01/01/1990', 'DD/MM/YYYY'), TO_DATE('16/01/1980', 'DD/MM/YYYY')) / 12)
FROM dual;

Oh, and as a further point, the following returns the correct value of 16/01/1990 for 10 years service, and 16/01/1989 for 9 years

SELECT to_timestamp('16/01/1980', 'DD/MM/YYYY') + INTERVAL '9' YEAR
FROM   dual;
LVL 7
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
ER .. UM

Businesses tend to work in months, quarters and years for financial reporting.  That kinda makes it useful.
Author Commented:
Yes, but any calculation you did between two dates as mentioned in the question would not be accurate, and for business processing you would need to be exact!
Commented:
Hello grim_toaster,

The problem you are facing is due to the precision of the datatype. You are using a lower precision datatype and expecting higher precision results.

The manual states :
INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. Specify INTERVAL YEAR TO MONTH as follows:
INTERVAL YEAR [(year_precision)] TO MONTH
where year_precision is the number of digits in the YEAR datetime field. The default value of year_precision is 2.

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. Specify this datatype as follows:
INTERVAL DAY [(day_precision)]
TO SECOND [(fractional_seconds_precision)]
where
day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6.

Since you cannot store a partial month in the YEAR TO MONTH interval, it rounds the months if you have a partial month. If half a month is going to make a difference in your results, use the DAY TO SECOND alternative you have described yourself.

here is an example showing the rounding (15 days=0 months; 16 days=1 month):

SQL> SELECT TO_CHAR((
2  TO_TIMESTAMP('01/02/2003', 'DD/MM/YYYY') - TO_TIMESTAMP('16/01/2003', 'DD/MM/YYYY')
3  ) YEAR TO MONTH)
4  FROM dual;

TO_CHAR((TO_TI
--------------
+000000000-01

SQL> SELECT TO_CHAR((
2  TO_TIMESTAMP('01/02/2003', 'DD/MM/YYYY') - TO_TIMESTAMP('17/01/2003', 'DD/MM/YYYY')
3  ) YEAR TO MONTH)
4  FROM dual;

TO_CHAR((TO_TI
--------------
+000000000-00

Hope this helps.
SDutta

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
I had a feeling it was something to do with precision of the value, thanks for that!  But I still have one question, I cannot think of any kind of application where you would want to use something like that.  Does it actually perform significantly quicker than doing the good old standard way of months_between?
Commented:
I dont think I will be able to answer your question about comparative speeds since I have not benchmarked them. The TZ and INTERVAL datatypes have been introduced in 9i for ANSI compatibility and additional functionality and does not stop you from using the old DATE type and functions. I believe the main benefit is making the sql more readable :

IF lag_time > INTERVAL '6' DAY THEN ...
or
next_vacc_date := childs_age + INTERVAL '01-03' YEAR TO MONTH;
Author Commented:
I totally agree that it looks better if you do your:
next_vacc_date := childs_age + INTERVAL '01-03' YEAR TO MONTH;

But, I don't have the problem with that part.  The thing I truly don't understand, or see the point of, is if you subtract two timestamps then convert that to the interval, you don't get an exact answer.
Commented:
Well its a question of truncating vs. rounding. If the months were truncated, you will get the exact result. May be you can file a TAR on this and Oracle will give you their opinion. In the meantime, don't change all your code to use the INTERVAL functions, they don't work the same way as the previous DATE functions. I can give you another issue to look at which does not work like it should :

Adding one month to Jan 31 2004 works good.
--------------------
29-FEB-2004 00:00:00
1 row selected.

Adding one month to the same date using interval craps out ! It is basically trying to add 1 to the month part only and creates a date of 2004-02-31 which is invalid.
SQL> select to_date('2004-01-31','yyyy-mm-dd')+ INTERVAL '00-01' YEAR TO MONTH from dual;
TO_DATE('2004-01-31'
--------------------
ORA-01839: date not valid for month specified
Author Commented:
Well that's another good one!  Maybe Oracle will get it right in 10!?  We'll just have to wait and see!  Thanks for that though!
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.