4
Datetime Datatypes and Ti me Zone Support

You can specify a date value by:

• Specifying the date value as a literal
• Converting a character or numeric value to a date value with the TO_DATE function

A 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 ex ample 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')

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 follo wing 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 corres ponding 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 day light time.The following specification ensures that a Daylight Saving Time value is returned:

<
/a>TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'

If you do not a dd 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_F ORMAT initialization parameter.

TIMESTAMP WITH LOCAL TIME ZONE Datatype

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP. It differs from TIMESTAMP WI TH 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 zo ne offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Ti me).

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 d
igits 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 liter
als 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 initi
alization parameter.
 



See Also:  

Oracle Database SQL Reference for more inform
ation about the TIMESTAMP WITH LOCAL TIME ZONE datatype
"TIMESTAMP Datatype" for more information about fractional seconds precis
ion
"NLS_TIMESTAMP_FORMAT"
 
Inserting Values into Datetime Datatypes

 
You can insert values into a datetime column in the following ways:

Insert a character string whose format is based on the appropriate NLS format value
Insert a literal
Insert a literal for which implicit c
onversion is performed
Use the TO_TIMESTAMP, TO_TIMESTAMP_T
Z, or TO_DATE SQL function
 
The following examples show how to ins
ert data into datetime datatypes.
 
Example 4-
1 Inserting Data into a DATE Column
 
Set the date format.
<
a name="1008691">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 co
lumn is of DATE datatype.

SQL> CREATE TABLE table_dt (c_id NUMBER, c_dt DAT
E);




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 V
ALUES(2, DATE '2003-01-01');




Insert the date as a TIMESTAMP
 literal. Oracle drops the time zone information.
