| Oracle® Database Globalization Support Guide 10g Release 1 (10.1) Part Number B10749-01 |
|
|
View PDF |
| Oracle® Database Globalization Support Guide 10g Release 1 (10.1) Part Number B10749-01 |
|
|
View PDF |
This chapter includes the following topics:
Businesses conduct transactions across time zones. Oracle's datetime and interval datatypes and time zone support make it possible to store consistent information about the time of events and transactions.
|
Note: This chapter describes Oracle datetime and interval datatypes. It does not attempt to describe ANSI datatypes or other kinds of datatypes except when noted. |
The datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. Values of datetime datatypes are sometimes called datetimes.
The interval datatypes are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Values of interval datatypes are sometimes called intervals.
Both datetimes and intervals are made up of fields. The values of these fields determine the value of the datatype. The fields that apply to all Oracle datetime and interval datatypes are:
TIMESTAMP WITH TIME ZONE also includes these fields:
TIMESTAMP WITH LOCAL TIME ZONE does not store time zone information, but you can see local time zone information in output if the TZH:TZM or TZR TZD format elements are specified.
The following sections describe the datetime datatypes and interval datatypes in more detail:
| See Also:
Oracle Database SQL Reference for the valid values of the datetime and interval fields. Oracle Database SQL Reference also contains information about format elements. |
This section includes the following topics:
The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.
You can specify a date value by:
TO_DATE functionA date can be specified as an ANSI date literal or as an Oracle date value.
An ANSI date literal contains no time portion and must be specified in exactly the following format:
DATE 'YYYY-MM-DD'
The following is an example of an ANSI date literal:
DATE '1998-12-25'
Alternatively, you can specify an Oracle date value as shown in the following example:
TO_DATE('1998-DEC-25 17:30','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN')
The default date format for an Oracle date value is derived from the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE initialization parameters. The date format in the example includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation. The specification for NLS_DATE_LANGUAGE is included because 'DEC' is not a valid value for MON in all locales.
Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions.
If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.
Oracle DATE columns always contain fields for both date and time. If your queries use a date format without a time portion, then you must ensure that the time fields in the DATE column are set to midnight. You can use the TRUNC (date) SQL function to ensure that the time fields are set to midnight, or you can make the query a test of greater than or less than (<, <=, >=, or >) instead of equality or inequality (= or !=). Otherwise, Oracle may not return the query results you expect.
See Also:
|
The TIMESTAMP datatype is an extension of the DATE datatype. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE datatype.
Specify the TIMESTAMP datatype as follows:
TIMESTAMP [(fractional_seconds_precision)]
fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field. It can be a number in the range 0 to 9. The default is 6.
For example, '26-JUN-02 09:39:16.78' shows 16.78 seconds. The fractional seconds precision is 2 because there are 2 digits in '78'.
You can specify the TIMESTAMP literal in a format like the following:
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'
Using the example format, specify TIMESTAMP as a literal as follows:
TIMESTAMP '1997-01-31 09:26:50.12'
The value of NLS_TIMESTAMP_FORMAT initialization parameter determines the timestamp format when a character string is converted to the TIMESTAMP datatype. NLS_DATE_LANGUAGE determines the language used for character data such as MON.
See Also:
|
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone offset or time zone region name in its value. The time zone offset 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
fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field.
You can specify TIMESTAMP WITH TIME ZONE as a literal as follows:
TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
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, the following expressions have the same value:
TIMESTAMP '1999-01-15 8:00:00 -8:00' TIMESTAMP '1999-01-15 11:00:00 -5:00'
You can replace the UTC offset with the TZR (time zone region) format element. The following expression specifies US/Pacific for the time zone region:
TIMESTAMP '1999-01-15 8:00:00 US/Pacific'
To eliminate the ambiguity of boundary cases when the time switches from Standard Time to Daylight Saving Time, use both the TZR format element and the corresponding TZD format element. The TZD format element is an abbreviation of the time zone region with Daylight Saving Time information included. Examples are PST for US/Pacific standard time and PDT for US/Pacific daylight time.The following specification ensures that a Daylight Saving Time value is returned:
TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'
If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If ERROR_ON_OVERLAP_TIME is set to FALSE (the default value), then Oracle interprets the ambiguous datetime as Standard Time.
The default date format for the TIMESTAMP WITH TIME ZONE datatype is determined by the value of the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.
See Also:
|
TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP. It differs from TIMESTAMP WITH TIME ZONE as follows: data stored in the database is normalized to the database time zone, and the time zone offset 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 offset 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
fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field.
There is no literal for TIMESTAMP WITH LOCAL TIME ZONE, but TIMESTAMP literals and TIMESTAMP WITH TIME ZONE literals can be inserted into a TIMESTAMP WITH LOCAL TIME ZONE column.
The default date format for TIMESTAMP WITH LOCAL TIME ZONE is determined by the value of the NLS_TIMESTAMP_FORMAT initialization parameter.
See Also:
|
You can insert values into a datetime column in the following ways:
TO_TIMESTAMP, TO_TIMESTAMP_TZ, or TO_DATE SQL functionThe following examples show how to insert data into datetime datatypes.
Set the date format.
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Create a table table_dt with columns c_id and c_dt. The c_id column is of NUMBER datatype and helps to identify the method by which the data is entered. The c_dt column is of DATE datatype.
SQL> CREATE TABLE table_dt (c_id NUMBER, c_dt DATE);
Insert a date as a character string.
SQL> INSERT INTO table_dt VALUES(1, '01-JAN-2003');
Insert the same date as a DATE literal.
SQL> INSERT INTO table_dt VALUES(2, DATE '2003-01-01');
Insert the date as a TIMESTAMP literal. Oracle drops the time zone information.
SQL> INSERT INTO table_dt VALUES(3, TIMESTAMP '2003-01-01 00:00:00 US/Pacific');
Insert the date with the TO_DATE function.
SQL> INSERT INTO table_dt VALUES(4, TO_DATE('01-JAN-2003', 'DD-MON-YYYY'));
Display the data.
SQL> SELECT * FROM table_dt; C_ID C_DT ---------- -------------------- 1 01-JAN-2003 00:00:00 2 01-JAN-2003 00:00:00 3 01-JAN-2003 00:00:00 4 01-JAN-2003 00:00:00
Set the timestamp format.
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';
Create a table table_ts with columns c_id and c_ts. The c_id column is of NUMBER datatype and helps to identify the method by which the data is entered. The c_ts column is of TIMESTAMP datatype.
SQL> CREATE TABLE table_ts(c_id NUMBER, c_ts TIMESTAMP);
Insert a date and time as a character string.
SQL> INSERT INTO table_ts VALUES(1, '01-JAN-2003 2:00:00');
Insert the same date and time as a TIMESTAMP literal.
SQL> INSERT INTO table_ts VALUES(2, TIMESTAMP '2003-01-01 2:00:00');
Insert the same date and time as a TIMESTAMP WITH TIME ZONE literal. Oracle converts it to a TIMESTAMP value, which means that the time zone information is dropped.
SQL> INSERT INTO table_ts VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');
Display the data.
SQL> SELECT * FROM table_ts; C_ID C_TS ---------- ----------------------------- 1 01-JAN-03 02:00:00.000000 AM 2 01-JAN-03 02:00:00.000000 AM 3 01-JAN-03 02:00:00.000000 AM
Note that the three methods result in the same value being stored.
Set the timestamp format.
SQL> ALTER SESSION SET NLS_TIMESTAMP__TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';
Set the time zone to '-07:00'.
SQL> ALTER SESSION SET TIME_ZONE='-7:00';
Create a table table_tstz with columns c_id and c_tstz. The c_id column is of NUMBER datatype and helps to identify the method by which the data is entered. The c_tstz column is of TIMESTAMP WITH TIME ZONE datatype.
SQL> CREATE TABLE table_tstz (c_id NUMBER, c_tstz TIMESTAMP WITH TIME ZONE);
Insert a date and time as a character string.
SQL> INSERT INTO table_tstz VALUES(1, '01-JAN-2003 2:00:00 AM -07:00');
Insert the same date and time as a TIMESTAMP literal. Oracle converts it to a TIMESTAMP WITH TIME ZONE literal, which means that the session time zone is appended to the TIMESTAMP value.
SQL> INSERT INTO table_tstz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');
Insert the same date and time as a TIMESTAMP WITH TIME ZONE literal.
SQL> INSERT INTO table_tstz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -8:00');
Display the data.
SQL> SELECT * FROM table_tstz; C_ID C_TSTZ ---------- ------------------------------------ 1 01-JAN-03 02:00.00:000000 AM -07:00 2 01-JAN-03 02:00:00.000000 AM -07:00 3 01-JAN-03 02:00:00.000000 AM -08:00
Note that the time zone is different for method 3, because the time zone information was specified as part of the TIMESTAMP WITH TIME ZONE literal.
Consider data that is being entered in Denver, Colorado, U.S.A., whose time zone is UTC-7.
SQL> ALTER SESSION SET TIME_ZONE='07:00';
Create a table table_tsltz with columns c_id and c_tsltz. The c_id column is of NUMBER datatype and helps to identify the method by which the data is entered. The c_tsltz column is of TIMESTAMP WITH LOCAL TIME ZONE datatype.
SQL> CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);
Insert a date and time as a character string.
SQL> INSERT INTO table_tsltz VALUES(1, '01-JAN-2003 2:00:00');
Insert the same data as a TIMESTAMP WITH LOCAL TIME ZONE literal.
SQL> INSERT INTO table_tsltz VALUE(2, TIMESTAMP '2003-01-01 2:00:00');
Insert the same data as a TIMESTAMP WITH TIME ZONE literal. Oracle converts the data to a TIMESTAMP WITH LOCAL TIME ZONE value. This means the time zone that is entered (-08:00) is converted to the session time zone value (-07:00).
SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');
Display the data.
SQL> SELECT * FROM table_tsltz; C_ID C_TSLTZ ---------- ------------------------------------ 1 01-JAN-03 02.00.00.000000 AM 2 01-JAN-03 02.00.00.000000 AM 3 01-JAN-03 03.00.00.000000 AM
Note that the information that was entered as UTC-8 has been changed to the local time zone, changing the hour from 2 to 3.
| See Also:
"Datetime SQL Functions" for more information about the |
Use the TIMESTAMP datatype when you need a datetime value without locale information. For example, you can store information about the times when workers punch a timecard in and out of their assembly line workstations. The TIMESTAMP datatype uses 7 or 11 bytes of storage.
Use the TIMESTAMP WITH TIME ZONE datatype when the application is used across time zones. Consider a banking company with offices around the world. It records a deposit to an account at 11 a.m. in London and a withdrawal of the same amount from the account at 9 a.m. in New York. The money is in the account for four hours. Unless time zone information is stored with the account transactions, it appears that the account is overdrawn from 9 a.m. to 11 a.m.
The TIMESTAMP WITH TIME ZONE datatype requires 13 bytes of storage, or two more bytes of storage than the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE datatypes because it stores time zone information.The time zone is stored as an offset from UTC or as a time zone region name. The data is available for display or calculations without additional processing. A TIMESTAMP WITH TIME ZONE column cannot be used as a primary key. If an index is created on a TIMESTAMP WITH TIME ZONE column, it becomes a function-based index.
The TIMESTAMP WITH LOCAL TIME ZONE datatype stores the timestamp without time zone information. It normalizes the data to the database time zone every time the data is sent to and from a client. It requires 11 bytes of storage.
The TIMESTAMP WITH LOCAL TIME ZONE datatype is appropriate when the original time zone is of no interest, but the relative times of events are important. Consider the transactions described in the previous banking example. Suppose the data is recorded using the TIMESTAMP WITH LOCAL TIME ZONE datatype. If the database time zone of the bank is set to Asia/Hong_Kong, then an employee in Hong Kong who displays the data would see that the deposit was made at 1900 and the withdrawal was made at 2300. If the same data is displayed in London, it would show that the deposit was made at 1100 and the withdrawal was made at 1500. The four-hour difference is preserved, but the actual times are not, making it impossible to tell whether the transactions were done during business hours.
Interval datatypes store time durations. They are used primarily with analytic functions. For example, you can use them to calculate a moving average of stock prices. You must use interval datatypes to determine the values that correspond to a particular percentile. You can also use interval datatypes to update historical tables.
This section includes the following topics:
| See Also:
Oracle Data Warehousing Guide for more information about analytic functions, including moving averages (and inverse percentiles |
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
year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default value of year_precision is 2.
Interval values can be specified as literals. There are many ways to specify interval literals.The following is one example of specifying an interval of 123 years and 2 months.The year precision is 3.
INTERVAL '123-2' YEAR(3) TO MONTH
| See Also:
Oracle Database SQL Reference for more information about specifying interval literals with the |
INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. Specify this datatype as follows:
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
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.
The following is one example of specifying an interval of 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second. The fractional second precision is 3.
INTERVAL '4 5:12:10.222' DAY TO SECOND(3)
Interval values can be specified as literals. There are many ways to specify interval literals.
| See Also:
Oracle Database SQL Referencefor more information about specifying interval literals with the |
You can insert values into an interval column in the following ways:
INSERT INTO table1 VALUES (INTERVAL '4-2' YEAR TO MONTH);
This statement inserts an interval of 4 years and 2 months.
Oracle recognizes literals for other ANSI interval types and converts the values to Oracle interval values.
NUMTODSINTERVAL, NUMTOYMINTERVAL, TO_DSINTERVAL, and TO_YMINTERVAL SQL functions.
This section includes the following topics:
You can perform arithmetic operations on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE) and interval (INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH) data. You can maintain the most precision in arithmetic operations by using a timestamp datatype with an interval datatype.
You can use NUMBER constants in arithmetic operations on date and timestamp values. Oracle internally converts timestamp values to date values before doing arithmetic operations on them with NUMBER constants. This means that information about fractional seconds is lost during operations that include both date and timestamp values. Oracle interprets NUMBER constants in datetime and interval expressions as number of days.
Each DATE value contains a time component. The result of many date operations includes a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours. These fractions are also returned by Oracle built-in SQL functions for common operations on DATE data. For example, the built-in MONTHS_BETWEEN SQL function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.
Oracle performs all timestamp arithmetic in UTC time. For TIMESTAMP WITH LOCAL TIME ZONE data, Oracle converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. For TIMESTAMP WITH TIME ZONE data, the datetime value is always in UTC, so no conversion is necessary.
See Also:
|
When you compare date and timestamp values, Oracle converts the data to the more precise datatype before doing the comparison. For example, if you compare data of TIMESTAMP WITH TIME ZONE datatype with data of TIMESTAMP datatype, Oracle converts the TIMESTAMP data to TIMESTAMP WITH TIME ZONE, using the session time zone.
The order of precedence for converting date and timestamp data is as follows:
For any pair of datatypes, Oracle converts the datatype that has a smaller number in the preceding list to the datatype with the larger number.
If you want to do explicit conversion of datetime datatypes, use the CAST SQL function. You can explicitly convert DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE to another datatype in the list.
Datetime functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE) and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.
Some of the datetime functions were designed for the Oracle DATE datatype. If you provide a timestamp value as their argument, then Oracle internally converts the input type to a DATE value. Oracle does not perform internal conversion for the ROUND and TRUNC functions.
Table 4-1 shows the datetime functions that were designed for the Oracle DATE datatype. It contains cross-references to more detailed descriptions of the functions.
Table 4-2 describes additional datetime functions and contains cross-references to more detailed descriptions.
This section includes the following topics:
Table 4-3 contains the names and descriptions of the datetime format parameters.
Their default values are derived from NLS_TERRITORY.
You can specify their values by setting them in the initialization parameter file. You can specify their values for a client as client environment variables.
You can also change their values by changing their value in the initialization parameter file and then restarting the instance.
To change their values during a session, use the ALTER SESSION statement.
See Also:
|
The time zone environment variables are:
ORA_TZFILE, which specifies the Oracle time zone file used by the databaseORA_SDTZ, which specifies the default session time zone
ERROR_ON_OVERLAP_TIME is a session parameter that determines how Oracle handles an ambiguous datetime boundary value. Ambiguous datetime values can occur when the time changes between Daylight Saving Time and standard time.
The possible values are TRUE and FALSE. When ERROR_ON_OVERLAP_TIME is TRUE, then an error is returned when Oracle encounters an ambiguous datetime value. When ERROR_ON_OVERLAP_TIME is FALSE, then ambiguous datetime values are assumed to be standard time. The default value is FALSE.
The Oracle time zone files contain the valid time zone names. The following information is also included for each time zone:
Two time zone files are included in the Oracle home directory. The default time zone file, $ORACLE_HOME/oracore/zoneinfo/timezone.dat, contains the most commonly used time zones. More time zones are included in $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat.
To use the larger time zone file, complete the following tasks:
ORA_TZFILE environment variable to the full path name of the timezlrg.dat file.Oracle's time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/. Oracle's time zone data may not reflect the most recent data available at this site.
You can obtain a list of time zone names and time zone abbreviations from the time zone file that is installed with your database by entering the following statement:
SELECT tzname, tzabbrev FROM v$timezone_names;
For the default time zone file, this statement results in output similar to the following:
TZNAME TZABBREV -------------------- ---------- Africa/Cairo LMT Africa/Cairo EET Africa/Cairo EEST Africa/Tripoli LMT Africa/Tripoli CET Africa/Tripoli CEST Africa/Tripoli EET . . . W-SU LMT W-SU MMT W-SU MST W-SU MDST W-SU S W-SU MSD W-SU MSK W-SU EET W-SU EEST WET LMT WET WEST WET WET 622 rows selected.
There are 3 time zone abbreviations associated with the Africa/Cairo time zone and 4 abbreviations associated with the Africa/Tripoli time zone. The following table shows the time zone abbreviations and their meanings.
| Time Zone Abbreviation | Meaning |
|---|---|
|
LMT |
Local Mean Time |
|
EET |
Eastern Europe Time |
|
EEST |
Eastern Europe Summer Time |
|
CET |
Central Europe Time |
|
CEST |
Central Europe Summer Time |
Note that an abbreviation can be associated with more than one time zone. For example, EET is associated with both Africa/Cairo and Africa/Tripoli, as well as time zones in Europe.
If you want a list of time zones without repeating the time zone name for each abbreviation, use the following query:
SELECT UNIQUE tzname FROM v$timezone_names;
For the default file, this results in output similar to the following:
TZNAME -------------------- Africa/Cairo Africa/Tripoli America/Adak America/Anchorage . . . US/Mountain US/Pacific US/Pacific_New US/Samoa W-SU
The default time zone file contains more than 180 unique time zone names. The large time zone file has more than 350 unique time zone names.
See Also:
|
Set the database time zone when the database is created by using the SET TIME_ZONE clause of the CREATE DATABASE statement. If you do not set the database time zone, it defaults to the time zone of the server's operating system.
The time zone may be set to an absolute offset from UTC or to a named region. For example, to set the time zone to an offset from UTC, use a statement similar to the following:
CREATE DATABASE db01 ... SET TIME_ZONE='-05:00';
The range of valid offsets is -12:00 to +14:00.
To set the time zone to a named region, use a statement similar to the following:
CREATE DATABASE db01 ... SET TIME_ZONE='Europe/London';
You can change the database time zone by using the SET TIME_ZONE clause of the ALTER DATABASE statement. For example:
ALTER DATABASE SET TIME_ZONE='05:00'; ALTER DATABASE SET TIME_ZONE='Europe/London';
The ALTER DATABASE SET TIME_ZONE statement returns an error if the database contains a table with a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data.
The change does not take effect until the database has been shut down and restarted.
You can find out the database time zone by entering the following query:
SELECT dbtimezone FROM dual;
You can set the default session time zone with the ORA_SDTZ environment variable. When users retrieve TIMESTAMP WITH LOCAL TIME ZONE data, Oracle returns it in the users' session time zone. The session time zone also takes effect when a TIMESTAMP value is converted to the TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE datatype.
The ORA_SDTZ environment variable can be set to the following values:
'OS_TZ')'DB_TZ')'-05:00')'Europe/London')To set ORA_SDTZ, use statements similar to one of the following in a UNIX environment (C shell):
% setenv ORA_SDTZ 'OS_TZ' % setenv ORA_SDTZ 'DB_TZ' % setenv ORA_SDTZ '-05:00' % setenv ORA_SDTZ 'Europe/London'
You can change the time zone for a specific SQL session with the SET TIME_ZONE clause of the ALTER SESSION statement.
TIME_ZONE can be set to the following values:
local)dbtimezone)'+10:00')'Asia/Hong_Kong')Use ALTER SESSION statements similar to the following:
ALTER SESSION SET TIME_ZONE=local; ALTER SESSION SET TIME_ZONE=dbtimezone; ALTER SESSION SET TIME_ZONE='+10:00'; ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong';
You can find out the current session time zone by entering the following query:
SELECT sessiontimezone FROM dual;
A datetime SQL expression can be one of the following:
A datetime expression can include an AT LOCAL clause or an AT TIME ZONE clause. If you include an AT LOCAL clause, then the result is returned in the current session time zone. If you include the AT TIME ZONE clause, then use one of the following settings with the clause:
(+|-)HH:MM' specifies a time zone as an offset from UTC. For example, '-07:00' specifies the time zone that is 7 hours behind UTC. For example, if the UTC time is 11:00 a.m., then the time in the '-07:00' time zone is 4:00 a.m.DBTIMEZONE: Oracle uses the database time zone established (explicitly or by default) during database creation.SESSIONTIMEZONE: Oracle uses the session time zone established by default or in the most recent ALTER SESSION statement.Asia/Hong_Kong.The following example converts the datetime value in the America/New_York time zone to the datetime value in the America/Los_Angeles time zone.
SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'America/Los_Angeles' "West Coast Time" FROM DUAL; West Coast Time ---------------------------------------------------------- 01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGELES
Oracle automatically determines whether Daylight Saving Time is in effect for a specified time zone and returns the corresponding local time. The datetime value is usually sufficient for Oracle to determine whether Daylight Saving Time is in effect for a specified time zone. The periods when Daylight Saving Time begins or ends are boundary cases. For example, in the Eastern region of the United States, the time changes from 01:59:59 a.m. to 3:00:00 a.m. when Daylight Saving Time goes into effect. The interval between 02:00:00 and 02:59:59 a.m. does not exist. Values in that interval are invalid. When Daylight Saving Time ends, the time changes from 02:00:00 a.m. to 01:00:01 a.m. The interval between 01:00:01 and 02:00:00 a.m. is repeated. Values from that interval are ambiguous because they occur twice.
To resolve these boundary cases, Oracle uses the TZR and TZD format elements. TZR represents the time zone region in datetime input strings. Examples are 'Australia/North', 'UTC', and 'Singapore'. TZD represents an abbreviated form of the time zone region with Daylight Saving Time information. Examples are 'PST' for US/Pacific standard time and 'PDT' for US/Pacific daylight time. To see a list of valid values for the TZR and TZD format elements, query the TZNAME and TZABBREV columns of the V$TIMEZONE_NAMES dynamic performance view.
The rest of this section contains the following topic:
| See Also:
"Time Zone Names" for a list of valid time zones |
The TIMESTAMP datatype does not accept time zone values and does not calculate Daylight Saving Time.
The TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE datatypes have the following behavior:
The rest of this section contains examples that use datetime datatypes. The examples use the global_orders table. It contains the orderdate1 column of TIMESTAMP datatype and the orderdate2 column of TIMESTAMP WITH TIME ZONE datatype. The global_orders table is created as follows:
CREATE TABLE global_orders ( orderdate1 TIMESTAMP(0), orderdate2 TIMESTAMP(0) WITH TIME ZONE); INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM', '28-OCT-00 11:24:54 PM America/New_York');
SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR FROM global_orders;
The following output results:
ORDERDATE1+INTERVAL'8'HOUR ORDERDATE2+INTERVAL'8'HOUR -------------------------- -------------------------- 29-OCT-00 07.24.54.000000 AM 29-OCT-00 06.24.54.000000 AM AMERICA/NEW_YORK
This example shows the effect of adding 8 hours to the columns. The time period includes a Daylight Saving Time boundary (a change from Daylight Saving Time to standard time). The orderdate1 column is of TIMESTAMP datatype, which does not use Daylight Saving Time information and thus does not adjust for the change that took place in the 8-hour interval. The TIMESTAMP WITH TIME ZONE datatype does adjust for the change, so the orderdate2 column shows the time as one hour earlier than the time shown in the orderdate1 column.
|
Note: If you have created a |
The TIMESTAMP WITH LOCAL TIME ZONE datatype uses the value of TIME_ZONE that is set for the session environment. The following statements set the value of the TIME_ZONE session parameter and create an orders table. The global_orders table has one column of TIMESTAMP datatype and one column of TIMESTAMP WITH LOCAL TIME ZONE datatype.
ALTER SESSION SET TIME_ZONE='America/New_York'; CREATE TABLE global_orders ( orderdate1 TIMESTAMP(0), orderdate2 TIMESTAMP(0) WITH LOCAL TIME ZONE ); INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM', '28-OCT-00 11:24:54 PM' );
Add 8 hours to both columns.
SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR FROM global_orders;
Because a time zone region is associated with the datetime value for orderdate2, the Oracle server uses the Daylight Saving Time rules for the region. Thus the output is the same as in Example 4-6. There is a one-hour difference between the two calculations because Daylight Saving Time is not calculated for the TIMESTAMP datatype, and the calculation crosses a Daylight Saving Time boundary.
Set the time zone region to UTC. UTC does not use Daylight Saving Time.
ALTER SESSION SET TIME_ZONE='UTC';
Truncate the global_orders table.
TRUNCATE TABLE global_orders;
Insert values into the global_orders table.
INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM', TIMESTAMP '2000-10-28 23:24:54 ' );
Add 8 hours to the columns.
SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR FROM global_orders;
The following output results.
ORDERDATE1+INTERVAL'8'HOUR ORDERDATE2+INTERVAL'8'HOUR -------------------------- --------------------------- 29-OCT-00 07.24.54.000000000 AM 29-OCT-00 07.24.54.000000000 AM UTC
The times are the same because Daylight Saving Time is not calculated for the UTC time zone region.
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|