4 Datetime Datatypes and Time Zone Support

Overview of Datetime and Interval Datatypes and Time Zone Support

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.

Datetime and Interval Datatypes

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:

• YEAR

• MONTH

• DAY

• HOUR

• MINUTE

• SECOND

TIMESTAMP WITH TIME ZONE also includes these fields:

• TIMEZONE_HOUR

• TIMEZONE_MINUTE

• TIMEZONE_REGION

• TIMEZONE_ABBR

TIMESTAMP WITH LOCAL TIME ZONE does not store time zone information internally, but you can see local time zone information in SQL 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:

• Datetime Datatypes

• Interval Datatypes

  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.

DATE 'YYYY-MM-DD'

DATE '1998-12-25'

TO_DATE('1998-DEC-25 17:30','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN')

TIMESTAMP [(fractional_seconds_precision)]

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

TIMESTAMP '1997-01-31 09:26:50.12'

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

TIMESTAMP '1997-01-31 09:26:56.66 +02:00'

TIMESTAMP '1999-01-15 8:00:00 -8:00'
TIMESTAMP '1999-01-15 11:00:00 -5:00'

TIMESTAMP '1999-01-15 8:00:00 US/Pacific'

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

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

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

SQL> INSERT INTO table_dt VALUES(1, '01-JAN-2003');

SQL> INSERT INTO table_dt VALUES(2, DATE '2003-01-01');

SQL> INSERT INTO table_dt VALUES(3, TIMESTAMP '2003-01-01 00:00:00 US/Pacific');

SQL> INSERT INTO table_dt VALUES(4, TO_DATE('01-JAN-2003', 'DD-MON-YYYY'));

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

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';

SQL> CREATE TABLE table_ts(c_id NUMBER, c_ts TIMESTAMP);

SQL> INSERT INTO table_ts VALUES(1, '01-JAN-2003 2:00:00');

SQL> INSERT INTO table_ts VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

SQL> INSERT INTO table_ts VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');

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

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';

SQL> ALTER SESSION SET TIME_ZONE='-7:00';

SQL> CREATE TABLE table_tstz (c_id NUMBER, c_tstz TIMESTAMP WITH TIME ZONE);

SQL> INSERT INTO table_tstz VALUES(1, '01-JAN-2003 2:00:00 AM -07:00');

SQL> INSERT INTO table_tstz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

SQL> INSERT INTO table_tstz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -8:00');

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

SQL> ALTER SESSION SET TIME_ZONE='-07:00';

SQL> CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);

SQL> INSERT INTO table_tsltz VALUES(1, '01-JAN-2003 2:00:00');

SQL> INSERT INTO table_tsltz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');

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

INTERVAL YEAR [(year_precision)] TO MONTH

INTERVAL '123-2' YEAR(3) TO MONTH

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

INTERVAL '4 5:12:10.222' DAY TO SECOND(3)

Datetime and Interval Arithmetic and Comparisons

This section includes the following topics:

• Datetime and Interval Arithmetic

• Datetime Comparisons

• Explicit Conversion of Datetime Datatypes

Datetime SQL Functions

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.

Datetime and Time Zone Parameters and Environment Variables

This section includes the following topics:

• Datetime Format Parameters

• Time Zone Environment Variables

• Daylight Saving Time Session Parameter

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 (UTC)

• Transition times for Daylight Saving Time

• Abbreviations for standard time and Daylight Saving Time

Two time zone files are included in the Oracle home directory. The default time zone file is $ORACLE_HOME/oracore/zoneinfo/timezonelrg.dat, which contains all the time zones defined in the database. $ORACLE_HOME/oracore/zoneinfo/timezone.dat contains only the most commonly used time zones.

If you use the larger time zone file, then you must continue to use it unless you are sure that none of the additional time zones that it contains are used for data that is stored in the database. Also, all databases and client installations that share information must use the same time zone file.

To enable the use of $ORACLE_HOME/oracore/zoneinfo/timezone.dat, or if you are already using it as your time zone file and you want to continue to do so in an Oracle Database 10g environment, perform the following steps:

1. Shut down the database if it has been started.

2. Set the ORA_TZFILE environment variable to $ORACLE_HOME/oracore/zoneinfo/timezone.dat.

3. 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 FROM V$TIMEZONE_NAMES;

For the default time zone file, this statement results in output similar to the following:

TZNAME               TZABBREV
-------------------- ----------
Africa/Algiers       LMT
Africa/Algiers       PMT
Africa/Algiers       WET
Africa/Algiers       WEST
Africa/Algiers       CET
Africa/Algiers       CEST
Africa/Cairo         LMT
Africa/Cairo         EET
Africa/Cairo         EEST
Africa/Casablanca    LMT
Africa/Casablanca    WET
Africa/Casablanca    WEST
Africa/Casablanca    CET
...
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

1393 rows selected.

There are 6 time zone abbreviations associated with the Africa/Algiers time zone, 3 abbreviations associated with the Africa/Cairo time zone, and 4 abbreviations associated with the Africa/Casablanca time zone. The following table shows the time zone abbreviations and their meanings.

