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:
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:
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:
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.
| 
No comments:
Post a Comment