Interview Q and A for Oracle Timestamps & Time zones Part - 1

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.

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.

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.

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 has 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
/

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

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 a offset (+00:00 for example) , or a static time zone that is not affected by DST (like UTC or GMT ) and NOT to a named timezone that is affected by DST (Like Europe/Brussels or US/Central) .

-- Get the database time zone
--
select dbtimezone from dual;
--
-- Set the session time zone to Europe/Brussels
--
alter session set time_zone = 'Europe/Brussels';
select sessiontimezone from dual;
--
-- insert two timestamp values equivalent to
-- 30 minutes before 13 March 2011, 02:00 America/New_York, and
-- 30 minutes after 13 March 2011, 02:00 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 (TO_TIMESTAMP_TZ('06-NOV-2011 06.30.00.000 Europe/Brussels','DD-MON-YYYY HH24.MI.SS.FF TZR'));
INSERT INTO tz_test VALUES (TO_TIMESTAMP_TZ('06-NOV-2011 07.30.00.000 Europe/Brussels','DD-MON-YYYY HH24.MI.SS.FF TZR'));


-- when using a database time zone set to America/New_York this gives:
--
SQL> select coltsltz from tz_test;

COLTSLTZ
-----------------------------------------------------
06/11/11 07:30:00,000000
06/11/11 07:30:00,000000

SQL> select sys_extract_utc(coltsltz) from tz_test;

SYS_EXTRACT_UTC(COLTSLTZ)
-----------------------------------------------------
06/11/11 06:30:00,000000
06/11/11 06:30:00,000000

-- when using a database time zone set to +00:00 this gives:
--
SQL> select coltsltz from tz_test;

COLTSLTZ
-------------------------------------------------------------
06/11/11 06:30:00,000000
06/11/11 07:30:00,000000

SQL> select sys_extract_utc(coltsltz) from tz_test;

SYS_EXTRACT_UTC(COLTSLTZ)
-------------------------------------------------------------
06/11/11 05:30:00,000000
06/11/11 06:30:00,000000

 Using +00:00 will also avoid the problem

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 is simply +00:00 (or any other OFFSET like -09:00, +08:00, ...).

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

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

As mentioned in the first answer above, the database time zone is only used as a time zone in which stored TSLTZ values are normalized. As such 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:

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

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

Again note that changing the "database timezone" will NOT have ANY influence on SYSDATE, SYSTIMESTAMP, CURRENT_DATE, LOCALTIMESTAMP and CURRENT_TIMESTAMP.It will also have NO impact on the RESULT when inserting / selecting TSTZ columns.

Oracle support see that a lot of customers start changing the database timezone if they have "timezone" or "sydate" problems. It will simply not help. If the database timezone is a OFFSET like +08:00 or +00:00 then, please, leave it like it is.

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 227334.1 Dates & Calendars - Frequently Asked Questions
to correct SYSDATE and SYSTIMESTAMP will also be corrected.

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

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

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, hence the default value (= ORA_TZFILE not set) is then the best choise.

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:

1. Shut down the database.
2. 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.
3. 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.

In Oracle 11.2 the ORA_TZFILE usage and the naming of the files changed. 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.