Overview of Datetime and Interval Datatypes and Time Zone Support

Businesses conduct transactions across different time zones. Oracle Database 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 Database datetime and interval datatypes. It does not attempt to describe ANSI datatypes or other kinds of datatypes except when noted.

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 Database 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 TZRTZD format elements are specified.

The following sections describe the datetime datatypes and interval datatypes in more detail:

DATE Datatype

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 Database stores the following information: century, year, month, date, hour, minute, and second.

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 Database 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 Database date value as shown in the following example:

The default date format for an Oracle Database 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 Database 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 Database 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 Database may not return the query results you expect.

TIMESTAMP Datatype

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.

TIMESTAMP WITH TIME ZONE Datatype

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or time zone offset 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:

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

TIMESTAMP WITH LOCAL TIME ZONE Datatype

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

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.

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.

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 entered. The c_tsltz column is of TIMESTAMP WITH LOCAL TIME ZONE datatype.

Insert the same data as a TIMESTAMP WITH TIME ZONE literal. Oracle Database 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).

Choosing a TIMESTAMP Datatype

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 three 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 7 p.m. and the withdrawal was made at 10 p.m. If the same data is displayed in London, it would show that the deposit was made at 11 a.m. and the withdrawal was made at 2 p.m. The three-hour difference is preserved, but the time zone/region of the original transaction is not. Because of this, the actual time of the transaction can be interpreted differently depending on the time zone/region from which the information is retrieved. For example, in London, the transactions appear to be conducted within business hours, in Hong Kong, they do not.

Note that, because the original time zone region of the time data is not preserved in the TIMESTAMPWITHLOCALTIMEZONE data type, time data referring to times from regions such as Brazil and Israel, regions that update their Daylight Savings Transition dates frequently and at irregular periods, may be inaccurate. If time information from these regions is key to your application, you may wish to consider using one of the other datetime types.

Interval Datatypes

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.

Datetime and Interval Arithmetic and Comparisons

Datetime and Interval 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 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 Database 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 Database 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 Database 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 Database performs all timestamp arithmetic in UTC time. For TIMESTAMP WITH LOCAL TIME ZONE data, Oracle Database 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.

Datetime Comparisons

When you compare date and timestamp values, Oracle Database 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 Database 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:

DATE

TIMESTAMP

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH TIME ZONE

For any pair of datatypes, Oracle Database converts the datatype that has a smaller number in the preceding list to the datatype with the larger number.

