Solved

# Interval year to month - What is the point?

Posted on 2003-11-26
8,622 Views
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;
0
Question by:grim_toaster
• 4
• 3

LVL 22

Expert Comment

ER .. UM

Businesses tend to work in months, quarters and years for financial reporting.  That kinda makes it useful.
0

LVL 7

Author Comment

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!
0

LVL 10

Accepted Solution

SDutta earned 50 total points
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
0

LVL 7

Author Comment

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?
0

LVL 10

Expert Comment

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;
0

LVL 7

Author Comment

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

LVL 10

Expert Comment

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
0

LVL 7

Author Comment

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!
0

## Featured Post

### Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.