| |
Using Datetime and Interval Datatypes in a Global Environment
Module Objectives
Purpose
This module describes how to use datetime and interval
datatypes when operating within the different time zones of a global environment.
Objectives
After completing this module, you should be able to:
Prerequisites
Before starting this module, you should have completed the following:
Reference Material
The following is useful reference material if you want additional information
on the topics in this module:
 |
Documentation: Oracle9i
Globalization Support Guide
|
Overview
When database applications are operating in a global environment, the
notion of time has to be specified with a particular time zone. In this
lesson, you will get and set time zone information for a database and
a session and then explain the datetime and interval datatypes. Finally,
with knowledge of datetime and interval datatypes in general, you will
examine more specific topics of daylight saving time and NLS parameters
for datetime datatypes.
Time Zone Parameters for Databases and Sessions
Database Time Zone Parameter
There are two ways to create a Database with a specific time zone:
| 1. |
By specifying a displacement from UTC (Coordinated Universal Time-formerly
Greenwich Mean Time). The following example sets the time zone of
the database to Pacific Standard time (8 hours earlier than UTC):
CREATE DATABASE ... SET TIME_ZONE = '-08:00 ';
|
| 2. |
By specifying a time zone region. To see a listing of valid region
names, query the V$TIMEZONE_NAMES dynamic performance view. The following
example also sets the time zone of the database to Pacific Standard
time in the U.S.:
CREATE DATABASE ... SET TIME_ZONE = 'PST ';
Note: The database time zone is only relevant for TIMESTAMP
WITH LOCAL TIME ZONE columns. Oracle normalizes all TIMESTAMP WITH
LOCAL TIME ZONE data to the time zone of the database when the data
is stored on disk. If you do not specify the SET TIME_ZONE clause,
Oracle uses the operating systems time zone of the server.
|
| 3. |
After the database has been created, the time zone can be changed
with the ALTER DATABASE SET TIME_ZONE statement and then shutdown
and startup the database. The change will not take effect until
the database is bounced. The following example sets the time zone
of the database to London time:
ALTER DATABASE SET TIME_ZONE = 'Europe/London ';
|
| 4. |
To find out the time zone of a created database, use the DBtime
zone built-in SQL function as shown in the following SQL*Plus example:
|
Session Time Zone Parameter
In a similar fashion, using the following examples, the Time Zone parameter
of a user session can also be set to:
| O/S Local Time Zone |
ALTER SESSION SET TIME_ZONE = local; |
| Database Time Zone |
ALTER SESSION SET TIME_ZONE = dbtime zone; |
| An absolute offset |
ALTER SESSION SET TIME_ZONE = '-05:00'; |
| A named region |
ALTER SESSION SET TIME_ZONE = 'America/New_York'; |
Datetime Datatypes
To specify a time instance, we use datetime datatypes such as DATE, TIMESTAMP,
TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. Values of
datetime datatypes are called "datetimes".
Depending on the time zone specification, the following timestamp datatypes
exist:
|
TIMESTAMP -- Year, month, and day values
of date, as well as hour,minute, and second values of time, where
significant fractional_seconds_precision is the number of digits
in the fractional part of the SECOND datetime field. Accepted values
of significant fractional_seconds_precision are 0 to 9. The default
is 6.
|
|
TIMESTAMP WITH TIME ZONE -- All values of
TIMESTAMP as well as time zone displacement value, where significant
fractional_seconds_precision is the number of digits in the fractional
part of the SECOND datetime field. Accepted values are 0 to 9. The
default is 6. |
|
TIMESTAMP WITH LOCAL TIME ZONE -- All values
of TIMESTAMP, with the following exceptions: Data is normalized
to the database time zone when it is stored in the database; and
when the data is retrieved, users see the data in the session time
zone.
|
TIMESTAMP Datatype
The TIMESTAMP datatype stores date and time including fractional seconds.
Specify the TIMESTAMP datatype as follows:
TIMESTAMP [ (fractional_seconds_precision)]
where significant fractional_seconds_precision optionally specifies the
number of digits in the fractional part of the SECOND datetime field and
can be a number in the range 0 to 9. The default is 6. For example, you
specify TIMESTAMP as a literal as follows:
TIMESTAMP'1997-01-31 09:26:50.124'
TIMESTAMP WITH TIME ZONE Datatype
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time
zone displacement or time zone region in its value. The time zone displacement
is the difference (in hours and minutes) between local time and UTC (Coordinated
Universal Time formerly Greenwich Mean Time). Specify the TIMESTAMP WITH
TIME ZONE datatype as follows:
TIMESTAMP [ (fractional_seconds_precision) ] WITH TIME
ZONE
where fractional_seconds_precision optionally specifies the number of
digits in the fractional part of the SECOND datetime field and can be
a number in the range 0 to 9. The default is 6. For example, you specify
TIMESTAMP WITH TIME ZONE as a literal as follows:
TIMESTAMP '1997-01-31 09:26:56.66 America/New_York'
Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent
the same instant in UTC, regardless of the TIME ZONE offsets stored in the
data. For example,
TIMESTAMP '1999-04-15 8:00:00 -8:00'
is the same as
TIMESTAMP '1999-04-15 11:00:00 -5:00'
That is, 8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern
Standard Time on April 15, 1999.
TIMESTAMP WITH LOCAL TIME ZONE Datatype
TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP. It differs
from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized
to the database time zone, and the time zone displacement is not stored
as part of the column data. When users retrieve the data, Oracle returns
it in the users' local session time zone. The time zone displacement is
the difference (in hours and minutes) between local time and UTC (Coordinated
Universal Time formerly Greenwich Mean Time). Specify the TIMESTAMP WITH
LOCAL TIME ZONE datatype as follows:
TIMESTAMP [ (fractional_seconds_precision) ] WITH LOCAL
TIME ZONE
where fractional_seconds_precision optionally specifies the number of digits
in the fractional part of the SECOND datetime field and can be a number
in the range 0 to 9. The default is 6. Unlike TIMESTAMP WITH TIME ZONE,
you can specify columns of type TIMESTAMP WITH LOCAL TIME ZONE as part of
a primary or unique key. Though there is no literal for TIMESTAMP WITH LOCAL
TIME ZONE, both TIMESTAMP and TIMESTAMP WITH time zone literal can be used
to assign a datetime value to TIMESTAMP WITH LOCAL TIME ZONE column or variable.
INSERT INTO orders VALUES ( timestamp '2000-10-28 23:24:24
US/Eastern');
Interval Datatypes
To specify a time duration, we use the interval datatypes. The interval
datatypes are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Values
of interval datatypes are refered as "intervals".
|
INTERVAL YEAR TO MONTH Stores a period
of time in years and months, where year_precision is the number
of digits in the YEAR datetime field. Accepted values are 0 to 9.
The default is 6.
|
|
INTERVAL DAY TO SECOND Stores a period
of time in days, hours, minutes, and seconds, where day_precision
is the maximum number of digits in the DAY datetime field (accepted
values are 0 to 9; the default is 2), and fractional_seconds_precision
is the number of digits in the fractional part of the SECOND field.
Accepted values are 0 to 9. The default is 6. |
INTERVAL YEAR TO MONTH Datatype
INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH
datetime fields. Specify INTERVAL YEAR TO MONTH as follows:
INTERVAL YEAR [(year_precision)] TO MONTH
where year_precision is the number of digits in the YEAR datetime field.
The default value of year_precision is 2.
Restriction: The leading field must be more significant than
the trailing field. For example,
INTERVAL '0-1' MONTH TO YEAR
is not valid. The following INTERVAL YEAR TO MONTH literal indicates
an interval of 123 years, 2 months:
INTERVAL '123-2' YEAR(3) TO MONTH
Examples of the other forms of the literal follow, including some abbreviated
versions:
INTERVAL '123-2' YEAR(3) TO MONTH
indicates an interval of 123 years, 2 months. You must specify the
leading field precision if it is greater than the default of 2 digits.
INTERVAL '123' YEAR(3)
indicates an interval of 123 years 0 months.
INTERVAL '300' MONTH(3) indicates an interval of 300 months.
The Oracle9i Database supports only two interval datatypes: Interval
Year to Month and Interval Day to Second -- column type, PL/SQL argument,
variable and return type must be one of the two. However, for interval
literals the system recognizes other ANSI interval types such as INTERVAL
'2' YEAR or INTERVAL '10' HOUR. In these cases each interval is converted
to one of the two supported types.
INTERVAL DAY TO SECOND Datatype
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision) Stores
a period of time in days, hours, minutes, and seconds, where day_precision
is the maximum number of digits in the DAY datetime field (accepted values
are 0 to 9; the default is 2), and fractional_seconds_precision is the number
of digits in the fractional part of the SECOND field. Accepted values are
0 to 9. The default is 6.
An Example Using Datetime Datatype
From a SQL*Plus session, execute the script datetime.sql
The datetime.sql script contains the following statements:
COL c1 FORMAT A30
COL c2 FORMAT A48
COL c3 FORMAT A30
COL c4 FORMAT A48
COL c5 FORMAT A30
COL c1 HEADING TIMESTAMP
COL c2 HEADING "TIMESTAMP WITH TIME ZONE"
COL c3 HEADING "TIMESTAMP WITH LOCAL TIME ZONE"
COL c4 HEADING "INTERVAL YEAR TO MONTH"
COL c5 HEADING "INTERVAL DAY TO SECOND"
SET ECHO OFF
CONNECT OE
CREATE TABLE dattime (
c1 TIMESTAMP,
c2 TIMESTAMP WITH TIME ZONE,
c3 TIMESTAMP WITH LOCAL TIME ZONE,
c4 INTERVAL YEAR TO MONTH,
c5 INTERVAL DAY TO SECOND);
INSERT INTO dattime
VALUES ( TIMESTAMP'1980-1-12 15:13:23.33',
TIMESTAMP'2000-10-28 11:26:38 AMERICA/LOS_ANGELES',
TIMESTAMP'1985-3-1 1:11:11.11' AT TIME ZONE DBTIMEZONE,
INTERVAl '1-2' YEAR TO MONTH,
INTERVAL '90 00:00:00' DAY TO SECOND);
INSERT INTO dattime
VALUES (TO_TIMESTAMP('08-11-83 3:43:55.49','DD-MM-RR HH24:MI:SSXFF'),
TO_TIMESTAMP_TZ('2-4-58 14:2:56.18+8:00','DD-MM-RR HH24:MI:SSXFFTZH:TZM'),
TO_TIMESTAMP('12-8-38 1:2:56.1','DD-MM-RR HH24:MI:SSXFF'),
TO_YMINTERVAL('02-03'),
TO_DSINTERVAL('65 10:00:00'));
SELECT C1 FROM dattime;
SELECT C2 FROM dattime;
SELECT C3 FROM dattime;
SELECT C4 FROM dattime;
SELECT C5 FROM dattime;
DROP TABLE dattime;
Support for Daylight Saving Time
Daylight Saving Time (DST) is observed in the United States and its territories
except Hawaii, American Samoa, Guam, Puerto Rico, the Virgin Islands,
the Eastern Time Zone portion of the State of Indiana, and most of Arizona
(with the exception of the Navajo Indian Reservation in Arizona). Oracle
automatically determines, for any given time zone region in the United States,
whether daylight saving time is in effect and returns local time values
based accordingly. The datetime value is sufficient for Oracle to determine
whether daylight saving time is in effect for a given region in all cases
except boundary cases.
Daylight Saving Time Boundaries
A boundary case occurs during the period when daylight saving time goes
into or comes out of effect.
|
First Sunday in April |
| |
 |
