Team LiB   Previous Section   Next Section

6.4 Oracle9i New DATETIME Features

With Oracle9i, Oracle introduced features to enhance support for temporal data. These new features form the basis of Oracle's support for:

  • Time zones

  • Date and time data with fractional seconds

  • Date and time intervals

In this section we discuss these enhancements and their uses.

6.4.1 Time Zones

In the Internet economy, business is carried out across geographical boundaries and time zones. Oracle facilitates global e-business through its support for time zones. With Oracle9i, a database and a session can now be associated with time zones. Having database and session time zones enables users in geographically distant regions to exchange temporal data with the database without having to bother about the time differences between their location and the server's location.

6.4.1.1 Database time zone

We can set the time zone of a database when we create the database. After creating the database, we can change the time zone using the ALTER DATABASE command. Both CREATE DATABASE and ALTER DATABASE take an optional SET TIME_ZONE clause. Specify a time zone in one of the two ways:

  • By specifying a displacement from the Coordinated Universal Time (UTC).

  • By specifying a time zone region.

The displacement from the UTC is specified in hours and minutes with a + or - sign. Every time zone region is given a region name. For example, EST is the region name for Eastern Standard Time. We can also use such a region name to set the time zone of a database.

UTC was formerly known as Greenwich Mean Time (GMT).

The syntax of SET TIME_ZONE clause is: 

SET TIME_ZONE = '+ | - HH:MI' | 'time_zone_region'

The following examples use this clause to set the time zone of a database: 

CREATE DATABASE ... SET TIME_ZONE = '-05:00';

ALTER DATABASE ... SET TIME_ZONE = 'EST';

Both of the previous examples set the time zone to Eastern Standard Time. The first example uses a displacement (-05:00) from the UTC. The second example uses the region name (EST).

If we do not explicitly set the database time zone, Oracle defaults to the operating system time zone. If the operating system time zone is not a valid Oracle time zone, UTC is used as the default time zone.

6.4.1.2 Session time zone

Each session can have a time zone as well. The time zone of a session can be set by using the ALTER SESSION SET TIME_ZONE clause. The syntax for the SET TIME_ZONE clause in the ALTER SESSION statement is the same as that in the CREATE DATABASE and ALTER DATABASE statements.

The following example shows two ways to set the time zone of a session to Pacific Standard Time: 

ALTER SESSION SET TIME_ZONE = '-08:00';

ALTER SESSION SET TIME_ZONE = 'PST';

To set the session time zone to the local operating system time zone (e.g., the time zone of a PC initiating a remote user session), we can use the LOCAL keyword in the SET TIME_ZONE clause, as in the following example: 

ALTER SESSION SET TIME_ZONE = LOCAL;

To set the session time zone to the database time zone, use the DBTIMEZONE keyword in the SET TIME_ZONE clause, as in the following example: 

ALTER SESSION SET TIME_ZONE = DBTIMEZONE;

We will talk more about the DBTIMEZONE keyword later.

If the session time zone has not been explicitly set, Oracle defaults to the local operating system time zone. If the operating system time zone is not a valid Oracle time zone, UTC is used as the default time zone.

6.4.2 Date and Time Data with Fractional Seconds

To provide support for the fractional seconds along with date and time data, Oracle9i introduced the following new temporal datatypes:

  • TIMESTAMP

  • TIMESTAMP WITH TIMEZONE

  • TIMESTAMP WITH LOCAL TIMEZONE

These datatypes provide ways to handle time values resolved down to the fraction of a second, and in different time zones. The following sections discuss these datatypes.

6.4.2.1 TIMESTAMP

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

TIMESTAMP [ (precision for fractional seconds) ]

The precision for the fractional seconds is specified in the parentheses. We can specify integer values between 0 and 9 for fractional precision. A precision of 9 means that we can have 9 digits to the right of the decimal. As you can see from the syntax notation, the precision field is optional. If we don't specify the precision, it defaults to 6; i.e., TIMESTAMP is the same as TIMESTAMP(6).

The following example creates a table with a TIMESTAMP column:

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)

Note that even though we specified just TIMESTAMP as the datatype of the column TRANSACTION_TIMESTAMP, it appears as TIMESTAMP(6) when we describe the table. To insert data into this column, we can use a TIMESTAMP literal in the following format: 

TIMESTAMP 'YYYY-MM-DD HH:MI:SS.xxxxxxxxx'

A TIMESTAMP literal can have up to 9 digits of fractional seconds. The fractional part is optional, but the date and time elements are mandatory and must be provided in the specified format. Here's an example in which data is inserted into a table with a TIMESTAMP column: 

INSERT INTO TRANSACTION 
VALUES (1001, TIMESTAMP '1998-12-31 08:23:46.368', 'OPEN');

1 row created.

SELECT * FROM TRANSACTION;

TRANSACTION_ID TRANSACTION_TIMESTAMP             STATUS
-------------- --------------------------------- ---------
          1001 31-DEC-98 08.23.46.368000 AM      OPEN
