All about Databases - Oracle, PostgeSQL, Mysql, Big Data .....: Timestamps & time zones - Frequently Asked Questions (Doc ID 340512.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Oracle Database - Standard Edition - Version 9.0.1.0 and later
Information in this document applies to any platform.

PURPOSE

This note covers the FAQ for TIMESTAMP's and Time Zones in the Oracle database.

If you have a "wrong database time" (sysdate or systimestamp) please see first of all note 1627439.1 How to diagnose wrong time ( SYSDATE and SYSTIMESTAMP) after DST change , server reboot , database restart or installation when connecting to a database on an Unix server .

DATEs and Calendars are covered separately in: Note 227334.1 Dates & Calendars - Frequently Asked Questions, be aware that the influence of the OS time zone on functions like SYSDATE and SYSTIMESTAMP will be covered in that note, and not in this note.
For information about updates to the Oracle Database Timezone information please see Note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches.

QUESTIONS AND ANSWERS

1)What datatypes are using timezones?

Oracle has 2 datatypes who can store timezones: TimeStamp with Time Zone (TSTZ) and TimeStamp with Local Time Zone (TSLTZ). None of the 2 dataypes stored data is affected by the TZ variable on the server.

The biggest difference is that:

TimeStamp with Time Zone (TSTZ) data stores the time and the actual timezone offset/name used for that time at the moment of insert. The stored timezone information can be an offset or named timezone.
The date format for TIMESTAMP WITH TIME ZONE is determined by the value of the NLS_TIMESTAMP_TZ_FORMAT parameter.

In general TSTZ is the best dataype to store timezone information when dealing with multiple timezones seen it retains the original timezone used (and so possible DST information).

This select gives all TimeStamp with Time Zone (TSTZ) columns in your database:

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -'
    || c.data_type || ' ' col
  from dba_tab_cols c, dba_objects o
 where c.data_type like '%WITH TIME ZONE'
    and c.owner=o.owner
   and c.table_name = o.object_name
   and o.object_type = 'TABLE'
order by col
/

Oracle 10g and up uses some TSTZ columns in the Data Dictionary, if this select gives objects outside the Data Dictionary (= non-SYS / WMSYS owned tables) then you have user TZ (TSTZ and/or TSLTZ) data stored. A full overview of the Data Dictionary tables returned in 10g / 11g is found in: Note 402614.1 Time Zone Data in the Data Dictionary During a Time Zone File Update.

TimeStamp with Local Time Zone (TSLTZ) data stores internally the time converted to/from the database timezone (see point 3) from the timezone specified at insert/select time.

Note that the 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, the current DBTIMZONE is used. When users retrieve the data, Oracle Database returns it in the users' local session time zone from the current DBTIMEZONE.
The date format for TIMESTAMP WITH LOCAL TIME ZONE is determined by the value of the NLS_TIMESTAMP_FORMAT parameter. TIMESTAMP WITH LOCAL TIME ZONE data is NOT returned with a timezone in the result set.

If you store TSLTZ data the database timezone should always be an offset , see point 3) of this note.

Note that the SESSIONTIMEZONE (and NOT the database timezone) is the actual used timezone at both insert and select (if not specified explicit) to calculate the time for the inserted/returned result.

Point 3 and 29 of this note gives some examples.

This select gives all TimeStamp with Local Time Zone (TSLTZ) columns in your database:

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -'
    || c.data_type || ' ' col
  from dba_tab_cols c, dba_objects o
 where c.data_type like '%WITH LOCAL TIME ZONE'
    and c.owner=o.owner
   and c.table_name = o.object_name
   and o.object_type = 'TABLE'
order by col
/

More information is in the documentation set. Or in Note 756454.1 TSLTZ (TIMESTAMP WITH LOCAL TIME ZONE) data and DST updates

Note that the "Timestamp" datatype has no concept of timezones, it's basically the same as the "date" dataype but with added precision.

2)What is the difference between a timezone offset and a named timezone?

A timezone expressed in a offset (example: -04:00) is always the same and gives the difference compared to UTC (Coordinated Universal Time), practically (although not correct technically speaking) UTC is the same as GMT (Greenwich mean time), and UTC/GMT corresponds to a offset of +00:00.
Because a offset is fixed it cannot change reflect DST (Daylight Saving Time) changes. DST itself is a change of the offset.
A named timezone is a name (example: Canada/Eastern or GMT) who reflects a geographic region or location and this will be mapped to a certain offset for a certain time on a certain date. The offset may or may not change for DST or have been changed during history.
The named timezone Europe/London for example will have a different offset during summer (+01:00) as during winter (+00:00) and Oracle has the definitions on when this DST change happens stored, so it can adjust it at the right date and time.
The named timezone UTC for example is always +00:00.

3)What is the database time zone used for? And what does it not do?

The database time zone is not as important as it sounds. First of all it does not influence functions like sysdate, or systimestamp. These function take their contents (date and time, and in the case of systimestamp also time zone) completely from the OS without any "Oracle" intervention. This is further explained in the FAQ on Dates: Note:227334.1.

The only function of the database time zone is that it functions as a time zone in which the values of the "TIMESTAMP WITH LOCAL TIME ZONE" (TSLTZ) datatype are normalized to the current database timezone when they are stored in the database. However, these stored values are always converted into the session time zone on insert and retrieval, so the actual setting of the database time zone is more or less immaterial.
The timezone used for a session is the session timezone and is defined on the client side, not database side - see points 13) and 14) below.

The dbtimezone should be set to an offset (+00:00 , -05:00 or +09:00 for example) or a static time zone that is not affected by DST (like UTC or GMT ) , NOT to a named timezone that is affected by DST (Like Europe/Brussels or US/Central) .

A common misconception is that the database timezone needs to be "your" timezone. This is NOT true. The database timezone has NO relation with "where" the server is located.
There is NO advantage whatsoever in using your timezone or a named timezone as database timezone.
The best setting for dbtimezone is simply +00:00 (or any other OFFSET like -09:00, +08:00, ...), if your current dbtimezone value is an OFFSET then please leave it like it is.

If the requirement is to find out the server OS timezone then do not use dbtimezone to find this out.
Store the value in an application table or one can query systimestamp

[celcsol1]/grdbms/64bit/app/oracle/product/1210> export TZ=UTC
[celcsol1]/grdbms/64bit/app/oracle/product/1210> date
Mon Sep 9 15:13:58 UTC 2013
[celcsol1]/grdbms/64bit/app/oracle/product/1210> sqlplus / as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 15:14:07 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> select to_char(systimestamp ,'TZH:TZM') from dual;

TO_CHA
------
+00:00

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
[celcsol1]/grdbms/64bit/app/oracle/product/1210> export TZ=America/New_York
[celcsol1]/grdbms/64bit/app/oracle/product/1210> date
Mon Sep 9 11:14:29 EDT 2013
[celcsol1]/grdbms/64bit/app/oracle/product/1210> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Sep 9 11:14:35 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> select to_char(systimestamp ,'TZH:TZM') from dual;

TO_CHA
------
-04:00

Note that above is for a local ORACLE_SID connection, for a REMOTE (listener) session to use a new TZ you need to restart the db and listener with that TZ value ( see Note 227334.1 Dates & Calendars - Frequently Asked Questions / 9) Why is my SYSDATE / SYSTIMESTAMP time not the same as my system clock on Unix?) and we assume here using UTC as OS timezone gives a correct UTC time.

An example of wrong result when using a named timezone that has DST as dbtimezone:

note: when testing with DBTIMEZONE related results (like TSLTZ data ) do not use a session that is used to start a database. reconnect before testing.

-- set session format settings
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD' NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS' NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
-- Get the database time zone
select dbtimezone from dual;
-- Set the session time zone to Europe/Brussels
alter session set time_zone = 'Europe/Brussels';
-- insert two Europe/Brussels timestamp values equivalent to
-- 30 minutes before the DST transition on 06 Nov 2011 for America/New_York and
-- 30 minutes after the DST transition on 06 Nov 2011 for America/New_York
-- using Europe/Brussels local time.
drop table tz_test purge;
create table tz_test(coltsltz timestamp with local time zone);
INSERT INTO tz_test VALUES (CAST(to_date('06-11-2011 06.30.00','DD-MM-YYYY HH24.MI.SS')AS TIMESTAMP WITH LOCAL TIME ZONE));
INSERT INTO tz_test VALUES (CAST(to_date('06-11-2011 07.30.00','DD-MM-YYYY HH24.MI.SS')AS TIMESTAMP WITH LOCAL TIME ZONE));
-- check result in Europe/Brussels
select coltsltz from tz_test;
-- check result in UTC
alter session set time_zone='+00:00';
select coltsltz from tz_test;
-- check result in New York time
alter session set time_zone='America/New_York';
select coltsltz from tz_test;
select coltsltz at time zone 'America/New_York' from tz_test;

