6.2 Temporal Data Types in Oracle

   

Oracle provides the following categories of data types to represent temporal data inside an Oracle database:

  • The DATE data type

  • The TIMESTAMP data types:

    • TIMESTAMP

    • TIMESTAMP WITH TIME ZONE

    • TIMESTAMP WITH LOCAL TIME ZONE

  • The INTERVAL data types:

    • INTERVAL YEAR TO MONTH

    • INTERVAL DAY TO SECOND

Up to release Oracle8i, Oracle had only one temporal data type: DATE, which held date as well as time information. Oracle9i introduced several new data types three TIMESTAMP data types to hold time data with fractional seconds, and two INTERVAL data types to hold time intervals. The following sections discuss all these data types in detail.

6.2.1 The DATE Data Type

Oracle's DATE data type holds date as well as time information. Regardless of the date format you use for display purposes, Oracle stores dates internally in one standard format. Internal to the database, a date is a fixed-length, 7-byte field. The seven bytes represent the following pieces of information:

  • The Century

  • The Year

  • The Month

  • The Day

  • The Hour

  • The Minute

  • The Second

Even though the data type is called a DATE, it also stores the time. You choose the components to display (the date, the time, the date and the time, etc.) when you retrieve a DATE value from the database. Or, if you are putting a DATE value into a program (e.g., a Java program) you might choose to extract the components of interest after transferring the entire date/time value to that program.

6.2.2 The TIMESTAMP Data Types

To provide support for fractional seconds along with date and time data, and also to provide support for time zones, Oracle9i introduced the following temporal data types:

  • TIMESTAMP

  • TIMESTAMP WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

6.2.2.1 TIMESTAMP

The TIMESTAMP data type extends the DATE type to support more precise time values. A TIMESTAMP includes all the components of the DATE data type (century, year, month, day, hour, minute, second) plus fractional seconds. A TIMESTAMP data type is specified as:

TIMESTAMP [ (precision for fractional seconds) ]

The precision for fractional seconds is optional and is specified in parentheses. You can specify integer values between 0 and 9 for fractional precision. A precision of 9 means that you can have 9 digits to the right of the decimal i.e., up to nanoseconds precision. If you don't specify the precision, it defaults to 6 (microseconds precision) i.e., TIMESTAMP is the same as TIMESTAMP(6).

The following example creates a table with a column of type TIMESTAMP:

CREATE TABLE transaction ( transaction_id NUMBER(10), transaction_timestamp TIMESTAMP, status VARCHAR2(12)); Table created. DESC transaction  Name                        Null?    Type  --------------------------- -------- ---------------  TRANSACTION_ID                       NUMBER(10)  TRANSACTION_TIMESTAMP                TIMESTAMP(6)  STATUS                               VARCHAR2(12)

Since we don't specify a precision in this example for the column transaction_timestamp, Oracle uses the default precision for the TIMESTAMP data type, and it appears as TIMESTAMP(6) when we describe the table.

6.2.2.2 TIMESTAMP WITH TIME ZONE

The TIMESTAMP WITH TIME ZONE data type further extends the TIMESTAMP type to include a time zone displacement. A TIMESTAMP WITH TIME ZONE data type is specified as:

TIMESTAMP [ (precision for fractional seconds) ] WITH TIME ZONE

The precision for fractional seconds is the same as that for the TIMESTAMP data type. The time zone displacement is the time difference in hours and minutes between the local time and UTC. You supply such displacements when you store values in the column, and the database retains the displacements so that those values can later be translated into any target time zone desired by your system's users.

The following example creates a table with a column of type TIMESTAMP WITH TIME ZONE:

CREATE TABLE transaction_time_zone ( transaction_id NUMBER(10), transaction_timestamp TIMESTAMP(3) WITH TIME ZONE, status VARCHAR2(12)); Table created. DESC transaction_time_zone  Name                            Null?    Type  ------------------------------- -------- ------------------------  TRANSACTION_ID                           NUMBER(10)  TRANSACTION_TIMESTAMP                    TIMESTAMP(3) WITH TIME ZONE  STATUS                                   VARCHAR2(12)

6.2.2.3 TIMESTAMP WITH LOCAL TIME ZONE

The TIMESTAMP WITH LOCAL TIME ZONE data type is a variant of the TIMESTAMP WITH TIME ZONE data type. A TIMESTAMP WITH LOCAL TIME ZONE data type is specified as:

TIMESTAMP [ (precision for fractional seconds) ] WITH LOCAL TIME ZONE

The precision for fractional seconds is the same as that in the TIMESTAMP data type. TIMESTAMP WITH LOCAL TIME ZONE differs from TIMESTAMP WITH TIME ZONE in the following ways:

  • The time zone displacement is not stored as part of the column data.

  • The data stored in the database is normalized to the time zone of the database. To normalize an input value to the database time zone, the input time is converted to a time in the database time zone. The original time zone is not preserved.

  • When the data is retrieved, Oracle returns the data in the time zone of the user session.