Time jumps from 01:59:59 AM to 03:00:00 AM |
| |
|
Values from 02:00:00 AM to 02:59:59 AM are not valid |
|
Last Sunday in October |
| |
|
Time jumps from 02:00:00 AM to 01:00:01 AM |
| |
|
Values from 01:00:01 AM to 02:00:00 AM are ambiguous because they
are visited twice |
For example, in the US-Eastern region, when daylight saving time goes
into effect, the time changes from 01:59:59 a.m. to 3:00:00 a.m. The one
hour interval between 02:00:00 and 02:59:59 a.m. does not exist. When
daylight saving time goes out of effect, the time changes from 02:00:00
a.m. back to 01:00:01 a.m., and the one-hour interval between 01:00:01
and 02:00:00 a.m. is repeated.
Daylight Saving Time and Datetime Datatypes
|
TIMESTAMP datatype does not accept TIME_ZONE values
and does not calculate DST |
|
TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME
ZONE datatypes |
| |
|
If a TIME_ZONE REGION is associated with the datetime value then
the Oracle server knows the Daylight Saving Time rules for the region
and they will be considered in any operation with this datetime value.
|
| |
|
Daylight Saving Time is not calculated for regions that do not use
DST |
Timestamp with Time Zone
Two timestamps are inserted into columns ORDERDATE1 and ORDERDATE2 of table
ORDERS. Column ORDERDATE1 is datatype TIMESTAMP and does not accept TIME_ZONE
REGION, column ORDERDATE2 is datatype TIMESTAMP WITH TIME ZONE. The SELECT
statement shows the effects of adding 8 hours to the values in the columns
-- the time period crosses one of the DST boundaries. The hour difference
between the two values demonstrates the calculation of DST.
Timestamp with Local Time Zone
The TIMESTAMP WITH LOCAL TIME ZONE datatype accepts the value of TIME_ZONE
taken from the session environment or a datetime value with TIME_ZONE inserted
as a TIMESTAMP literal.
ERROR_ON_OVERLAP_TIME Session Parameter
The ERROR_ON_OVERLAP_TIME is a session parameter for daylight saving time
that notifies the system to issue an error when it encounters a datetime
that falls in the overlapped period, and no time zone abbreviation was specified
to distinguish the period. For example, in the year 2000, Daylight Saving
Time ends on Oct. 29 at 02:00:01 a.m. The overlapped periods are
10/29/2000 01:00:01 a.m. to 10/29/2000 02:00:00 a.m. (EDT)
10/29/2000 01:00:01 a.m. to 10/29/2000 02:00:00 a.m (EST)
If you input a datetime string which falls in one of these two periods,
you need to specify the time zone abbreviation (e.g., EDT or EST) in the
input string for the system to determine the period. Without this time zone
abbreviation, the system will do one of the following:
1. If the parameter ERROR_ON_OVERLAP_TIME is FALSE then it assumes
the input time is standard time (e.g., EST)
2. Otherwise, issue an error.
Invalid Time Period
The time period from 02:00:00 AM to 02:59:59 AM does not exist during the
April boundary. Inserting a nonexisting value during this time results in
an error.
Inserting into Ambiguous Time Period
When inserting values during the October boundary an error occurs only is
the parameter ERROR_ON_OVERLAP_TIME is set to true and a TZD (time zone
DST, such as EST) is not specified.
Since the datatype TIMESTAMP WITH LOCAL TIME ZONE can not accept a time
zone region or time zone DST in its format the TIMESTAMP literal must
be used to insert the value.
An Example Using Daylight Savings Time
From a SQL*Plus session, execute the script daylightsavings.sql
The first part of this example demonstrates DST calculation with TIME_ZONE
region.
The second part of this example demonstrates an invalid timestamp when
DST goes into effect.
The third part of this example demonstrates an invalid timestamp when
DST expires in October.
The daylightsavings.sql script contains the following statements:
SET ECHO OFF
CONNECT OE
PROMPT Calculate Daylight Saving Time (DST) when the session TIME_ZONE is a region
PROMPT
SET ECHO ON
UPDATE orders
SET order_date = TIMESTAMP '2000-4-1 23:24:54 AMERICA/LOS_ANGELES'
WHERE order_id = 2457;
ALTER SESSION SET TIME_ZONE='AMERICA/LOS_ANGELES';
SELECT order_date FROM orders WHERE order_id = 2457;
SELECT order_date + INTERVAL '8' HOUR FROM orders
WHERE order_id = 2457;
SET ECHO OFF
PROMPT
PAUSE Press enter to continue ...
PROMPT
PROMPT The time period from 02:00:00 AM to 02:59:59 AM does not exist
PROMPT during APRIL boundary
PROMPT
SET ECHO ON
UPDATE orders
SET order_date = TIMESTAMP'2000-04-02 02:30:30 AMERICA/LOS_ANGELES'
WHERE order_id = 2457;
SET ECHO OFF
PROMPT
PROMPT
PAUSE Press enter to continue ...
PROMPT
PROMPT The time period from 01:00:01 AM to 02:00:00 AM is repeated during
PROMPT OCTOBER boundary
PROMPT
SET ECHO ON
ALTER SESSION SET ERROR_ON_OVERLAP_TIME = TRUE;
UPDATE orders
SET order_date = TIMESTAMP '2000-10-29 01:00:01 AMERICA/LOS_ANGELES'
WHERE order_id = 2457;
NLS Parameters for Datetime Datatypes
There are two NLS parameters that we can set to define default timestamp
and time zone formats.
NLS_TIMESTAMP_FORMAT
This parameter defines the default timestamp format to use with the TO_CHAR
and TO_TIMESTAMP functions. The value must be surrounded by single quotation
marks. For example:
NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'
|
You can specify the value of this parameter for the database by
setting it in the initialization file. |
|
You can specify its value for a client as a client environment variable.
|
|
You can alter the value of this parameter by changing its value
in the initialization parameter file and then restarting the instance.
You can alter the value during a session using the ALTER SESSION SET
clause. |
NLS_TIMESTAMP_TZ_FORMAT
This parameter defines the default timestamp with time zone format to use
with the TO_CHAR and TO_TIMESTAMP_TZ functions. The value must be surrounded
by single quotation marks. For example:
NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
|
You can specify the value of this parameter for the database by
setting it in the initialization file. |
|
You can specify its value for a client as a client environment variable.
|
|
You can alter the value of this parameter by changing its value
in the initialization parameter file and then restarting the instance.
You can alter the value during a session using the ALTER SESSION SET
clause. |
Module Summary
In this module, you should have learned how to:
Copyright © 2002 Oracle Corporation. All Rights Reserved.
Close Window
|
Bookmark