result for database using +00:00 as dbtimezone (correct)

SQL> select dbtimezone from dual;

DBTIME
------
+00:00
SQL> -- check result in Europe/Brussels
SQL> select coltsltz from tz_test;

COLTSLTZ
-----------------------------------------------------
06*11*2011 06:30:00
06*11*2011 07:30:00

SQL> -- check result in UTC
SQL> alter session set time_zone='+00:00';

Session altered.

SQL> select coltsltz from tz_test;

COLTSLTZ
-----------------------------------------------------
06*11*2011 05:30:00
06*11*2011 06:30:00

SQL> -- check result in New York time
SQL> alter session set time_zone='America/New_York';

Session altered.

SQL> select coltsltz from tz_test;

COLTSLTZ
-----------------------------------------------------
06*11*2011 01:30:00
06*11*2011 01:30:00

SQL> select coltsltz at time zone 'America/New_York' from tz_test;

COLTSLTZATTIMEZONE'AMERICA/NEW_YORK'
--------------------------------------------------------------------
06/11/2011 01:30:00 AMERICA/NEW_YORK EDT
06/11/2011 01:30:00 AMERICA/NEW_YORK EST

result for database using America/New_York as dbtimezone (not correct) 

SQL> select dbtimezone from dual;

DBTIMEZONE
----------------
America/New_York
SQL> -- check result in Europe/Brussels
SQL> select coltsltz from tz_test;

COLTSLTZ
---------------------------------------------------------
06*11*2011 07:30:00
06*11*2011 07:30:00

SQL> -- check result in UTC
SQL> alter session set time_zone='+00:00';

Session altered.

SQL> select coltsltz from tz_test;

COLTSLTZ
---------------------------------------------------------
06*11*2011 06:30:00
06*11*2011 06:30:00

SQL> -- check result in New York time
SQL> alter session set time_zone='America/New_York';

Session altered.

SQL> select coltsltz from tz_test;

COLTSLTZ
---------------------------------------------------------
06*11*2011 01:30:00
06*11*2011 01:30:00

SQL> select coltsltz at time zone 'America/New_York' from tz_test;

COLTSLTZATTIMEZONE'AMERICA/NEW_YORK'
--------------------------------------------------------------------
06/11/2011 01:30:00 AMERICA/NEW_YORK EST
06/11/2011 01:30:00 AMERICA/NEW_YORK EST

4)How can I check the database time zone?

The DBTIMEZONE SQL function returns the value of the database time zone:

SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIMEZONE
--------------
+00:00

5)Why does the database time zone not change at the start or end of DST?

As mentioned pioint 4) above, the database time zone is only used as a time zone in which stored TSLTZ values are normalized.
So the value of the dbtimezone should in fact not change. Because this is the only task for the database time zone it should not be used for any other things (like trying to determine things about the OS time zone, there are other methods for this).

6)Why is the database time zone the value it is, and how can I change it?

First note that the database time zone is only relevant for TIMESTAMP WITH LOCAL TIME ZONE columns. If you have none of these columns in the database then the database time zone has no function at all and there certainly is no need to worry about it's setting.
The database time zone is usually only set at creation time of the database:

SQL> CREATE DATABASE...
     SET TIME_ZONE='+00:00';

If not specified with the CREATE DATABASE statement, the database time zone defaults to the server's O/S time zone offset.
Note that if your server runs in a time zone affected by DST, then the database time zone would default to whatever the current offset is at the time of database creation, so you can have databases created with the same script on the same server with a different database time zone.

After database creation the ALTER DATABASE SET TIME_ZONE statement can be used to change the database time zone. This will only work if there are no TSLTZ values already stored in the database or an ORA-02231 (9i) or ORA-30079 will be seen:

SQL> ALTER DATABASE SET TIME_ZONE = '+00:00';

The change will not take effect until the database is restarted.

A common misconception is that the database timezone needs to be "your" timezone. This is NOT true.
The database timezone has NO relation with "where" the server is located. There is NO advantage whatsoever in using your timezone or a named timezone as database timezone.
The best setting for dbtimezone is simply +00:00 (or any other OFFSET like -09:00, +08:00, ...), if your current dbtimezone value is an OFFSET then please leave it like it is.

Oracle uses by default no TSLTZ columns in the data dictionary, the next select gives all TSLTZ columns. If this gives "no rows" then an "ALTER DATABASE SET TIME_ZONE..." can be done.

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -'   || c.data_type || ' ' col
  from dba_tab_cols c, dba_objects o
 where c.data_type like '%WITH LOCAL TIME ZONE'
    and c.owner=o.owner
   and c.table_name = o.object_name
   and o.object_type = 'TABLE'
order by col
/

7) Why do I get ORA-02231 (9i) or ORA-30079 ( 10g and up) when I try to change the database time zone?

You can only change the database time zone if you have no TIMESTAMP WITH LOCAL TIME ZONE columns in the database otherwise ORA-02231: missing or invalid option to ALTER DATABASE (in 9i) or ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns (in 10g and up) will be seen.
To change the DBTIMEZONE for an database that has already TIMESTAMP WITH LOCAL TIME ZONE columns one need to
a) export all tables that have TIMESTAMP WITH LOCAL TIME ZONE columns

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col
from dba_tab_cols c, dba_objects o
where c.data_type like '%WITH LOCAL TIME ZONE'
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = 'TABLE'
order by col
/

b) truncate or drop those tables
c) change the DBTIMEZONE making sure the are no tables with TSLTZ in the RECYCLEBIN and restart the database

SQL>PURGE DBA_RECYCLEBIN
/
SQL> ALTER DATABASE SET TIME_ZONE = '+00:00';
SQL> SHUTDOWN

d) import the exported table again.

8) What is SYSTIMESTAMP ?

You could say that SYSTIMESTAMP is "sysdate with time zone information added".
SYSTIMESTAMP is the timestamp on the server machine itself and is obtained on Unix platforms by calling " GetTimeOfDay " and on Windows by calling "GetSystemTime" to get the servers local time.
This means that SYSTIMESTAMP, just like SYSDATE depends on Unix platforms on the UNIX time configuration (= Unix TZ variable) for the Unix session when the database and listener where started.
The precision is platform dependant, on most Unix platforms it's microseconds (10-6) on Windows this is Milliseconds (10-3). The output is defined by NLS_TIMESTAMP_TZ_FORMAT in NLS_SESSION_PARAMETERS.

SQL> SELECT SYSTIMESTAMP FROM dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
22-DEC-08 16.59.09.244000 +01:00

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS.FF9 TZR TZD';

Session altered.

SQL> SELECT SYSTIMESTAMP FROM dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
22/12/2008 16:59:32.097000000 +01:00

The servers local timezone is usually (= on Unix) controlled by the TZ environment variable and localtime returns a time already adjusted for the servers local timezone.
The SYSTIMESTAMP output has an offset from UTC but is not defined to include an actual named timezone. Mapping this offset to a timezone, or reinterpreting the OS TZ setting would be very hard and error prone and so the code sticks to the absolute offset between UTC and the local time.
You can see this by issuing:

SQL> SELECT EXTRACT( timezone_region from systimestamp ) FROM dual;

EXTRACT(TIMEZONE_REGIONFROMSYSTIMESTAMP)
----------------------------------------------------------------
UNKNOWN

It will show UNKNOWN and this is expected behavior.

That SYSTIMESTAMP returns a OFFSET will not provoke any problems seen:

a) if you want to know the time in a particular time zone you can use "AT TIME ZONE".

SQL> SELECT systimestamp AT TIME ZONE 'Canada/Eastern' FROM DUAL;

SYSTIMESTAMPATTIMEZONE'CANADA/EASTERN'
------------------------------------------------------------------
19-NOV-07 09.47.55.099000 CANADA/EASTERN

b) if you are wanting to see the timestamp in the sessions timezone then you should use CURRENT_TIMESTAMP.

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='YYYY.MM.DD HH24:MI:SS TZR TZD';

Session altered.

SQL> ALTER SESSION SET TIME_ZONE = 'Canada/Eastern';

Session altered.

SQL> SELECT current_timestamp FROM dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
2007.11.19 09:48:18 CANADA/EASTERN EST

