INTERVAL types are new to Oracle, so let's look at a few examples of how they might be used. We hope to spark your natural creativity here so you can begin to think about how you might use INTERVAL types in systems you develop.
Imagine for a moment that you work for a food cannery. Canned goods must be stamped with an expiration date, and it's your job to write the software to compute this date for whatever item is being canned at the moment. This is a problem that calls for the use of intervals. The food in a can is good for a certain interval of time, and the expiration date marks the end of that interval. Because canned goods last for long periods of time, we're talking intervals in terms of years and months.
To further complicate matters, each different type of canned food may have a different expiration date. Canned vegetables tend to last longer than canned meats, and the expiration dates for pet food can be pushed out further than for human food. You decide to create a table to store the "good for" interval for each product that your company cans. One possibility for that table is:
CREATE TABLE shelf_life (product_id NUMBER, good_for_period INTERVAL YEAR(2) TO MONTH NOT NULL);You then realize (when you've been programming for enough years, experience speaks to you in powerful and mysterious ways) that you will need a PL/SQL function to return the expiration date for a product, given its "good for" period of time. The function can be very concisely expressed as follows:
CREATE OR REPLACE FUNCTION expiration_date (good_for_period_in IN shelf_life.good_for_period%TYPE) RETURN DATEISBEGIN-- Truncate the current date so that we are left-- with only the date, and not the time of day. RETURN TRUNC (SYSDATE) + good_for_period_in;END;Had we used the first solution (two number columns for years and months, rather than an INTERVAL column), our computation would have been more complicated:
expiration_date:= ADD_MONTHS(TRUNC(SYSDATE), (good_for_years_in * 12) + good_for_months_in);The interval solution has several advantages over the other:
It's easier to code, which means fewer coding errors and less debugging.
It's easier to read and understand after you code it.
The use of INTERVAL YEAR TO MONTH in the good_for table ensures that only valid year/month intervals will be stored. You never have to worry about interpreting a value such as 1 year and 24 months.
Another use for INTERVAL types is when you need to look at the difference between two dates. Consider the following example, which computes an employee's length of service:
DECLARE start_date DATE; end_date DATE; service_interval INTERVAL YEAR TO MONTH; years_of_service NUMBER; months_of_service NUMBER;BEGIN --Normally, we would retrieve start and end dates from a database. start_date:= TO_DATE('29-DEC-1988','dd-mon-yyyy'); end_date:= TO_DATE ('26-DEC-1995','dd-mon-yyyy'); --Determine and display years and months of service: service_interval:= (end_date - start_date) YEAR TO MONTH; DBMS_OUTPUT.PUT_LINE(service_interval); --Use the new EXTRACT function to grab individual --year and month components. years_of_service:= EXTRACT(YEAR FROM service_interval); months_of_service:= EXTRACT(MONTH FROM service_interval); DBMS_OUTPUT.PUT_LINE(years_of_service || ' years and ' || months_of_service || ' months');END;The line that performs the actual calculation to get years and months of service is:
|
|
|
The YEAR TO MONTH is part of the interval expression syntax, about which you'll read more in the later section Section 10.3. You can see, however, that computing the interval is as simple as subtracting one date from another. Had we not used an INTERVAL type, we would have had to code something like the following:
months_of_service:= ROUND(months_between(end_date, start_date));years_of_service:= TRUNC(months_of_service/12);months_of_service:= MOD(months_of_service,12);Again, the non-INTERVAL solution is much more complex to code and to understand afterwards.
|
| Why Two INTERVAL Datatypes? We were initially puzzled about the need for two INTERVAL datatypes. We noticed that between the two types, all portions of a TIMESTAMP value were accounted for, but the decision to treat year and month separately from days, hours, minutes, and seconds seemed at first rather arbitrary. Why not simply have one INTERVAL type that covers all possibilities? It turns out that we can blame this state of affairs on the long-dead Roman Emperor Julius Caesar, who designed our calendar and determined most of our month lengths. The reason for having two INTERVAL types with a dividing line at the month level is that months are the only datetime component for which the length of time in question varies. Think about having an interval of 1 month and 30 days. How long is that, really? Is it less than two months? The same as two months? More than two months? If the one month is January, then 30 days gets you past February and into March, resulting in a 61-day interval that is a bit more than "two months" long. If the one month is February, then the interval is exactly two months (but only 59 or 60 days). If the one month is April, then the interval is slightly less than two months, for a total of 60 days. Rather than sort out and deal with all the complications differing month lengths pose for interval comparison, date arithmetic, and normalization of datetime values, the ANSI standard "breaks" the datetime model into two parts, year and month, and everything else. (For more, see C. J. Date's A Guide to the SQL Standard, 3rd Edition, Addison-Wesley, 1993). |
10.2 Date and Timestamp Conversions
Now that you understand Oracle's array of datetime datatypes, it's time to look at how you get dates into and out of datetime variables. Human-readable datetime values are character strings such as "March 5, 2002" and "10:30 AM", so this discussion centers around the conversion of datetime values from character strings to Oracle's internal representation, and vice versa.
PL/SQL validates and stores dates that fall from January 1, 4712 B.C. through December 31, 9999 A.D. (Oracle documentation indicates a maximum date of December 31, 4712; run the showdate.sql script, available on the O'Reilly site, to verify the range on your version.) If you enter a date without a time (many applications do not require the tracking of time, so PL/SQL lets you leave it off), the time portion of the value defaults to midnight (12:00:00 AM).
|
|
|
Oracle can interpret just about any date or time format you throw at it. Key to that flexibility is the concept of a date format model, which is a string of special characters that define a date's format to Oracle. Because they form the basis of date conversion, we talk about date format models first, and then show you how to use them to get dates into and out of PL/SQL datetime variables.