SQL> INSERT INTO table_dt VALUE
S(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-2
003 00:00:00
3          01-JAN-2003 00:00:00
4          01-JAN-2003 00:00:00



Example 4-2 Inserting Data into a TIMESTAMP Column

 
Set the timestamp format.
SQL> ALTE
R SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';




Creat
e 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 s
ame date and time as a TIMESTAMP WITH TIME ZONE literal. Oracle converts it to a TIMESTAMP value, which mea
ns that the time zone information is dropped.
SQL> INSERT INTO table_ts VALUES(3, TIMEST
AMP '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-JA
N-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.
 
<
font face="Arial, Helvetica, sans-serif">Example 4-3 Inserting Data into the TIMESTAMP WITH TIME ZONE Datatype
<
a name="1008874"> 
Set the timestamp format.
SQL> ALTER SESSION SET NL
S_TIMESTAMP__TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';




Set the ti
me 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 tabl
e_tstz VALUES(1, '01-JAN-2003 2:00:00 AM -07:00');




Insert the sam
e date and time as a TIMESTAMP literal. Oracle converts it to a TIMESTAMP WITH TIME ZONE literal, which mea
ns that the session time zone is appended to the TIMESTAMP value.
SQL> INSE
RT INTO table_tstz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');




Ins
ert the same date and time as a TIMESTAMP WITH TIME ZONE literal.
SQL> INSE
RT 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:0000
00 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 meth
od 3, because the time zone information was specified as part of the TIMESTAMP WITH TIME ZONE literal.
 
Example 4-4 Inserting Data into the TIMESTAMP WITH LOCAL TIME
 ZONE Datatype
 
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 e
ntered. 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> INSE
RT 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 tab
le_tsltz VALUE(2, TIMESTAMP '2003-01-01 2:00:00');




Insert the sam
e 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> SE
LECT * 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 TO_TIMESTAMP or TO_TIMESTAMP_TZ SQL functions

 
Choosing a T
IMESTAMP Datatype

 
Use the TIMESTAMP datatype when you n
eed 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. Consi
der 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 store
d 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 TI
MESTAMP and TIMESTAMP WITH LOCAL TIME ZONE datatypes because it stores time zone information.The time zone is sto
red as an offset from UTC or as a time zone region name. The data is available for display or calculations without additional process
ing. 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 TIM
ESTAMP WITH LOCAL TIME ZONE datatype is appropriate when the original time zone is of no interest, but the relative times of e
vents 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 a
n 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 busine
ss hours.
 
Interval Datatypes

 
Interval datatypes store time durations. They ar
e used primarily with analytic functions. For example, you can use them to calculate a moving average of stock prices. You must use i
nterval datatypes to determine the values that correspond to a particular percentile. You can also use interval datatypes to update h
istorical tables.
 
This section includes the following topics:

INTERVAL YEAR TO MONTH Datatype
INTERVAL DAY TO SECOND Datatype
Inserting Values into Interval Datatypes 




See Also:  
Oracle Data Warehousing Guide for more informa
tion about analytic functions, including moving averages (and inverse percentiles
 
INTERVAL YEAR TO MONTH Dat
atype

 
INTERVAL YEAR TO MONTH stores a period of time us
ing 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 value
s are 0 to 9. The default value of year_precision is 2.
 
Interval valu
es can be specified as literals. There are many ways to specify interval literals.The following is one example of specifying an inter
val 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 YEAR T
O MONTH datatype
 
INTERVAL DAY TO SECOND Datatype

 
INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. Specify this datatype a
s follows:
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_preci
sion)]




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 dateti
me field. Accepted values are 0 to 9. The default is 6.
 
The following is one example of spe
cifying 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 Da
tabase SQL Referencefor more information about specifying interval literals with the INTERVAL DAY TO SECOND dat
atype
 
Inserting Values into Interval Datatypes

 
You ca
n insert values into an interval column in the following ways:

Insert an interval as a literal. For example: 
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 t
o Oracle interval values.
Use the NUMTODSINTERVAL, NUMT
OYMINTERVAL, TO_DSINTERVAL, and TO_YMINTERVAL SQL functions. 





See Also:  
"Datetime SQL Functions"
 
Datetime and Interval
 Arithmetic and Comparisons

 
This section includes the following topi
cs:

Datetime and Interval Arith
metic
Datetime Comparisons
Explicit Conversion of Datetime Datatypes
 
Datetime and Int
erval Arithmetic

 
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 prec
ision 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 va
lues 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 const
ants in datetime and interval expressions as number of days.
 
Each DATE value c
ontains a time component. The result of many date operations includes a fraction. This fraction means a portion of one day. For examp
le, 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 fract
ional 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 d
atabase time zone to UTC and converts back to the database time zone after performing the arithmetic. For TIMESTAMP WITH TIME Z
ONE data, the datetime value is always in UTC, so no conversion is necessary.
 




See Also:  

Oracle Database SQL Reference for detailed information about datetime and interval arithmetic operations
"Datetime SQL Functions" for information about which functions cause implicit conversion to DATE
 
Datetime Comparisons


 
When you compare date and timestamp values, Oracle converts the dat
a 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 t
imestamp data is as follows:

DATE

TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
TIMEST
AMP WITH TIME ZONE
 
For any pair of datatypes, Oracle converts the datatype tha
t 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 SQL Functions
Datetime functions operate on date (DAT
E), 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 the
ir 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-ref
erences to more detailed descriptions of the functions.
 Table 4-1 Datetime Functions Designed for the DATE Datatype  


See Also:  
Oracle Database SQL Reference

 


 

 

 

 




 Function
 Description


 
ADD_MONTHS
 
Returns the date d plus n months

 
LAST_DAY
 
Returns the last day of the month that contains date

 
MONTHS_BETWEEN
 
Returns
the number of months between date1 and date2

 
NEW_TIME
 
Returns the
 date and time in zone2 time zone when the date and time in zone1 time zone are date. 
Note: This function takes as input only a l
imited number of time zones. You can have access to a much greater number of time zones by combining the FROM_TZ functio
n and the datetime expression.



NEXT_DAY
 
Returns the date of the first weekday named by char<
/em> that is later than date

 
RO
UND (date)
 
Returns date rounded to
 the unit specified by the fmt format model

 
TRUNC (date)
 
Returns date<
/em> with the time portion of the day truncated to the unit specified by the fmt format model

 
Table 4-2 describes additional datetime fu
nctions and contains cross-references to more detailed descriptions.
 
 Table 4-2 Additional Datetime Functions  





 Datetime Function
 Description



 
CURRENT_DATE

 
Returns the current date in the session time zone in a value in the Gregorian calendar, of the
DATE datatype

 
CURRENT_TIMESTAMP
 
Returns the current date and time in the session time zone as a
TIMESTAMP WITH TIME ZONE value

 
DBTIMEZON
E
 
Returns the value of the database time zone. The value
 is a time zone offset or a time zone region name.

 
EX
TRACT (datetime)
 
Extracts and returns the value of a spe
cified datetime field from a datetime or interval value expression

 
FROM_TZ
 
Converts a TIMESTAMP
value at a time zone to a TIMESTAMP WITH TIME ZONE value

 
LOCALTIMESTAMP
 
Returns the current date
 and time in the session time zone in a value of the TIMESTAMP datatype

 
NUMTODSINTERVAL
 
Converts
 number n to an INTERVAL DAY TO SECOND literal

 
NUMTOYMINTERVAL
 
Converts nu
mber n to an INTERVAL YEAR TO MONTH literal

 
SESSIONTIMEZONE
 
Returns the va
lue of the current session's time zone

 
SYS_EXTRACT_UT
C
 
Extracts the UTC from a datetime with time zone offset


 
SYSDATE
 
Returns the date and time of the operating system on which the database resides, taking into account
 the time zone of the database server's operating system that was in effect when the database was started.

 
SYSTIMESTAMP
 
Returns the system date, including fractional seconds and time zone of the system on which the database resides


 
TO_CHAR (datetime)
 
Converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP
WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the fo
rmat specified by the fmt date format.

 
TO_DSINTERVAL
 
Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of INTERVAL DAY TO SEC
OND datatype

 
TO_NCHAR (datetime)
 
Converts a datetime or interval value of DATE, TIM
ESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL MONTH TO YEAR, or INTERVAL DAY TO SECOND datatype from the database character set to the national character set.

 
TO_TIMESTAMP
 
Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype

 

TO_TIMESTAMP_TZ
 
Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of the TIMESTAMP WITH
TIME ZONE datatype

 
TO_YMINTERVAL
 
Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of the INTERVAL YEAR TO MONTH datatype

 
TZ_OFFSET
 
Returns the time zone offset that corresponds to the entered value, based on the date that the statement is ex
ecuted
 



See
 Also:  
Oracle Database SQL Reference
 
Datetime and Time Zone Parameters and Environment Variables<
/h2>

 
This section includes the following topics:

Datetime Format Parameters
Time Zone Environment Variables
Daylight Saving Time Session Parameter
 
Datetime Format Parameters

 
Table 4-3 contains the names and descriptions of t
he datetime format parameters.
 Table 4-3 Datetime Format Parameters  



 Parameter
 Description


 
NLS_DATE_FORMAT
 
Defines the default date format to use with the TO_CHAR and TO_DATE functions

 
NLS_TIMESTAMP_FORMAT
 
Defines the default timestamp format to use with the TO_CHAR
and TO_TIMESTAMP functions

 
NLS_TIMESTAMP_TZ_FORMAT
 
Define
s the default timestamp with time zone format to use with the TO_CHAR and TO_TIMESTAMP_TZ functions
 
Their default values are derived from NLS_TERRITORY.
 
You can specify their values by setting them in the initialization parameter file. You can specif
y their values for a client as client environment variables.
 
You can also change their valu
es 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:  

"Date and Time Parameters"<
/a> for more information, including examples

"NL
S_DATE_FORMAT"
"NLS_TIMESTAMP_FORMAT"
"NLS_TIMESTAMP_TZ_FORMAT"
 
Time Zone Environment Varia
bles

 
The time zone environment variables are:

<
li class="LB1" type="disc">ORA_TZFILE, which specifies the Oracle time zone file used by the datab
ase
ORA_SDTZ, which specifies the default session time zone 
<
a name="1009077">



See Also:  

"Choosing a Time Zone Fil
e"
"Setti
ng the Session Time Zone"
 <
!--TOC=h2-"1009079"-->
Daylight Saving Time Session Paramete
r

 
ERROR_ON_OVERLAP_TIME is a session parameter that det
ermines how Oracle handles an ambiguous datetime boundary value. Ambiguous datetime values can occur when the time changes between Da
ylight Saving Time and standard time.
 
The possible values are TRUE and F
ALSE. When ERROR_ON_OVERLAP_TIME is TRUE, then an error is returned when Oracle encounters an ambigu
ous datetime value. When ERROR_ON_OVERLAP_TIME is FALSE, then ambiguous datetime values are assumed to be s
tandard time. The default value is FALSE.
 




See Also:  
"Support for Daylig
ht Saving Time"
 
Choosing a Time Zone File

 
The Oracle time zone files contain the valid time zone names. The following information is also included
for each time zone:

Offset from Coordinated Universal Time (UT
C)
Transition times for Daylight Saving Time

Abbreviations for standard time and Daylight Saving Time
 
Two ti
me 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:

Shut down the database.

Set the ORA_TZFILE environment variable to the full path name of the timezlrg.dat fil
e.
Restart the database.
 
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 F
ROM v$timezone_names;




For the default time zone file, this statem
ent results in output similar to the following:
TZNAME               TZABBREV
-------------------- ----------
Africa/Cairo         LMT
Africa/Cairo         EE
T
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 s
elected.




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 abbr
eviations and their meanings.
 

<
td class="Informal">
EEST



 Time Zone Abbreviation
 Meaning


 
LMT
 
Local Mean Time


 
EET
 
Eastern Europe Time
 

 
Ea
stern Europe Summer Time

 
<
p class="TB">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 repeati
ng 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 outp
ut similar to the following:
TZNAME
--------------------
Africa/Cairo
Africa/Tripoli
America/Adak
America/Anchorag
e
.
.
.
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 f
ile has more than 350 unique time zone names.
 





Note:  
If you use the larger time zone file, it is not practical to return t
o the smaller time zone file because the database may contain data with time zones that are not part of the smaller time zone file.

 


<
tr class="NoteAlso">
See Also
:  

"Customizing Time Zone Data"
"Time Zone Names" for a list of valid Oracle time zone names
<
/td>
 
Setting the Database Time Zone

 
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 fro
m UTC, use a statement similar to the following:
CREATE DATABASE db01
...
SET TIME_ZONE='-05:00';




The range of v
alid offsets is -12:00 to +14:00.
 
To set the time zone to a named region, use a statement s
imilar to the following:
CREATE DATABASE db01
...
<
/a>SET TIME_ZONE='Europe/London';








Note:
  
The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZO
NE columns. Oracle Corporation recommends that you set the database time zone to UTC (0:00) to avoid data conversion and impro
ve performance when data is transferred among databases. This is especially important for distributed databases, replication, and exp
orting and importing.

 
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;



Setting the Session Time Zone

 
You can set the
 default session time zone with the ORA_SDTZ environment variable. When users retrieve TIMESTAMP WITH LOCAL TIME Z
ONE 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:

Operating system local time zone ('OS_TZ')
Database time zone ('DB_TZ')
Absolute offset from UTC (for example, '-05:00')
Time
zone region name (for example, 'Europe/London')
To set ORA_SDTZ, use statements similar to one of the following in a UNIX environment (C shell):
% set
env 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:

Default local time zone when the session was started (local)

Database time zone (dbtimezone)
Absolute of
fset from UTC (for example, '+10:00')
Time zone region name (for
example, 'Asia/Hong_Kong')
 
Use ALTER SESSION statements sim
ilar to the following:
ALTER SESSION SET TIME_ZONE=local;
ALTER SESSI
ON 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;



Converting Time Zones Wi
th the AT TIME ZONE Clause

 
A datetime SQL expression can be one of t
he following:

A datetime column
A compound expression that yields a datetime value
 
A datetim
e 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 u
se one of the following settings with the clause:


Time zone of
fset: The string '(+|-)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.
SESSIO
NTIMEZONE: Oracle uses the session time zone established by default or in the most recent ALTER SESSION statement
.
Time zone region name: Oracle returns the value in the time zone indicated b
y the time zone region name. For example, you can specify Asia/Hong_Kong.
An expression: If an expression returns a character string with a valid time zone format, then Oracle returns the input in t
hat time zone. Otherwise Oracle returns an error.
 
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
.
 
Example 4-5 Converting a Datetime Value to
 Another 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_Ang
eles' "West Coast Time"
FROM DUAL;

West Coast Time
----------------------------------------------------------
01-DEC-99 08.00.00.000000 AM AMERICA/LOS_ANGE
LES









Note:  
Setting the session time zone does not affect the value returned by the SYSDATE and SYSTIMESTAMP
 SQL function. SYSDATE returns the date and time of the operating system on which the database resides, taking into acco
unt the time zone of the database server's operating system that was in effect when the database was started.

 


 





See Also:  
Oracle Database SQL Reference

 
Support for Daylight Saving Time

 
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 in
terval 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 string
s. Examples are 'Australia/North', 'UTC', and 'Singapore'. TZD represents an abbr
eviated form of the time zone region with Daylight Saving Time information. Examples are 'PST' for US/Pacific standard t
ime 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 perform
ance view.
 
The rest of this section contains the following topic:

Examples: The Effect of Daylight Saving Time on Datetime
 Calculations 




See Also:  
"Time Zone Names" for a list of valid time zones
 

Examples: The Effect of Daylight Saving Time on Datetime Calculations

 
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:

If a time zone region is associated with the datetime value, then the database server knows the Daylight Saving Time
 rules for the region and uses the rules in calculations.
Daylight Saving Time
 is not calculated for regions that do not use Daylight Saving Time.
 
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<
/code> datatype. The global_orders table is created as follows:

CREATE TABLE g
lobal_orders ( orderdate1 TIMESTAMP(0),
                      orderdate2 TIMESTAMP(0) WITH TIME ZONE);
INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM',
                            '28-OCT-0
0 11:24:54 PM America/New_York');



Example
 4-6 Comparing Daylight Saving Time Calculations Using TIMESTAMP WITH TIME ZONE and TIMESTAMP
SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR 
       FROM global_order
s;




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 i
ncludes 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 orderd
ate2 column shows the time as one hour earlier than the time shown in the orderdate1 column.
 





Note:  
If
 you have created a global_orders table for the previous examples, then drop the global_orders table before
 you try Example 4-7 through Example 4-8.

 
Example 4-7 Comparing Dayl
ight Saving Time Calculations Using TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP
 
The TIMESTAMP WITH LOCAL TIME ZONE datatype uses the value of TIME_ZONE that is set for the session envi
ronment. The following statements set the value of the TIME_ZONE session parameter and create an orders tab
le. The global_orders table has one column of TIMESTAMP datatype and one column of TIMESTAMP WITH LOC
AL TIME ZONE datatype.
ALTER SESSION SET TIME_ZONE='America/New_York';
CREATE TABLE global_orders ( orderdate1 TIMESTAMP(0),
                             orderdate2 TIMESTAM
P(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 ti
me zone region is associated with the datetime value for orderdate2, the Oracle server uses the Daylight Saving Time rul
es for the region. Thus the output is the same as in Example 4-6. There is a one-hour differe
nce between the two calculations because Daylight Saving Time is not calculated for the TIMESTAMP datatype, and the calc
ulation crosses a Daylight Saving Time boundary.
 
Example 4-8 Daylight Saving Time Is Not Calculated for Regions That Do Not Use Daylight Saving Time
 
Set the time zone region to UTC. UTC does not use Daylight Saving Time.
ALTER SESSION SET TIME_ZONE='UTC';




Truncate the glo
bal_orders table.
TRUNCATE TABLE global_orders;




Insert values into the global_orders table.
IN
SERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM',
                                 TIMESTAMP '2000-10-2
8 23:24:54 ' );



Add 8 hours to the columns.
SE
LECT 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.










Previous


Next



Copyright © 199
6, 2003 Oracle Corporation

All Rights Reserved.





Home


Book List


Contents


Index
<
br>
Master Index


Feedback