c) If the Unix TZ variable is set to a DST aware value (= pure on unix level) then systimestamp will return the correct time and timezone regardless of DST is in effect or not.

If the Unix TZ variable is NOT set to a named timezone (following the DST rules you want) then of course it will return a incorrect result, but this is basically the same issue as with "sysdate" - we simply ask the OS to give us the current time, but with SYSTIMESTAMP Oracle also includes (unlike sysdate) the timezone OFFSET.

If "SYSDATE" gives the wrong time then "SYSTIMESTAMP" will also do so, please follow the steps in note 1627439.1 How to diagnose wrong time ( SYSDATE and SYSTIMESTAMP) after DST change , server reboot , database restart or installation when connecting to a database on an Unix server

Please note that the DBTIMEZONE has NOTHING to do with this, changing DBTIMEZONE will NOT solve SYSDATE or SYSTIMESTAMP returning a wrong time.

9) What is the difference between CURRENT_DATE, LOCALTIMESTAMP and CURRENT_TIMESTAMP?

They all depend on the session timezone, which is defined on the CLIENT side, not server side.

CURRENT_DATE returns the current date and time in the session time zone in a value of datatype DATE.
LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP.
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE.

The sessions NLS_DATE_FORMAT defines the output format of a DATE, NLS_TIMESTAMP_FORMAT defines the output format of a TIMESTAMP, the NLS_TIMESTAMP_TZ_FORMAT defines the output format of a TIMESTAMP WITH TIME ZONE.

ALTER SESSION SET TIME_ZONE = '-05:00';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD' NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS' NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

-- gives for example:

SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FRO
M DUAL;
SESSIONTIMEZONE
---------------------------------------------------------------------------
CURRENT_DATE
-------------------
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
-05:00
28-10-2009 06:47:19
28/10/2009 06:47:18 -05:00
28*10*2009 06:47:18

-- and when doing an alter of the session timezone

ALTER SESSION SET TIME_ZONE = '-08:00';

-- gives for example:

SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP FRO
M DUAL;
SESSIONTIMEZONE
---------------------------------------------------------------------------
CURRENT_DATE
-------------------
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
-08:00
28-10-2009 03:47:49
28/10/2009 03:47:49 -08:00
28*10*2009 03:47:49

more sample output is found in note 1624595.1 Clarification CURRENT_DATE, CURRENT_TIMESTAMP, LOCALTIMESTAMP ,SYSDATE, SYSTIMESTAMP, FIXED_DATE

10)Which time zones are available in Oracle?

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

Please see point " J) What timezones are known in Oracle Database?" in Note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches

11)How can I change between the large and the basic time zone files?

This is defined by the ORA_TZFILE, in most cases there is no need to set this parameter. The performance difference between both files is not very great unless the application uses extensive amount of timezone information.

In most cases the benefit of having more timezones available outweights any (small) performance difference, the default value (= ORA_TZFILE not set) is the best choice.

Until patchset 9.2.0.5 the 'basic' (small) time zone file is the default time zone file that is loaded by the database. From 9.2.0.5 (and all versions higher than 9) the large file gets loaded by default. To switch between the time zone files, and overwrite the default, complete the following tasks:

Shut down the database.
Set the ORA_TZFILE environment variable to the full path name of the time zone file you want to use. On Unix this needs to be a environment variable, on Windows this needs to be set in the relevant HOME under HKEY_LOCAL_MACHINE/Software/Oracle in the registry.
Restart the database.

(On Windows this can also be set in the registry, in the correct HOME location)
Be aware that you can neither create nor alter these time zone definition files, they are binary files that only Oracle can build and provide.

$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat is the "large" file $ORACLE_HOME/oracore/zoneinfo/timezone.dat is the "small" file.

Please note: In Oracle 11.2 and up the ORA_TZFILE usage and the naming of the files changed, this is documented in the docset under "Choosing a Time Zone File" in Oracle� Database Globalization Support Guide
11g Release 2 (11.2), Chapter 4. and Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST

In 11.2 and up there are no timezlrg.dat and timezone.dat, this is normal and intended.
Do NOT make any symbolic links for timezlrg.dat and timezone.dat or copy any of the files in \oracore\zoneinfo\ and rename them to timezlrg.dat and timezone.dat
in 11.2 and up there should be NO timezlrg.dat and timezone.dat in $ORACLE_HOME/oracore/zoneinfo/ (unix) or %ORACLE_HOME%\oracore\zoneinfo\ (windows)

Note that the actual result in 11.2 , when compared to 11.1 or below, will not be very different when NOT defining ORA_TZFILE, Oracle RDBMS still uses the large file by default and will use the higest DST version available in $ORACLE_HOME/oracore/zoneinfo when creating the database.

Note that in 11.2 (and up) the database need to explicit update stored timezone data before a new DST version is used for these columns. See the Docset for more information.

12)Which time zone file is currently used?

Beside checking the ORA_TZFILE parameter as described above, you can easily check whether you are using the large or small time zone file with the following query:

SQL> SELECT COUNT(*) FROM v$timezone_names;

For the default 'large' time zone file:
COUNT(*)
----------
1393         (1250 in Oracle9)

For the 'small' time zone file:
COUNT(*)
---------
636          (616 in Oracle9)

13)How can I check the session time zone?

The SESSIONTIMEZONE sql function returns the value of the current session's time zone:

SQL> SELECT SESSIONTIMEZONE FROM DUAL;
SESSIONTIMEZONE
---------------
+01:00

14)How can I set the session time zone?

By default the session timezone is set to the OFFSET of the clients (!) operating system timezone value/setting at connection time.
The client asks the client Os what the current offset is to UTC and then , during the connection/session creation fase , does an alter session based on this.

The session time zone can be explicitly set to:

the client OS time zone
Database time zone
An absolute offset
A named region ( = SELECT unique(TZNAME) FROM v$timezone_names ; )

This can be done in 2 ways, the first method consists to use one of the following ALTER SESSION SET TIME_ZONE statements:

SQL> ALTER SESSION SET TIME_ZONE = local;
SQL> ALTER SESSION SET TIME_ZONE = dbtimezone;
SQL> ALTER SESSION SET TIME_ZONE = '-05:00';
SQL> ALTER SESSION SET TIME_ZONE = 'Europe/London';

The alternative method is to set the (client) operating system environment variable ORA_SDTZ:

ORA_SDTZ = 'OS_TZ' | 'DB_TZ' | '[+ | -] hh:mm' | 'timezone_region' ->  ( = SELECT unique(TZNAME) FROM v$timezone_names ; )

$ ORA_SDTZ='America/Chicago'
$ export ORA_SDTZ
$ ORA_SDTZ='-05:00'
$ export ORA_SDTZ

If you do not want to set ORA_SDTZ on all client machines, but still want to be in control over the session time zone settings for all sessions, then consider using a logon trigger to the database, in which you can set the session time zone specifically through ALTER SESSION (as per point a above).

Note that the session timezone defaults to an offset ( like +05:00), even if the unix TZ variable or Windows timezone region is set to a named TZ.
If you need the session timezone to be a named timezone then you need to set ORA_SDTZ client (!) environment (or registry on windows) with an Oracle TZ name ( = SELECT unique(TZNAME) FROM v$timezone_names ; ).
If no ORA_SDTZ is set to or no alter session is done to use a named timezone with dst as sessiontimezone then the OFFSET for the session timezone is the offset the clients (!) operating system timezone value/setting at connection time. this implies that if the connection is made before the client OS "switched DST" the offset after the DST change in that session will still be the "old" offset. This is not a bug. Reconnecting will use the new offset setting of the client OS.
Note that "LOCAL" has 2 meanings by the way:
* when used to set the session timezone ( alter session set time_zone local; ) it sets the session timezone to the current *client* OS OFFSET (if no ORA_SDTZ variable is set) or the timezone define on that client by the ORA_SDTZ variable
* when used to do an "at local" conversion ( select to_timestamp_tz('2015-04-15 00:00:00 UTC','yyyy-mm-dd hh24:mi:ss tzr') at local from dual;) then the "local" means the current session timezone (= meaning that if you do an alter session of the session timezone this new value will be used, not the client timezone)

We do not advice to set the session timezone to the database timezone unless you have a compelling reason to do so. It might be use full for example when your application logic (outside oracle) is doing the mapping to/from the locale timezone to UTC and you are always creating databases using UTC as database timezone .

15)What does "ORA-1804: failure to initialize timezone information" mean?

