A Date with DATE's Manipulating date and time information is an essential ingredient of many applications. Without proper handling of date and time information, it isn't possible to compute the interest on an investment or the mortgage payment on your house. Oracle database provides some exciting features for handling temporal data. Up to Oracle8i, all temporal data within an Oracle database were represented and manipulated using the DATE data type. Oracle9i introduced a gamut of new features, including new data types, and functions. In this article, we present some interesting ways to manipulate temporal data, using a case study. Case Study An auto insurance company maintains all its information (policy holders, claims, accidents, etc.) in a Oracle9i database. Let's assume, for the purpose of this case study, you work for this company and you are responsible for developing the software application for processing the claims for all the accidents. The accident related information is stored in the following ACCIDENTS table:
As and when accidents are reported rows are inserted to this table, and when the claim amount is settled, the CLAIM_AMOUNT column is updated. Your task involves reporting various information based on this data. In the rest of the article, I will walk you through the various tasks at hand, and the techniques you can use to accomplish effectively. Difference Between Accident Time and the Reporting Time One of the tasks you need to do is finding the time difference between the time of the accident, and the time the accident was reported. Should be fairly easy, isn't it? You can subtract the ACCIDENT_DATE_TIME from the REPORTED_DATE_TIME. However, subtracting one date from another returns the difference in number of days, such as:
The fractional number returned by the above query isn't much useful. Most of us are not used to understand ".176041667 Days" intuitively. For better understanding, we need to know the time difference in hours, minutes and seconds. To do this, we need to convert the result of date subtraction into an interval. Oracle9i introduced two interval data types - INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. A fractional day is best fitted into the INTERVAL DAY TO SECOND data type. Oracle9i also provides some useful function for data type conversion involving time intervals. You can use the NUMTODSINTERVAL function to convert the number returned by date subtraction into an interval of type INTERVAL DAY TO SECOND. This function takes a number and the unit of the number as input, and converts the number to an interval, as in the following example:
This output is better than ".176041667", but not that much clear. A more intuitive output would be hours, minutes and seconds shown in clearly and explicitly. Oracle provides an interesting function - EXTRACT - to extract any component of a DATE, TIMESTAMP or an INTERVAL. For example, you can extract the hour, minute and second components from the interval data, as shown in the following example:
This output indicating "4 Hours 13 Minutes and 30 Seconds" is much more clear. When will the car be ready? Well it was an accident. The accident was reported to the insurance company, and the car was towed to the repair center. Now, the policy holder wants to know when his car will be ready after repair? The repair center says "12 business days from today". Now how to find out the day 12 business days from today? We present a simple approach here (Other approaches are also possible. But this is one approach we know works). First create a pivot table to hold all the dates of the year.
Now populate this table with all the dates of this year. The following PL/SQL code does this.
You need to maintain another table containing the holidays the year, such as:
Now, to find the date that is 12 business days away from the date of accident, you can first create a function that returns the number of business days between two dates.
This function takes two dates as inputs and computes the number of business days between these two dates by counting days from the table DATES_2004 and excluding the days that are Saturday, Sunday or holidays. Now you can find out the date that is 12 business days away from the date of accident, by using the following SQL:
Print the Claim Check Once the car has been fixed, you receive a claim invoice from the repair center for US$ 2463.79. You update the accident record with this information: update ACCIDENTS set CLAIM_AMOUNT = 2463.79; Before you can consider your job on this claim is finished, you need to print a check and send it to the repair center. When you are printing a check, the amount needs to printed in number as well as in words. You can make innovative use of the date functions to convert a number into words. To convert a dollar amount into words, you first need to convert the number into a date, using the TO_DATE function and the 'J' date format. The 'J' date format is meant to express Julian dates. Then, you can convert the date into words using the 'Jsp' date format, which is meant to express dates in spelled words. However, you first need to break the number into two components - the whole number (dollars) and the fractional number (cents).
Conclusion Oracle provides rich features to handle temporal data. The date, time and interval data types help you represent various types of temporal data in the database efficiently. Moreover, the date and time functions help you manipulate the temporal data in your applications. [Further Reading] This article is not meant to describe all the date time features of Oracle9i. Please refer to the following texts for detailed discussion of the date/time features of the Oracle9i database.
[Author Bio] Sanjay Mishra is a recognized database expert, author and presenter. He has extensively worked in the areas of database architecture, database management, performance tuning, scalability, ETL, backup / recovery, parallel server and parallel execution. He has coauthored 4 Oracle books published by O'Reilly & Associates, including the recently published Mastering Oracle SQL, Second Edition. He has published several articles in reputed publications such as, Oracle Magazine, SELECT Journal and dbazine.com. Sanjay can be reached at smishra_tech@yahoo.com. |
Consulting Associates, Inc.