[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8669
  • Last Modified:

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;
0
grim_toaster
Asked:
grim_toaster
  • 4
  • 3
1 Solution
 
earth man2Commented:
ER .. UM

Businesses tend to work in months, quarters and years for financial reporting.  That kinda makes it useful.
0
 
grim_toasterAuthor 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!
0
 
SDuttaCommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
grim_toasterAuthor 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?
0
 
SDuttaCommented:
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
 
grim_toasterAuthor 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.
0
 
SDuttaCommented:
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.
SQL> select add_months(to_date('2004-01-31','yyyy-mm-dd'),1) from dual;
ADD_MONTHS(TO_DATE('
--------------------
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
 
grim_toasterAuthor 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!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now