The following example creates a table with a column of type TIMESTAMP WITH LOCAL TIME ZONE:

CREATE TABLE transaction_local_time_zone ( transaction_id NUMBER(10), transaction_timestamp TIMESTAMP(3) WITH LOCAL TIME ZONE, status VARCHAR2(12)); Table created. DESC transaction_local_time_zone  Name                     Null?    Type  ------------------------ -------- ------------------------  TRANSACTION_ID                    NUMBER(10)  TRANSACTION_TIMESTAMP             TIMESTAMP(3) WITH LOCAL TIME ZONE  STATUS                            VARCHAR2(12)

6.2.3 The INTERVAL Data Types

Date and time interval data are an integral part of our day-to-day life. Common examples of interval data are the age of a person, the maturity period of a bond or certificate of deposit, and the warranty period of your car. Prior to Oracle9i Database, we all used the NUMBER data type to represent such data, and the logic needed to deal with interval data had to be coded at the application level. Oracle9i Database introduced two new data types to handle interval data:

  • INTERVAL YEAR TO MONTH

  • INTERVAL DAY TO SECOND

The following sections discuss the use of these data types.

6.2.3.1 INTERVAL YEAR TO MONTH

The INTERVAL YEAR TO MONTH type stores a period of time expressed as a number of years and months. An INTERVAL YEAR TO MONTH data type is specified as:

INTERVAL YEAR [ (precision for year) ] TO MONTH

The precision for year specifies the number of digits in the year field. The precision can range from 0 to 9, and the default value is 2. The default precision of two allows for a maximum interval of 99 years, 11 months.

The following example creates a table with a column of type INTERVAL YEAR TO MONTH:

CREATE TABLE event_history ( event_id NUMBER(10), event_duration INTERVAL YEAR TO MONTH); Table created. DESC event_history   Name                         Null?    Type  ---------------------------- -------- ------------------------  EVENT_ID                              NUMBER(10)  EVENT_DURATION                        INTERVAL YEAR(2) TO MONTH

The next example uses the NUMTOYMINTERVAL (NUMBER-TO-YEAR-MONTH INTERVAL) function to insert data into a database column of type INTERVAL YEAR TO MONTH. This function converts a NUMBER value into a value of type INTERVAL YEAR TO MONTH, using the units specified by the second argument:

INSERT INTO event_history VALUES (5001, NUMTOYMINTERVAL(2,'YEAR')); 1 row created. INSERT INTO event_history VALUES (5002, NUMTOYMINTERVAL(2.5,'MONTH')); 1 row created. SELECT * FROM event_history;   EVENT_ID EVENT_DURATION ---------- ------------------       5001 +02-00       5002 +00-02

The second argument to the NUMTOYMINTERVAL function specifies the unit of the first argument. Therefore, in the first example, the number 2 is treated as 2 years, and in the second example, the number 2.5 is treated as 2 months. Any fractional part of a month is ignored. An INTERVAL YEAR TO MONTH value is only in terms of years and months, not fractional months.

6.2.3.2 INTERVAL DAY TO SECOND

The INTERVAL DAY TO SECOND type stores a period of time expressed as a number of days, hours, minutes, seconds, and fractions of a second. An INTERVAL DAY TO SECOND data type is specified as:

INTERVAL DAY [(precision for day)]  TO SECOND [(precision for fractional seconds)]

The precision for day specifies the number of digits in the day field. This precision can range from 0 to 9, and the default value is 2. The precision for fractional seconds is the number of digits in the fractional part of a second. It can range from 0 to 9, and the default value is 6.

The following example creates a table with an INTERVAL DAY TO SECOND column:

CREATE TABLE batch_job_history ( job_id NUMBER(6), job_duration INTERVAL DAY(3) TO SECOND(6)); Table created. DESC batch_job_history  Name                    Null?    Type  ----------------------- -------- -----------------------------  JOB_ID                           NUMBER(6)  JOB_DURATION                     INTERVAL DAY(3) TO SECOND(6)

Here's how to insert data into a table with an INTERVAL DAY TO SECOND column:

INSERT INTO batch_job_history VALUES (6001, NUMTODSINTERVAL(5369.2589,'SECOND')); 1 row created. SELECT * FROM batch_job_history;     JOB_ID JOB_DURATION ---------- ----------------------------------------       6001 +00 01:29:29.258900

For the INSERT in this example we used the function NUMTODSINTERVAL (NUMBER-TO-DAY-SECOND-INTERVAL). This function converts a NUMBER value into a value of type INTERVAL DAY TO SECOND, using the units specified in the second argument. It's analogous to NUMTOYMINTERVAL discussed in the previous section.



Mastering Oracle SQL
Mastering Oracle SQL, 2nd Edition
ISBN: 0596006322
EAN: 2147483647
Year: 2003
Pages: 154

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net