ORA-1804 indicates that the database cannot load the time zone file it wants to use. This error will come up every time that a named time zone is used and the time zone file is not available or corrupt.
For more details on this please see Note 365558.1 ORA-1804: Failure to Initialize Timezone Information

16) Can I rely on Oracle time zones definitions?

see Note 412160.1Updated DST transitions and new Time Zones in Oracle Time Zone File patches , point F) Can I rely on the Oracle Timezone data?

17)So what do I do if the Oracle time zone information is not up to date?

The Daylight Saving Transition data for regions that change their Daylight Saving Transition rules frequently and during irregular periods, such as Israel and Brazil, are quite frequently not up to date. Other regions might also have made changes that means that the time zone information in the database is "out of synch" with the real world. Obviously this may affect the accuracy of the time data for these regions stored in the TIMESTAMP WITH (LOCAL) TIME ZONE data type.
If you plan to use the TIMESTAMP WITH TIME ZONE data type, and you need to manage time data from these regions, you should use the time zone offset (for example, -05:00), instead of the time zone region name when specifying timestamp literals. When using offsets you are in complete charge about when you change this.

18)How can I retrieve the time zone offset corresponding to a time zone region?

The TZ_OFFSET() sql function returns the time zone offset displacement to the input time zone region.

SQL> SELECT TZ_OFFSET('US/Pacific') FROM DUAL;

TZ_OFFS
-------
-07:00

The returned offset depends on the date this statement is executed. For example, in the 'US/Pacific' time zone, it may return '-07:00' or '-08:00' whether daylight saving is in effect or not.

To know the offset at a certain time you need to check the deviation from UTC like this:

SQL> select to_timestamp_tz('2007-01-28 00:00:00 Australia/Melbourne',
     'yyyy-mm-dd hh24:mi:ss tzr') at time zone '00:00' from dual;

TO_TIMESTAMP_TZ('2007-01-2800:00:00AUSTRALIA/MELBOURNE','YYYY-MM-DDHH24:MI:
---------------------------------------------------------------------------
27-JAN-07 13.00.00.000000000 +00:00

-- here you see 11 hour SUBSTRACTED, wich means the TZ offset is PLUS 11 UTC

SQL> select to_timestamp_tz('2007-06-28 00:00:00 Australia/Melbourne',
     'yyyy-mm-dd hh24:mi:ss tzr') at time zone '00:00' from dual;

TO_TIMESTAMP_TZ('2007-06-2800:00:00AUSTRALIA/MELBOURNE','YYYY-MM-DDHH24:MI:
---------------------------------------------------------------------------
27-JUN-07 14.00.00.000000000 +00:00

-- here you see 10 hour SUBSTRACTED, wich means the TZ offset is PLUS 10 UTC

SQL> select to_timestamp_tz('2007-01-28 00:00:00 US/Pacific',
     'yyyy-mm-dd hh24:mi:ss tzr') at time zone '00:00' from dual;

TO_TIMESTAMP_TZ('2007-01-2800:00:00US/PACIFIC','YYYY-MM-DDHH24:MI:SSTZR')AT
---------------------------------------------------------------------------
28-JAN-07 08.00.00.000000000 +00:00

-- here you see 8 hour ADDED, wich means the TZ offset is MINUS 8 UTC

SQL> select to_timestamp_tz('2007-06-28 00:00:00 US/Pacific',
     'yyyy-mm-dd hh24:mi:ss tzr') at time zone '00:00' from dual;

TO_TIMESTAMP_TZ('2007-06-2800:00:00US/PACIFIC','YYYY-MM-DDHH24:MI:SSTZR')AT
---------------------------------------------------------------------------
28-JUN-07 07.00.00.000000000 +00:00

-- here you see 7 hour ADDED, wich means the TZ offset is MINUS 7 UTC

You can also use the sessiontimezone and dbtimezone function in combination with tz_offset:

SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;
TZ_OFFS TZ_OFFS
------- -------
+01:00  +00:00

20)How can I retrieve the time zone region (and abbreviation) for a given offset?

There is no generic solution to this because many different time zones 'map' onto the same offsets, and usually they do so at different parts of the year. Even if we take the actual timestamp into account we still cannot do this mapping

For example if you only have a offset of +01:00 without a corresponding time zone then there is no way of knowing whether this is the time in the UK during Summertime/DST (BST) or the time in Central/Western Europe outside of DST.

However, even if we do know the timestamp to go with the offset we can have problems. For example "01-OCT-2006 14:00:00 +02:00" would map onto "Central European Summer Time", and therefore be valid in all time zones like Europe/Paris, Europe/Berlin, Europe/Madrid, etc. etc. In itself there is no way of automatically choosing between these, but that's not much of a problem because their DST end-dates are at the same time at the end of October, so choosing one over the other is not going to hurt much. The bigger problem is that this same timestamp is also valid for Africa/Cairo, because that has just come out of DST and is back to it's standard offset of +02:00.

The recommended solution for this would be to create your own function for this mapping, since you will usually know that your users are only using a limited number of time zones anyway. Therefore for your own use you will know if "01-OCT-2006 14:00:00 +02:00" is more likely to map to Europe/Rome than to Africa/Cairo, but this is not something that the Oracle database can automatically determine for you.

21) Time zone names vs. Time zone abbreviations

Time zone abbreviations are only relevant when we want to specify that a specific time zone name should be interpreted as it's "DST" version or it's "standard time" version. Certain "words" are valid as a time zone name, and as a time zone abbreviation - which can lead to issues understanding what's going on, but is important to realize this. An example of this is "CET". This can be used as a time zone abbreviation to for example the Europe/Berlin time zone name to specify we mean standard time during the "end of DST overlap hour". However, CET can also be used as a real time zone name, just like "Europe/Berlin", "Europe/Paris" etc.
Time zone names are DST aware. Any timestamp using a time zone name will be automatically adjusted for DST if needed (if DST doesn't exist in a region then obviously it's not used, for example in time zone 'Japan'). Perhaps other than expected the "CET" time zone name, just like EST, MST, PST etc. is defined in Oracle as DST aware! Note that we're talking about "time zone names" here, not "time zone abbreviations". Despite the fact that these 3 letters are obviously an abbreviation in the literal sense of the word. This might at first seem counter-intuitive, since you might expect CET (EST/MST/PST...) to be static. However, the reason for this is usability. Most users using "Central European Time" expect that to reflect the "clock time", and not a notion of "standard time", which is different to "clock time" during the summer.
As stated this might initially seem "strange" to time zone experts, but when you realize the way it's been set up there should not be any problems using it. Afterall, if you want to use a non-DST time zone then you can always use an offset like +01:00.

In general good "usability advise would be to:

Use full time zone names ("Europe/Berlin", "Europe/London", "America/Detroit", etc. etc.) when DST-awareness is wanted
Use offsets, or GMT and UTC when you want to make sure there is no DST involved
Use no "3 letter" time zone names apart from UTC (or GMT), because of the confusion with time zone abbreviations.
Use time zone abbreviations only when needed to specify the correct time during the overlap hour.

22)How can I compute the difference between two timestamp values?

You can simply subtract 2 timestamps from each other to get a interval, for example this calculates how long it is since/before lunch on Christmas day:

declare
  duration interval day(6) to second(6);
  v_start  timestamp := to_timestamp('25-DEC-2003 14:00:00.000000', 'DD-MON-YYYY HH24:MI:SSxFF');
  v_end    timestamp(6) := sysTimestamp;
begin
  duration := v_end - v_start;
  dbms_output.put_line ('Now: '||to_char(v_end,'DD-MON-YYYY HH24:MI:SSxFF'));
  dbms_output.put_line ('Difference: '|| to_char(duration));
end;
/

And it works the same in sql:

create table temp(start_TS Timestamp(6), duration interval day(6) to second(6)  );
Insert into temp values
  (to_timestamp('25-DEC-2003 14:00:00.000000', 'DD-MON-YYYY HH24:MI:SSxFF'),
  (sysTimeStamp - to_timestamp('25-DEC-2003 14:00:00.000000', 'DD-MON-YYYY HH24:MI:SSxFF'))   );

Also usefull : note 337698.1 TIMECALC: A package to simplify timestamp calculations

23) How can I see the abbreviation for my time zone?