6.4.2.2 TIMESTAMP WITH TIME ZONE

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

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

The precision for fractional seconds is the same as that for the TIMESTAMP datatype. The time zone displacement is the time difference in hours and minutes, between the local time and GMT (Greenwich Mean Time, also known as Coordinated Universal Time or UTC). We supply such displacements when we 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 the user.

The following example creates a table with a TIMESTAMP column:

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)

To insert data into the TRANSACTION_TIMESTAMP column, we can use a TIMESTAMP literal with a time zone displacement, which takes the following form: 

TIMESTAMP 'YYYY-MM-DD HH:MI:SS.xxxxxxxxx {+|-} HH:MI'

Here is an example showing how to insert data into a table with a TIMESTAMP WITH TIME ZONE column: 

INSERT INTO TRANSACTION_TIME_ZONE 
VALUES (1002, TIMESTAMP '1998-12-31 08:23:46.368 -10:30', 'NEW');

1 row created.

SELECT * FROM TRANSACTION_TIME_ZONE;

TRANSACTION_ID TRANSACTION_TIMESTAMP               STATUS
-------------- ----------------------------------- -------
          1002 31-DEC-98 08.23.46.368 AM -10:30    NEW

Note that even though the datatype is called TIMESTAMP WITH TIME ZONE, the literal still uses just the TIMESTAMP keyword. Also note that the literal specifies a date/time displacement using the {+|-}hour:minute notation.

If we are specifying a time zone displacement with a TIMESTAMP literal, we must specify the sign of the displacement (i.e., + or -). The range of the hour in a time zone displacement is -12 through +13, and the range of a minute is 0 through 59. A displacement outside these ranges will generate an error.

When we don't specify a time zone displacement, the displacement is not assumed to be zero; instead, the timestamp is assumed to be in the local time zone, and the value of the displacement defaults to the displacement of the local time zone. In the following example, the input data doesn't specify any time zone. Therefore, Oracle assumes the timestamp to be in the local time zone, and stores the local time zone along with the timestamp in the database column. 

INSERT INTO TRANSACTION_TIME_ZONE 
VALUES (1003, TIMESTAMP '1999-12-31 08:23:46.368', 'NEW');

1 row created.

SELECT * FROM TRANSACTION_TIME_ZONE;

TRANSACTION_ID TRANSACTION_TIMESTAMP                 STATUS
-------------- ------------------------------------- -------
          1003 31-DEC-99 08.23.46.368 AM -05:00      NEW
6.4.2.3 TIMESTAMP WITH LOCAL TIME ZONE

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

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

The precision for the fractional seconds is the same as that in the TIMESTAMP datatype. 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 date to the database time zone, the input time is converted to a time in the database time zone.

  • 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 TIMESTAMP column:

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)

There is no literal for the TIMESTAMP WITH LOCAL TIME ZONE datatype. To insert data into this column, we use a TIMESTAMP literal. For example: 

INSERT INTO TRANSACTION_LOCAL_TIME_ZONE VALUES (
2001, TIMESTAMP '1998-12-31 10:00:00 -3:00', 'NEW');

1 row created.

SELECT * FROM TRANSACTION_LOCAL_TIME_ZONE;

TRANSACTION_ID TRANSACTION_TIMESTAMP      STATUS
-------------- -------------------------- -------
          2001 31-DEC-98 08.00.00 AM      NEW

Note that the time zone displacement is not stored in the database. The data is stored in the database in the normalized form with respect to the database time zone. What this means is that the input time is converted into a time in the database time zone before being storing in the database. The database time zone is -5:00. Therefore, -3:00 is 2 hours ahead of the database time zone, and 10:00:00 - 3:00 is the same as 08:00:00 - 5:00. Since the time is normalized with respect to the database time zone, the displacement does not need to be stored in the database.

6.4.3 Date and Time Intervals

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, we all used the NUMBER datatype to represent such data, and the logic needed to deal with interval data had to be coded at the application level. Oracle9i provides two new datatypes to handle interval data:

  • INTERVAL YEAR TO MONTH

  • INTERVAL DAY TO SECOND

The following sections discuss the use of these datatypes.

6.4.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 datatype is specified as: 

INTERVAL YEAR [ (precision for year) ] TO MONTH

The precision 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 INTERVAL YEAR TO MONTH datatype: 

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 (NUM-TO-YM-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, and is discussed later in this chapter in Section 6.5.3. 

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. Note that the fractional part of a month is ignored. An INTERVAL YEAR TO MONTH value is only in terms of years and months, not fractional months. Any fractional values of a month are truncated.

6.4.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 datatype 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 second. It can range from 0 to 9, and the default value is 6.

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

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

To insert into a database column of type INTERVAL DAY TO SECOND, we used a function NUMTODSINTERVAL (NUM-TO-DS-INTERVAL). This function converts a NUMBER value into a value of type INTERVAL DAY TO SECOND, and is discussed in Section 6.5.3 later in this chapter.

    Team LiB   Previous Section   Next Section