Date/Time- Tables and Constraints
When capturing and reporting on date-related information, you should always use a DATE or TIMESTAMP data type and not VARCHAR2. Using the correct date-related data type allows you to perform accurate Oracle date calculations and aggregations and dependable sorting for reporting. If you use a VARCHAR2 for a field that contains date information, you are guaranteeing future reporting inconsistencies and needless conversion functions such as TO_DATE and TO_CHAR.
Oracle supports three date-related data types:
• DATE
• TIMESTAMP
• INTERVAL
The DATE data type contains a date component well as a time component that is granular to the second. By default, if you do not specify a time component when inserting data, then the time value defaults to midnight (0 hour at the 0 second). If you need to track time at a more granular level than the second, then use TIMESTAMP; otherwise, feel free to use DATE.
The TIMESTAMP data type contains a data component and a time component that is granular to fractions of a second. When you define a TIMESTAMP, you can specify the fractional second precision component. For instance, if you wanted five digits of fractional precision to the right of the decimal point, you would specify that as TIMESTAMP(5).
The maximum fractional precision is 9; the default is 6. If you specify 0 fractional precision, then you have the equivalent of the DATE data type.
The TIMESTAMP data type comes in two additional variations: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. These are time zone–aware data types, meaning that when the user selects the data, the time value is adjusted to the time zone of the user’s session.
Oracle also provides an INTERVAL data type. This is meant to store a duration, or interval, of time. There are two types: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Use the former when precision to the year and month is required. Use the latter when you need to store interval data granular to the day and second.
INTERVAL
When choosing an interval type, let your choice be driven by the level of granularity you desire in your results. For example, you can use INTERVAL DAY TO SECOND to store intervals several years in length; it is just that you will express such intervals in terms of days, perhaps of several hundreds of days. Recording the number of days represented by a year or month depends on which specific year and month are under discussion.
Similarly, if you need granularity in terms of months, you can’t back into the correct number of months based on the number of days. So, choose the type to match the granularity needed to your application.
RAW
The RAW data type allows you to store binary data in a column. This type of data is sometimes used for storing globally unique identifiers or small amounts of encrypted data. The RAW data type can have a maximum size of 32,767 bytes, and large amounts of binary data should be stored in a CLOB.
If you select data from a RAW column, SQL*Plus implicitly applies the built-in RAWTOHEX function to the data retrieved. The data is displayed in hexadecimal format, and this goes for inserting data into a RAW column. The built-in HEXTORAW function is implicitly applied.
This is important because if you create an index on a RAW column, the optimizer may ignore the index, as Oracle is implicitly applying functions where the RAW column is referenced in the SQL. A normal index may be of no use, whereas a function-based index using RAWTOHEX may result in a substantial performance improvement.