In some cases you can be working with a time zone like "America/New_York" but you are interested in the abbreviation "EST" or "EDT" (depending on DST).
In the v$timezone_names view you can see all the abbreviations associated with the time zone, like EST and EDT etc., but you cannot see what the "current" correct abbreviation is.
The way to get this from the database is by using the TO_CHAR function with he TZD format mask. If you use this on a TIMESTAMP WITH TIME ZONE you will receive the abbreviation associated with the time zone.
If you already work with a TSTZ then you can simply use the TO_CHAR, if you do not have a TSTZ yet, then you can construct this using the FROM_TZ and AT TIME ZONE functions.
For example, lets say your time zone is America/New_York and you want to make sure you get the correct EST/EDT dependent on the current date/time. In this case you can take the current time, cast that into a TSTZ, and do a TO_CHAR on it:

select TO_CHAR(FROM_TZ(sys_extract_utc(systimestamp),'UTC') AT TIME ZONE 'America/New_York','TZD') FROM DUAL;

24)What are reasons to get ORA-1882?

These are known issues who might provoke an ORA-01882:
* note 389804.1 Range Partition Splitting Fails with ORA-01882. Fixed in 12.1.0.1 and 11.2.0.2
* using dblink or insert or create index: Bug 16731148 - ORA-01882 WHEN SELECTING TIMESTAMP LITERALS VIA DB-LINK OR USING INSERT . Fixed in 12.1.0.2 and PSU 11.2.0.4.2

ORA-1882 is normally triggered when you specify a incorrect time zone. For example:

select * from scott.emp where to_timestamp(hiredate) = TIMESTAMP'2003-04-07 00:00:00 QWE';

Because the time zone QWE that is used in the TIMESTAMP literal does not exist you receive ORA-1882.

Between DSTv3 and higer timezone versions a few timezones changed from internal ID they may give ora-1882 : note 414590.1 Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade

It is also possible your RDBMS DST version simply does not know the timezone, for example trying to set session timezone to :

SQL> alter session set time_zone='Africa/Dar_es_Salaam';
ERROR:
ORA-01882: timezone region not found

Will fail on an DSTv4 system since Dar-es-Salaam is only added in DSTv5. See point I) in NOTE 412160.1 - Updated DST transitions and new Time Zones in Oracle Time Zone File patches.

You can also receive ORA-1882 when you query a timestamp, or a table that contains a timestamp, which uses a time zone which is not valid in the client's time zone files. Both the client's and the server's time zone files need to "agree" on a time zone before it's passed as valid. Note 397766.1 describes a particular problem about the time zone UTC which is only known in Oracle10, and not in Oracle9. Therefore querying a Oracle10 database from a Oracle9 client can cause a ORA-1882 when the time zone UTC is used.
So first check with a client using the same version and RDBMS DST patch applied to the client as found on the server.

This also applies to using ORA_TZFILE and when this is set to the "basic" timezone file, this does not know all timezones defined in the timezlrg.dat file (wich is used by default). There is no real reason to use the "basic" file and we suggest to leave ORA_TZFILE unset.

It is also possible to get ORA-1882 if case of a syntax error in the TIMESTAMP. Most syntax errors in TIMESTAMPS receive ORA-01861 but if the syntax error is on or after the 'seconds' part, it will be interpreted as a time zone. That time zone obviously does not exist so ORA-1882 is raised. For example, you accidentally add a letter 'o' instead of a number '0':

select * from scott.emp where to_timestamp(hiredate) = TIMESTAMP'2003-04-07 00:00:00.00o';

This syntax error also shows up if you try to use a 'comma' as the decimal separator in a TIMESTAMP literal. In 'normal' numbers you can set NLS_NUMERIC_CHARACTERS to set your preferences for the decimal separator and the thousands separator. However, in TIMESTAMP literals you should ALWAYS use a 'dot' as the decimal separator, regardless of the setting of NLS_NUMERIC_CHARACTERS. For example:

select * from scott.emp where to_timestamp(hiredate) = TIMESTAMP'2003-04-07 00:00:00,00';

This will always fail, whatever the NLS settings are.

Using a Timezone Abbriviation (select TZABBREV from V$TIMEZONE_NAMES;) with TO_TIMESTAMP_TZ or FROM_TZ will also give a ORA-1882. You can only use Timezone Names (select TZNAME from V$TIMEZONE_NAMES;)

25)How does Oracle Forms work with time zones?

There are some specific variables you can set in Oracle Forms 9i to work with the Oracle time zone support. These are specifically documented in the following note:
Note 207822.1 Time Zone Support in Forms 9i

26)I have some troubles with DBMS_SCHEDULER and timezones.

Please see Note 467722.1 DBMS_SCHEDULER And Time Zones ( DST ) Explained.
For when running trough DBMS_SCHEDULER a job and the output gives the wrong time.
Or your jobs run at wrong time after DST has changed.

27)Where can I find more external information about time zones?

There are quite a few websites that give very good and interesting background information about calendars. The information on these websites often goes a lot further than the oracle scope and these websites are in no way linked with Oracle. We have only included them for your information in case you're interested in reading more background information about these subjects:

Website about the TZ database, containing many links to to sources for TZ and DST data and information:
http://www.twinsun.com/tz/tz-link.htm

For more info on the time zones:
http://wwp.greenwichmeantime.com/home.htm

A interesting website about the history of Daylight Saving Time:
http://webexhibits.org/daylightsaving/index.html

Benjamin Franklin's Essay on Daylight Saving
http://webexhibits.org/daylightsaving/franklin3.html

Local times around the World:
http://swissinfo.net/cgi/worldtime/

2 World Time Zone Maps:
http://aa.usno.navy.mil/faq/docs/world_tzones.html
http://www.worldtimezone.com/

28) Why does using Datetime Arithmetic on datatypes having timezone information seams to give incorrect result ?

It's often not known that using Datetime Arithmetic in most cases returns a DATE dataype. When using for example:

SQL> select to_timestamp_tz('25/10/2009 02:20:00 UTC','DD/MM/YYYY HH24:MI:SS TZR') +5 +30/48 from dual;
TO_TIMESTAMP_TZ('25
-------------------
30-10-2009 17:20:00

SQL> select systimestamp +5 +30/48 from dual;
SYSTIMESTAMP+5+30/4
-------------------
01-11-2009 07:39:16

the returned dataype is a DATE, wich is a datatype that has no timezone information . This can be easely seen when using a slightly different format in the session for DATE, TIMESTAMP and TIMESTAMP WITH TIMEZONE datatypes.

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD'  NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS' NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';

Or to use the dump function to check the returned datatype.

SQL> select dump(sysdate,1016) from dual;
   
    DUMP(SYSDATE,1016)
    ---------------------------------------------------------------
   
    Typ=13 Len=8: d9,7,a,17,d,c,35,0
   
 SQL> select dump( systimestamp,1016) from dual;
   
    DUMP(SYSTIMESTAMP,1016)
    ---------------------------------------------------------------
   
    Typ=188 Len=20: d9,7,a,17,c,c,36,0,0,7c,31,25,1,0,5,0,0,0,0,0
   
 SQL> select dump(sysdate +5 +30/48,1016) from dual;
   
    DUMP(SYSDATE+5+30/48,1016)
    ---------------------------------------------------------------
   
    Typ=13 Len=8: d9,7,a,1d,4,c,39,0

-- here you see the "type" changing from 188 to 13 (= date )   

 SQL> select dump( systimestamp +5 +30/48,1016) from dual;
   
    DUMP(SYSTIMESTAMP+5+30/48,1016)
    ---------------------------------------------------------------
   
    Typ=13 Len=8: d9,7,a,1d,4,c,3a,0

This is documented in the Oracle Database SQL Language Reference 11g Release 2 (11.2), chapter Data Types , Datetime/Interval Arithmetic

If one operand is a DATE value or a numeric value, neither of which contains time zone or fractional seconds components, then:

Oracle implicitly converts the other operand to DATE data. The exception is multiplication of a numeric value times an interval, which returns an interval.
If the other operand has a time zone value, then Oracle uses the session time zone in the returned value.
If the other operand has a fractional seconds value, then the fractional seconds value is lost.

The solution is to use intervals by using functions like NUMTODSINTERVAL or NUMTOYMINTERVAL.

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD'  NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS' NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
ALTER SESSION SET TIME_ZONE = '+02:00';
set serveroutput on
declare
v_t1  timestamp with time zone;
v_t2  timestamp with time zone;
begin
v_t1 := from_tz(cast(to_date('2013-11-04', 'YYYY-MM-DD') as timestamp), '-06:00');
dbms_output.put_line(v_t1);
v_t2 := v_t1 + 35/1440;
dbms_output.put_line(v_t2);
v_t2 := v_t1 + 30;
dbms_output.put_line(v_t2);
v_t2 := v_t1 +  NUMTODSINTERVAL(35, 'MINUTE');
dbms_output.put_line(v_t2);
v_t2 := v_t1 +  NUMTOYMINTERVAL(1, 'MONTH');
dbms_output.put_line(v_t2);
end;
/ 
04/11/2013 00:00:00 -06:00
-- here you see the SESSION timezone beeing used , not the timezone of the original TSTZ
04/11/2013 00:35:00 +02:00
04/12/2013 00:00:00 +02:00
-- here the timezone of the TSTZ is retained
04/11/2013 00:35:00 -06:00
04/12/2013 00:00:00 -06:00

29) When comparing TSLTZ and TSTZ output the result around or after DST times seams wrong.