Note that an abbreviation can be associated with more than one time zone. For example, CET is associated with both Africa/Algiers and Africa/Casablanca, 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 time zone file, this results in output similar to the following:

TZNAME
--------------------
Africa/Algiers
Africa/Cairo
Africa/Casablanca
Africa/Ceuta
...
US/Pacific
US/Pacific-New
US/Samoa
UTC
W-SU
WET

The default time zone file contains more than 350 unique time zone names. The small time zone file contains more than 180 unique time zone names.

Upgrading the Time Zone File

The time zone files that are supplied with Oracle Database 10g have been updated from version 1 to version 2 to reflect changes in transition rules for some time zone regions. The changes may affect existing data of TIMESTAMP WITH TIME ZONE datatype. For example, when users enter TIMESTAMP '2003-02-17 09:00 America/Sao_Paulo', Oracle converts the data to UTC based on the transition rules in the time zone file and stores the data on disk. When the version 1 transition rules were in effect, Oracle stored '2003-02-17 11:00:00' and the time zone ID for 'America/Sao_Paulo' because the offset in this example was '-02:00'. The offset under version 2 transition rules is '-03:00'. When users retrieve the data, they receive '2003-02-17 08:00:00 American/Sao_Paulo'. There is a one-hour difference compared to the original data.

You can use the $ORACLE_HOME/rdbms/admin/utltzuv2.sql script to discover all columns of TIMESTAMP WITH TIME ZONE datatype in your database. Execute the script before you update the database time zone file to version 2. The result is stored in the sys.sys_tzuv2_temptab table. The table has 5 columns: table_owner, table_name, column_name, rowcount, nested_tab. The nested_tab column indicates whether the table mentioned in the table_name column is a nested table.

If your database has data that will be affected by the time zone file update, then back up the data before you upgrade the time zone file to version 2. After the upgrade, you must update the data to ensure that the data is stored based on the new rules. For example, if user scott has a table tztab, as in the following:

CREATE TABLE tztab(x NUMBER PRIMARY KEY, y TIMESTAMP WITH TIME ZONE);
INSERT INTO tztab VALUES(1, timestamp '2003-02-17 09:00:00 America/Sao_Paulo');

Before upgrading, you can create a table tztab_back (note that column y here is defined as VARCHAR2 to preserve the original value):

CREATE TABLE tztab_back(x NUMBER PRIMARY KEY, y VARCHAR2(256));
INSERT INTO tztab_back
SELECT x, TO_CHAR(y, 'YYYY-MM-DD HH24.MI.SSXFF TZR')
FROM tztab;

After upgrading, you need to update the data in the table tztab using the value in tztab_back, as in the following:

UPDATE tztab t SET t.y =
  (SELECT to_timestamp_tz(t1.y,'YYYY-MM-DD HH24.MI.SSXFF TZR')
FROM tztab_back t1
WHERE t.x=t1.x);

Or you can use the Export utility to export your data before the upgrade and then import your data again after the upgrade. See the comments in the utltzuv2.sql script for more information.

Although the transition rule changes may affect data of TIMESTAMP WITH LOCAL TIME ZONE datatype, there is no way to upgrade the data. The data cannot be upgraded because this type does not preserve the original time zone/region associated with the data.

Time zone regions in Brazil and Israel may have frequent transition rules changes, perhaps as often as every year. Use the time zone offset instead of the time zone region name to avoid storing inconsistent data.

Customers using time zone regions that have been updated in version 2 of the time zone files are required to update all Oracle9i Database clients and databases that will communicate with an Oracle Database 10g server. This ensures that all environments will have the same version of the time zone file, version 2. This is not a requirement for other customers, but Oracle still recommends that you do so. Users who need to update their time zone files to version 2 can find the following information on OracleMetaLink (http://metalink.oracle.com):

• readme.txt contains the list of time zone regions that have changed from version 1 to version 2.

• Actual time zone files for version 2 for the Oracle9i Database release.

• utltzuv2.sql script that must be run on the server side to find out if the database already has a column of type TIMESTAMP WITH TIME ZONE. It contains time zones that have changed from version 1 to version 2.

Oracle Database 10g clients that communicate with Oracle Database 10g servers automatically get version 2 of the time zone file, so there is no need to download the new time zone file.

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, then 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;

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 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:

• 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):

% 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:

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

• Database time zone (dbtimezone)

• Absolute offset from UTC (for example, '+10:00')

• Time zone region name (for example, '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;

Converting Time Zones With the AT TIME ZONE Clause

A datetime SQL expression can be one of the following:

• A datetime column

• A compound expression that yields a datetime value

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:

• Time zone offset: 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.

• SESSIONTIMEZONE: 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 by 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 that 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.

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

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 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.

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;

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

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' );

SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR 
FROM global_orders;

ALTER SESSION SET TIME_ZONE='UTC';

TRUNCATE TABLE global_orders;

INSERT INTO global_orders VALUES ( '28-OCT-00 11:24:54 PM',
                                 TIMESTAMP '2000-10-28 23:24:54 ' );

SELECT orderdate1 + INTERVAL '8' HOUR, orderdate2 + INTERVAL '8' HOUR 
FROM global_orders;

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