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

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?

The session time zone can be set to:

• O/S local time zone
• Database time zone
• An absolute offset
• A named region

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'

$ ORA_SDTZ='OS_TZ'
$ 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.

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.

16)Can I rely on Oracle time zones definitions?

Oracle's time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/ and based on information maintained by the U.S. Navy Observatory.
Oracle's disclaimer reflects a similar statement as on U.S. Navy Observatory's website at http://tycho.usno.navy.mil/tzones.html:
"Unfortunately, no U.S. Web page can provide official information on world time zones because nations are sovereign powers that can and do change their timekeeping systems as they see fit."

Oracle periodically checks if a new time zone data is available and updates Oracle files for a new release. There is no fixed schedule for such updates.
If a new rule for the daylight saving time is introduced for any time zone and you need the Oracle information to be updated you should log a Service Request with Oracle Support Services requesting a patch for the new rule. Oracle Development will prepare a patched version of the time zone files timezlrg.dat and timezone.dat that you will have to install on the database server and clients. However, please be aware that this is a process that can take some time, so take note of the next question.

For DSTv7 and below the timezone transition tables cover a timeframe up to the year 2020, so for a date beyond 2020 you will get not the expected result. DST updates from DSTv9 onwards expand this to the year 2040.

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

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

In some cases you can be working with a time zone like "US/Eastern" 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 database time zone is US/Eastern (which in itself is not advisable btw, see question 1 above), 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 dbtimezone,'TZD') FROM DUAL;

If your database time zone is an offset rather than a named time zone (which is advisable), then the above query will fail because the "dbtimezone" function will return this offset. In that case we can simply run the same statement with the named time zone you're interested in:

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

24)What are reasons to get ORA-1882?

ORA-1882 is 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.
   
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.

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

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.

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
    
 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
    
-- here you see the "type" changing from 188 to 13 (= date )