Often this mistake comes from the result of previous point (or similar) or a misunderstanding of the TSLTZ dataype. See the next examples that uses some time around 25/10/2009 02:20:00, which is the DST transition period in Belgium. Europe/Brussels is UTC +02:00 during summer ( CEST ) and UTC +01:00 during winter ( CET ) .

Also usefull : Note 337698.1 TIMECALC: A package to simplify timestamp calculations

-- you do some inserts based on sysdate and systimestamp and and a Date rithmetic like:
-- insert into timetest values (sysdate +5 +30/48, systimestamp +5 +30/48, systimestamp +5 +30/48) ;

-- What actually happens is an insert of the DATE datatype which is
-- implicit casted to the column datatype using the SESSIONTIMEZONE.

alter session set time_zone = 'Europe/Brussels' ;
Drop table timetest;
create table timetest (datum date, tsltz timestamp with local time zone, tstz timestamp with time zone) ;
insert into timetest values (to_date('25/10/09 00:50:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 00:50:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 00:50:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 01:50:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:50:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 01:50:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 02:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 02:20:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 02:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 02:50:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 02:50:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 02:50:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values (to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'),to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
commit;
   
-- now let's select the result of this with some formatting in the same timezone
   
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
select * from timetest order by TSLTZ ;
   
--  which gives
   
--  SQL> select * from timetest order by TSLTZ ;
--  DATUM
--  -------------------
--  TSLTZ
--  --------------------------------------------------------------------
--  TSTZ
--  --------------------------------------------------------------------
--  25-10-2009 00:50:00
--  25*10*2009 00:50:00
--  25/10/2009 00:50:00 EUROPE/BRUSSELS CEST 
--  25-10-2009 01:20:00
--  25*10*2009 01:20:00
--  25/10/2009 01:20:00 EUROPE/BRUSSELS CEST
--  25-10-2009 01:50:00
--  25*10*2009 01:50:00
--  25/10/2009 01:50:00 EUROPE/BRUSSELS CEST
--  25-10-2009 02:20:00
--  25*10*2009 02:20:00
--  25/10/2009 02:20:00 EUROPE/BRUSSELS CET
--  25-10-2009 02:50:00
--  25*10*2009 02:50:00
--  25/10/2009 02:50:00 EUROPE/BRUSSELS CET
--  25-10-2009 03:20:00
--  25*10*2009 03:20:00
--  25/10/2009 03:20:00 EUROPE/BRUSSELS CET
--
--  6 rows selected.
   
-- Note the absence of the "double time", this is simply seen a DATE has no
-- idea what timezone it is. hence the SESSION timezone is used when doing the
-- implicit casting to TSTZ or TSLTZ datatype.
-- But when doing the conversion Oracle has no idea if '02:20:00" is
-- CEST or CET, so it has to choose something, CET the second time it's 02:xx.
-- Note also the fundamental difference between TSLTZ - which does NOT store
-- the used timezone (implicit or explicit defined) , the date/time part
-- is converted to the database timezone using the CURRENT value for the
-- (session) timezone - and TSTZ which stores the actual timezone but
-- where no mapping to the session timeone is done when read.
   
-- now let's look at the UTC values:

alter session set time_zone = '+00:00' ;
select datum, tsltz , tstz AT TIME ZONE '+00:00' from timetest order by TSLTZ ;
   
--  DATUM
--  -------------------
--  TSLTZ
--  -------------------------------------------------------
--  TSTZATTIMEZONE'+00:00'
--  -------------------------------------------------------
--  25-10-2009 00:50:00
--  24*10*2009 22:50:00
--  24/10/2009 22:50:00 +00:00
--  25-10-2009 01:20:00
--  24*10*2009 23:20:00
--  24/10/2009 23:20:00 +00:00
--  25-10-2009 01:50:00
--  24*10*2009 23:50:00
--  24/10/2009 23:50:00 +00:00
--  25-10-2009 02:20:00
--  25*10*2009 01:20:00
--  25/10/2009 01:20:00 +00:00
--  25-10-2009 02:50:00
--  25*10*2009 01:50:00
--  25/10/2009 01:50:00 +00:00
--  25-10-2009 03:20:00
--  25*10*2009 02:20:00
--  25/10/2009 02:20:00 +00:00
-- 
--  6 rows selected. 
-- correct result
   
alter session set time_zone = '+02:00' ;
select datum, tsltz , tstz AT TIME ZONE '+02:00' from timetest order by TSLTZ ;
   
--  DATUM
--  -------------------
--  TSLTZ
--  -----------------------------------
--  TSTZATTIMEZONE'+02:00'
--  -----------------------------------
--  25-10-2009 00:50:00
--  25*10*2009 00:50:00
--  25/10/2009 00:50:00 +02:00
--  25-10-2009 01:20:00
--  25*10*2009 01:20:00
--  25/10/2009 01:20:00 +02:00
--  25-10-2009 01:50:00
--  25*10*2009 01:50:00
--  25/10/2009 01:50:00 +02:00 
--  25-10-2009 02:20:00
--  25*10*2009 03:20:00
--  25/10/2009 03:20:00 +02:00 
--  25-10-2009 02:50:00
--  25*10*2009 03:50:00
--  25/10/2009 03:50:00 +02:00
--  25-10-2009 03:20:00
--  25*10*2009 04:20:00
--  25/10/2009 04:20:00 +02:00
--
--  6 rows selected.
   
-- If you do not use the "at timezone" for the tstz colum then you get

select datum, tsltz , tstz from timetest order by TSLTZ ;

--  DATUM
--  -------------------
--  TSLTZ
--  ------------------------------------------------------------------
--  TSTZ
--  ------------------------------------------------------------------
--  25-10-2009 00:50:00
--  25*10*2009 00:50:00
--  25/10/2009 00:50:00 EUROPE/BRUSSELS CEST
--  25-10-2009 01:20:00
--  25*10*2009 01:20:00
--  25/10/2009 01:20:00 EUROPE/BRUSSELS CEST
--  25-10-2009 01:50:00
--  25*10*2009 01:50:00
--  25/10/2009 01:50:00 EUROPE/BRUSSELS CEST   
--  25-10-2009 02:20:00
--  25*10*2009 03:20:00
--  25/10/2009 02:20:00 EUROPE/BRUSSELS CET
--  25-10-2009 02:50:00
--  25*10*2009 03:50:00
--  25/10/2009 02:50:00 EUROPE/BRUSSELS CET
--  25-10-2009 03:20:00
--  25*10*2009 04:20:00
--  25/10/2009 03:20:00 EUROPE/BRUSSELS CET
--
--  6 rows selected.
-- Which is again correct seen the TSLTZ colum is casted
-- to the session timezone (+02:00) but the TSTZ is NOT,
-- a TSTZ is reported in the stored timezone.

-- The TSLTZ behavior is even more clear when assuming next testcase:
-- which clearly illustrates the effect of the session timezone
-- on both insert as selct of a TSLTZ column.

Drop table timetest;
create table timetest (sessionTZ varchar2(100), datum date, tsltz timestamp with local time zone) ;
alter session set time_zone = 'Europe/Brussels';
insert into timetest values ( 'Europe/Brussels', to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values ('Europe/Brussels', to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
alter session set time_zone = '+01:00';
insert into timetest values ( '+01:00', to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values ('+01:00', to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
alter session set time_zone = '+02:00';
insert into timetest values ( '+02:00', to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 01:20:00','DD/MM/YY HH24:MI:SS'));
insert into timetest values ('+02:00', to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'), to_date('25/10/09 03:20:00','DD/MM/YY HH24:MI:SS'));
commit;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
set pages 100
SELECT DBTIMEZONE FROM DUAL;
alter session set time_zone = 'Europe/Brussels';
select * from timetest order by sessionTZ;
alter session set time_zone = '+01:00';
select * from timetest order by sessionTZ;
alter session set time_zone = '+02:00';
select * from timetest order by sessionTZ;

-- the output of this is:

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00

SQL> alter session set time_zone = 'Europe/Brussels';

Session altered.

SQL> select * from timetest order by sessionTZ;

SESSIONTZ
-----------------------------------------------------------
DATUM
-------------------
TSLTZ
-----------------------------------------------------------
+01:00
25-10-2009 01:20:00
25*10*2009 02:20:00

+01:00
25-10-2009 03:20:00
25*10*2009 03:20:00

+02:00
25-10-2009 03:20:00
25*10*2009 02:20:00

+02:00
25-10-2009 01:20:00
25*10*2009 01:20:00

Europe/Brussels
25-10-2009 03:20:00
25*10*2009 03:20:00

Europe/Brussels
25-10-2009 01:20:00
25*10*2009 01:20:00

6 rows selected.

SQL> alter session set time_zone = '+01:00';

Session altered.

SQL> select * from timetest order by sessionTZ;

SESSIONTZ
-----------------------------------------------------------
DATUM
-------------------
TSLTZ
-----------------------------------------------------------
+01:00
25-10-2009 01:20:00
25*10*2009 01:20:00

+01:00
25-10-2009 03:20:00
25*10*2009 03:20:00

+02:00
25-10-2009 03:20:00
25*10*2009 02:20:00

+02:00
25-10-2009 01:20:00
25*10*2009 00:20:00

Europe/Brussels
25-10-2009 03:20:00
25*10*2009 03:20:00

Europe/Brussels
25-10-2009 01:20:00
25*10*2009 00:20:00

6 rows selected.

SQL> alter session set time_zone = '+02:00';

Session altered.

SQL> select * from timetest order by sessionTZ;

SESSIONTZ
-----------------------------------------------------------
DATUM
-------------------
TSLTZ
-----------------------------------------------------------
+01:00
25-10-2009 01:20:00
25*10*2009 02:20:00

+01:00
25-10-2009 03:20:00
25*10*2009 04:20:00

+02:00
25-10-2009 03:20:00
25*10*2009 03:20:00

+02:00
25-10-2009 01:20:00
25*10*2009 01:20:00

Europe/Brussels
25-10-2009 03:20:00
25*10*2009 04:20:00

Europe/Brussels
25-10-2009 01:20:00
25*10*2009 01:20:00


6 rows selected.

30) extract TIMEZONE_MINUTE gives a negative result

Thit is possible and intended. A timezone offset cannot be something like -04:-30 but the timezone minutes "alone" can be negative , -4 hours +30 minutes wouldn't make sense.
There are not many timezones who are affected, most known is the Venezuela America/Caracas timezone.

drop table test;
create table test (col1 timestamp with time zone);
insert into test values (to_timestamp_TZ('21/12/2010 US/Pacific','DD/MM/YYYY TZR'));
insert into test values (to_timestamp_TZ('21/12/2010 America/Caracas','DD/MM/YYYY TZR'));
insert into test values (to_timestamp_TZ('21/12/2010 Europe/Brussels','DD/MM/YYYY TZR'));
commit;
SELECT TZ_OFFSET(to_char(col1,'TZR')) from test;
SELECT extract(TIMEZONE_HOUR from col1) from test;
SELECT extract(TIMEZONE_MINUTE from col1) from test;

-- gives:
SQL> SELECT TZ_OFFSET(to_char(col1,'TZR')) from test;

TZ_OFFS
-------
-08:00
-04:30
+01:00

SQL> SELECT extract(TIMEZONE_HOUR from col1) from test;

EXTRACT(TIMEZONE_HOURFROMCOL1)
------------------------------
-8
-4
1

SQL> SELECT extract(TIMEZONE_MINUTE from col1) from test;

EXTRACT(TIMEZONE_MINUTEFROMCOL1)
--------------------------------
0
-30
0

There are 2 known issue with something similar:
Bug 7690421 INVALID TZ VALUE IN TIMEZONE COLUMN PRODUCES ORA-01875
Fixed in 11gR2 (11.2.0.1) and up and affects Advanced Queuing

BUG:9432071 - 'NO DATA AVAILABLE' SHOWS IN CHARTS TAB OF GENERIC SERVICE PAGE WITH UDM USED
Affects Oracle Enterprise manager, see Note 1232904.1 ORA-20241:ORA-01875:time zone minute must be between -59 and 59 when minutes part of TZ in repository DB is non-zero

31) How to cast a DATE or TIMESTAMP to another timezone?

note: do not use the NEW_TIME() function, this does not handle DST see note 419404.1 DST Has No Effect on NEW_TIME function

There are several way's but this is the most intuitive.
It's important to realise that the "DATE" and "TIMESTAMP" datatype has no timezone attributed . It may have a implicit timezone , because you know the timezone of the server, but the datatype itself is not aware of this.
If you want to use timezone aware timestamps then the best solution is to not use "sysdate" and the DATE or TIMESTAMP datatype but Timestamp with timezone and store the time / date with a full timezone , for example from systimestamp.

If you want to "convert" the datetime information in a DATE or TIMESTAMP datatype to an other timezone you first of all need to be very sure what timezone this DATE or TIMESTAMP is actually supposed to be.
For example the "US Central Time" might have different meanings, you have Central Standard Time (CST) = GMT-6 but a in a lof of places DST is applied ( Central Daylight Time (CDT) = GMT-5 ).
So you need to use the correct Olson/oracle timezone, for example for an "US central location" that has DST use America/Chicago.
Make also sure your rdbms DST version is up to date for the timezones used , see note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches

You can then construct from a date or timestamp a timstamp with timezone with the correct timezone , cast that at a different timezone and (if needed) back to a date or timestamp.
Here an example of casting a date that is know to be actually "US Central Time" (= with DST) to the Chinese timezone.

-- to make things clear in the output
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';

drop table test;
create table test (col date);
insert into test values ( to_date('25/11/2011 12:20:00','DD-MM-YYYY HH24:MI:SS'));
insert into test values ( to_date('25/06/2011 12:20:00','DD-MM-YYYY HH24:MI:SS'));
Commit;

select * from test;

-- to_timestamp_TZ uses a string not a date as input so use a explicit date format mask
select to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS') ||' America/Chicago','DD-MM-YYYY HH24:MI:SS TZR') from test;
-- then you can use the at time zone operator
select to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS') ||' America/Chicago','DD-MM-YYYY HH24:MI:SS TZR') AT TIME ZONE 'Asia/Chongqing' from test;
-- then, if needed you can cast it back to a date
-- again use explicit to_char first
select to_date(to_char(to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS') ||' America/Chicago','DD-MM-YYYY HH24:MI:SS TZR') AT TIME ZONE 'Asia/Chongqing','DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') from test;

Output:

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
Session altered.
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
Session altered.
SQL> ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
Session altered.
SQL> drop table test;
Table dropped.
SQL> create table test (col date);
Table created.
SQL> insert into test values ( to_date('25/11/2011 12:20:00','DD-MM-YYYY HH24:MI:SS'));
1 row created.
SQL> insert into test values ( to_date('25/06/2011 12:20:00','DD-MM-YYYY HH24:MI:SS'));
1 row created.
SQL> Commit;
Commit complete.
SQL> select * from test;

COL
-------------------
25-11-2011 12:20:00
25-06-2011 12:20:00

SQL> -- to_timestamp_TZ uses a string not a date as input so use a explicit date format mask
SQL> select to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS') ||' America/Chicago','DD-MM-YYYY HH24:MI:SS TZR') from test;
TO_TIMESTAMP_TZ(TO_CHAR(COL,'DD-MM-YYYYHH24:MI:SS')||'AMERICA/CHICAGO','DD-
---------------------------------------------------------------------------
25/11/2011 12:20:00 AMERICA/CHICAGO CST
25/06/2011 12:20:00 AMERICA/CHICAGO CDT

SQL> -- then you can use the at time zone operator
SQL> select to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS') ||' America/Chicago','DD-MM-YYYY HH24:MI:SS TZR') AT TIME ZONE 'Asia/Chongqing' from test;

TO_TIMESTAMP_TZ(TO_CHAR(COL,'DD-MM-YYYYHH24:MI:SS')||'AMERICA/CHICAGO','DD-
---------------------------------------------------------------------------
26/11/2011 02:20:00 ASIA/CHONGQING CST
26/06/2011 01:20:00 ASIA/CHONGQING CST

SQL> -- then, if needed you can cast it back to a date
SQL> -- again use explicit to_char first
SQL> select to_date(to_char(to_timestamp_TZ(to_char(col,'DD-MM-YYYY HH24:MI:SS')||' America/Chicago','DD-MM-YYYY HH24:MI:SS TZR') AT TIME ZONE 'Asia/Chongqing','DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') from test;

TO_DATE(TO_CHAR(TO_
-------------------
26-11-2011 02:20:00
26-06-2011 01:20:00

32) Should I worry when moving data from a database in one timezone into a database in an other timezone using export/import or dblinks?

The "OS timezone" has no impact on any stored value in DATE/TIMESTAMP/TSTZ/TSLTZ datatypes.

The DATE and TIMESTAMP datatypes have no concept of timezone so that data will not change at all.

TimeStamp with Time Zone (TSTZ) data (see point 1 in this note to see if you actually store user TSTZ data) stores the time and the actual timezone offset/name used for that time at the moment of insert. so you need to make sure that for BOTH databases the RDBMS DST version is up to date for the used named timezones ( see note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches ). TSTZ data using offsets is not affected by the RDBMS DST version.
For example if you store US timezones and export data from a DST 4 database into a DSTv14 database nothing will change seen the DST definitions for US timezones in both are the same. If you however export data containing a timezone that has been changed in let's say DSTv7 (New Zealand for example) and you source DB is DSTv7 but the target is DSTv4 then for some NZ dates the result will be incorrect in the new database. If the target is DSTv16 then all is fine seen DSTv16 has the same (correct) "DST Rules" for New Zealand as DSTv7.
So bottomline is, if you have user TSTZ using named timezones, make sure your target database is using the same or a higher RDBMS DST than the source.

TimeStamp with Local Time Zone (TSLTZ) data (see point 1 in this note to see if you actually store TSLTZ data) stores internally the time converted to/from the database timezone (see point 3) from the timezone specified at insert/select time. The DBTIMEZONE should be an OFFSET not a named timezone that has DST.
The actual DBTIMEZONE is however transparent to the returned result. If your source database uses for example a DBTIMEZONE set to -08:00 and the target database uses +07:00 then the internal values for the stored TSLTZ will change (exp/imp and dblinks will adapt between them) but if you select this column in both databases using the same SESSIONTIMEZONE the result will be just the same.

33) How to get the time in UTC , GMT or epoch (Unix) time in Oracle?

Technically there is a difference between UTC and GMT, in Oracle RDBMS however UTC and GMT are considered the same.

This will give "systimestamp in UTC" as a Timestamp with Timezone datatype result regardless of any database or OS setting

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR ';
select systimestamp AT TIME ZONE 'UTC' from dual;

This will give "systimestamp in UTC" as a Timestamp datatype regardless of any database or OS setting

ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='DD*MM*YYYY HH24:MI:SS';
select sys_extract_utc(systimestamp) from dual;

This will give a "sysdate in UTC" as a date dataype regardless of any database or OS setting

ALTER SESSION SET NLS_DATE_FORMAT ='YYYY-MM-DD HH24:MI:SS';
select to_date(to_char(systimestamp AT TIME ZONE 'UTC','DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') from dual;
-- or
select to_date(to_char(sys_extract_utc(systimestamp),'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') from dual;

Epoch time is by definition in UTC, defined as the number of seconds that have elapsed since midnight Coordinated Universal Time (UTC), 1 January 1970 .
Using "sysdate in UTC" and subtract 01-01-1970 00:00:00 and multiply by 86400 to make a epoch routine that is unaffected by the server timezone or database / session settings.

select to_number(to_date(to_char(systimestamp AT TIME ZONE 'UTC','DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * (86400) from dual;
-- or
select to_number(to_date(to_char(sys_extract_utc(systimestamp),'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * (86400) from dual;

34) Is there a way to get the "server" (= Operating system) timezone setting in SQL?

No, there is an Enhancement Request <Bug 20204352> - ADD FUNCTION TO GET OS TIMEZONE SETTING
To provide such functionality but currently there is no way to get the actual used OS timezone (or equivalent RDBMS timezaone name) in the database.

99) Known issues related to timezone usage in sql

Open Bugs at time of writing (Oct 2013):
Bug 11770037 - timestamp with local time zone and numtodsinterval give wrong result around dst

Bug 1937516 TRAILING NULL (0) BYTE AT END OF RESULT FROM TZ_OFFSET CAUSES STRANGE BEHAVIOR
the workaround is using substr: SUBSTR(TZ_OFFSET(-TIMEZONE-),0,(LENGTH(TZ_OFFSET(-TIMEZONE-))-1))

Fixed in 12.1.0.2 and up:
BUG 16731148 - ORA-01882 WHEN SELECTING TIMESTAMP LITERALS VIA DB-LINK OR USING INSERT

Fixed in 11.2.0.3 and up:
Bug 9914473 - cast of tstz to tsltz can return wrong day value

Bug 7655007 Wrong results from SQL involving TIMESTAMP with TIME ZONE values / note 1354912.1 AT TIME ZONE clause ignored in some queries

Fixed in 11.2.0.1 and up:
Bug.9309601/bug 6760036 TZD MASK GIVES WRONG RESULT AROUND SOME DST TRANSITIONS
Bug.9487184/bug 6760036 EXTRACT(TIMEZONE_HOUR ) GIVES INCORRECT RESULT FOR SOME DST TRANSITIONS
(ask a backport of bug 6760036 to have the fix for both bugs for older releases. )

Bug 7655007 - sys_extract_utc cannot parse timestamp at time zone statement
SYS_EXTRACT_UTC is used to extract the UTC (Coordinated Universal Time.formerly Greenwich Mean Time) from a datetime value with time zone offset or time zone region name. But when used with AT TIME ZONE 'timezone' statement, the TIME ZONE is ineffective.
SELECT to_char(sys_extract_utc(TIMESTAMP'1999-10-29 01:30:00' AT TIME ZONE 'GMT'),'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
For example, the above should return 01:30:00. However it returns 09:30:00. In fact, whatever specified with AT TIME ZONE, it always returns 09:30:00, which is the time converted 01:30:00 using a session time zone.

Fixed in RDBMS DSTv16:
Bug 11813131 dst-v15 does not work properly for asia/amman

REFERENCES

NOTE:227334.1 - Dates & Calendars - Frequently Asked Questions

NOTE:365558.1 - ORA-1804: Failure to Initialize Timezone Information
NOTE:756454.1 - TSLTZ (TIMESTAMP WITH LOCAL TIME ZONE) Data and DST Updates
NOTE:207822.1 - Time Zone Support in Forms
BUG:9487184 - EXTRACT(TIMEZONE_HOUR ) GIVES INCORRECT RESULT FOR SOME DST TRANSITIONS
BUG:9914473 - CAST OF TSTZ TO TSLTZ CAN RETURN WRONG DAY VALUE
BUG:11770037 - TIMESTAMP WITH LOCAL TIME ZONE AND NUMTODSINTERVAL GIVE WRONG RESULT AROUND DST
NOTE:60134.1 - Globalization (NLS) - Frequently Asked Questions

BUG:7690421 - INVALID TZ VALUE IN TIMEZONE COLUMN PRODUCES ORA-01875
BUG:11813131 - AFTER APPLIED DST-V15, IT DOES NOT WORK PROPERLY ON ASIA/AMMAN
BUG:9309601 - TZD MASK GIVES WRONG RESULT AROUND SOME DST TRANSITIONS
NOTE:467722.1 - DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained
NOTE:412160.1 - Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches
NOTE:397766.1 - ORA-1882 when using time zone UTC from a 9.2 client or connecting to 9i database
NOTE:301420.1 - Why does sysdate have the Wrong Time Stamp when Connecting via the Listener
NOTE:402614.1 - Time Zone Data in the Data Dictionary (SYS) and the Effects of a Time Zone File Update
NOTE:337698.1 - TIMECALC: A package to simplify timestamp calculations
NOTE:1811.1 - HOW TO SET UP TIMEZONES ON UNIX
NOTE:1232904.1 - Metric Collection Error: 'ORA-20241:ORA-01875:time zone minute must be between -59 and 59' For WebApplication Metrics when Repository Timezone has an offset to UTC
BUG:9956870 - TIMEZONE_MINUTEFROM(SELECTSYSTIMESTAMPFROMDUAL) SHOWS '-30'
BUG:9432071 - 'NO DATA AVAILABLE' SHOWS IN CHARTS TAB OF GENERIC SERVICE PAGE WITH UDM USED

Tuesday, January 23, 2018

Timestamps & time zones - Frequently Asked Questions (Doc ID 340512.1)