Explicit Conversion of Datetime Datatypes

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 (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 Database DATE datatype. If you provide a timestamp value as their argument, then Oracle Database internally converts the input type to a DATE value. Oracle Database 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.

Extracts and returns the value of a specified 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 number n to an INTERVAL YEAR TO MONTH literal

SESSIONTIMEZONE

Returns the value of the current session's time zone

SYS_EXTRACT_UTC

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 format 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 SECOND datatype

Converts a datetime or interval value of DATE, TIMESTAMP, 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 executed

Daylight Saving Time Session Parameter

ERROR_ON_OVERLAP_TIME is a session parameter that determines how Oracle Database 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 Database encounters an ambiguous datetime value. When ERROR_ON_OVERLAP_TIME is FALSE, then ambiguous datetime values are assumed to be the standard time representation for the region. The default value is FALSE.

Choosing a Time Zone File

The Oracle Database 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 Database 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 11g environment, perform the following steps:

Shut down the database if it has been started.

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

Restart the database.

Note:

If you are already using the default time zone file, then it is not practical to change to the smaller time zone file because the database may contain data with time zones that are not part of the smaller time zone file.

Oracle Database time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/. Oracle Database 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:

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.

Time Zone Abbreviation

Meaning

LMT

Local Mean Time

PMT

Paris Mean Time

WET

Western European Time

WEST

Western European Summer Time

CET

Central Europe Time

CEST

Central Europe Summer Time

EET

Eastern Europe Time

EEST

Eastern Europe Summer Time

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:

Upgrading the Time Zone File

The time zone files that are supplied with Oracle Database are updated periodically to reflect changes in transition rules for various time zone regions. The files supplied with Oracle Database 11g have been updated to version 3. This version includes the recent change in the Daylight Savings Time (DST) rule for US time zones starting with the year 2007.

Note:

Oracle Database 9i includes version 1 of the time zone files, and Oracle Database 10g includes version 2. Various patches and patch sets, which are released separately for these releases, may update the time zone file version as well.

DST Transition Rules Changes

The changes to DST transition rules may affect existing data of TIMESTAMPWITHTIMEZONE datatype, because of the way Oracle Database stores this data internally. When users enter timestamps with time zone, Oracle Database converts the data to UTC, based on the transition rules in the time zone file, and stores the data together with the ID of the original time zone on disk. When data is retrieved, the reverse conversion from UTC takes place. For example, when the version 2 transition rules were in effect, the value TIMESTAMP'2007-11-02 12:00:00 America/Los_Angeles', would have been stored as UTC value '2007-11-02 20:00:00'plus the time zone ID for 'America/Los_Angeles'. The time in Los Angeles would have been UTC minus eight hours (PST). Under version 3 of the transition rules, the offset for the same day is minus seven hours (PDT). Beginning with year 2007, the DST will be in effect longer (until the first Sunday of November, which is November 4th in year 2007). Now when users retrieve the same timestamp and the new offset is added to the stored UTC time, they will receive TIMESTAMP'2007-11-02 13:00:00 America/Los_Angeles'. There is a one hour difference compared to the data previous to version 3 taking effect.

In the most common case, the value TIMESTAMP'2007-11-02 12:00:00 America/Los_Angeles' is meant to represent noon of November 2nd, 2007 in Los Angeles. Because the change to the DST transition rules introduces the one hour shift, the timestamp must be updated to point back to 12:00 p.m., as described further in this section. In the less common case, the value might be meant to represent the local time in Los Angeles when it is 08:00 p.m. in UTC. In this less common case, the value actually becomes correct only after the time zone file has been updated. No further correction is needed in this case. Therefore, before updating any timestamp values, it is important to understand what the values are meant to represent.

Note:

For any time zone region whose transition rules have been updated, the issue discussed in this section, "Upgrading the Time Zone File", affects only timestamps that point to the future relative to the effective date of the corresponding DST rule change. For example, no timestamp before year 2007 is affected by the version 3 change to the 'America/Los_Angeles' time zone region.

Updating the Time Zone File with the utltzuv2.sql Script

You can use the $ORACLE_HOME/rdbms/admin/utltzuv2.sql script to discover all columns of TIMESTAMPWITHTIMEZONE datatype in your database that are potentially affected by a time zone file update. The result is stored in the sys.sys_tzuv2_temptab table, which has five columns: table_owner, table_name, column_name, rowcount, and nested_tab. Each row of sys.sys_tzuv2_temptab describes a TIMESTAMPWITHTIMEZONE table column that: (a) contains at least one timestamp in any of the time zone regions that are affected by the time zone file update, or (b) belongs to a nested table. The nested_tab value 'YES' indicates that the table specified in the table_name has been inserted because of condition (b). For performance reasons, nested table columns are reported without scanning their contents for the affected time zone regions. For columns inserted because of condition (a), the rowcount value contains the number of timestamps in the column that contains one of the affected time zone regions.

The utltzuv2.sql script must be run before you update the database time zone file. This script identifies the time zone regions to search for by comparing the version of the current database time zone file with the version of the time zone file for which the script has been created. If the database time zone file is updated too early, then the script does nothing, as the two versions are equal. If the time zone file changes as result of an Oracle Database software upgrade from a previous release, or because a patch set is installed, then the script must be run before the database is upgraded. Therefore, before any new software is installed, you must locate and install the appropriate utltzuv2.sql patch that is specifically for your combination of old software version and new time zone file version.

To run the utltzuv2.sql script, start SQL*Plus, log in as SYSDBA, and run the following command:

SQL> @?/rdbms/admin/utltzuv2.sql

If your database has data that will be affected by the time zone file update, as discussed in the preceding section, then back up the timestamp data to an alternative format before you upgrade the time zone file. After the upgrade, update the data using the backup that you created, to ensure that the data is stored based on the new rules.

The Export utility cannot be used to correct the timestamps, because timestamps are exported in the internal storage format. Export followed by Import never changes the values.

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 datatype does not preserve the original time zone/region associated with the data.

Customers who update the time zone file in a database, and who use time zone regions that are affected by the new file version, are required to update the time zone file of all database clients. Additionally, all other databases communicating with this database must be updated to the same version. This ensures that the whole database environment will have the same version of the time zone file. This is not a requirement for customers not using the affected regions. However, Oracle recommends that you do so.

Note:

You can find the matrix of available patches for updating your time zone files by going to Oracle MetaLink at http://metalink.oracle.com and reading Document ID 396906.1.

See Also:

$ORACLE_HOME/oracore/zoneinfo/timezdif.csv, provided with your Oracle Database software installation, for a full list of time zones changed since Oracle9i, and Oracle Database Upgrade Guide for upgrade information

The utltzuv2.sql script that was provided for Oracle Database 10g to update its time zone file to version 3 uses an external table based on the supplied timezdif.csv file to describe time zone changes introduced in the version of the time zone file associated with the script and in the previous versions. To access this data yourself, for example, to check the years in which a changed rule is valid, create an equivalent external table as in the following example. Replace $ORACLE_HOME with the actual path to the Oracle Home directory of your Oracle Database software installation.

Example 4-6 Sample External Table to Query Contents of timezdif.csv

The following example creates an external table whose contents come from the file $ORACLE_HOME/oracore/zoneinfo/timezdif.csv:

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. To set the time zone to an offset from UTC, use a statement similar to the following example:

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

CREATE DATABASE db01
...
SET TIME_ZONE='Europe/London';

Note:

The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle recommends that you set the database time zone to UTC (0:00) to avoid data conversion and improve performance when data is transferred among databases. This is especially important for distributed databases, replication, and exporting and importing.

You can change the database time zone by using the SET TIME_ZONE clause of the ALTER DATABASE statement. For example:

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

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 account the time zone of the database server's operating system that was in effect when the database was started.

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

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 Database uses the database time zone established (explicitly or by default) during database creation.

SESSIONTIMEZONE: Oracle Database uses the session time zone established by default or in the most recent ALTER SESSION statement.

Time zone region name: Oracle Database 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 Database returns the input in that time zone. Otherwise, Oracle Database 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.

Support for Daylight Saving Time

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

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 datatype. The global_orders table is created as follows:

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 global_orders table for the previous examples, then drop the global_orders table before you try Example 4-9 through Example 4-10.

Example 4-9 Comparing Daylight 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 environment. The following statements set the value of the TIME_ZONE session parameter and create a global_orders table. The global_orders table has one column of TIMESTAMP datatype and one column of TIMESTAMP WITH LOCAL TIME ZONE datatype.

Because a time zone region is associated with the datetime value for orderdate2, the Oracle Database server uses the Daylight Saving Time rules for the region. Thus the output is the same as in Example 4-8. 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.

Example 4-10 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.