Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle default_timezone & dbtimezone tips

Oracle Tips by Burleson Consulting
May 7, 2008

 

Oracle scheduling timestamps

Oracle Current Timestamp

default_timezone tips

As the name implies, this attribute sets the default time zone for the scheduler. When a job is scheduled using the calendar syntax to define a repeat interval, the scheduler needs to know which time zone to apply when calculating the next run date. Since a time zone cannot be specified explicitly by the calendar syntax, it must be derived from the following sources, in the order noted below:

* The time zone of the job's start_date attribute.

* The current session's time zone.

* The scheduler's default_timezone attribute.

* The time zone returned by the systimestamp function.

The following example sets the default_timezone attribute to a value of 'US/Eastern' and displays the change.

BEGIN
DBMS_SCHEDULER.set_scheduler_attribute (
attribute => 'default_timezone',
value => 'US/Eastern');
END;
/

SQL> @show_scheduler_attribute.sql default_timezone

V_VALUE
---------------------------------------------------
US/Eastern




The time zone in Oracle Enterprise Manager is derived from one of the following places in this order: the start_date, the current sessions time zone, the default_timezone scheduler attribute, or time zone returned by the systimestamp function.

The OEM Oracle scheduler derives the time zone from the default_timezone attribute.

Oracle recommends that you set the default_timezone attribute to a regions name instead of absolute time zone offset, in order to ensure that daylight saving adjustments are being taken into account.

The Oracle client must set the ORA_SDTZ timezone variable. Also the data must be of the type TIMESTAMP WITH LOCAL TIME ZONE.

Ram Srinivasan has this example of setting the time zone:

For the purposes of exploiting the time zone options for the TIMESTAMP datatype, create a table with different TIMESTAMP columns.

create table date_table
   (time_stamp_tz TIMESTAMP WITH TIME ZONE, time_stamp_ltz
   TIMESTAMP WITH LOCAL TIME ZONE);

This is created to show a quick example of how the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE datatypes will behave when used to store information. To begin with, set the DBTIMEZONE and SESSION TIMEZONE to be the same value '-07:00'. Then insert into the DATE_TABLE the value of a system time stamp.

SQL> insert into date_table values (SYSTIMESTAMP,SYSTIMESTAMP);
1 row created.


Now, change the time zone for the current session only to be '-03:00'. This will set the current session four hours ahead of the database time zone.

SQL> alter session set time_zone='-03:00';
Session altered.


SQL> select dbtimezone,sessiontimezone from dual;

DBTIME SESSIONTIMEZONE
---------------------------------------------------------------------------
-07:00 -03:00


Then insert into the DATE_TABLE a row that will hold the current session timestamp.

SQL> insert into date_table values (LOCALTIMESTAMP,LOCALTIMESTAMP );

1 row created.

SQL> select time_stamp_tz, time_stamp_ltz from date_table;

TIME_STAMP_TZ TIME_STAMP_LTZ
-------------------------------------------------------------------------------------------------------------
21-SEP-07 04.27.01.109000 PM +05:30 21-SEP-07 12.57.01.109000 AM
21-SEP-07 07.58.50.109000 AM -03:00 21-SEP-07 12.58.50.109000 AM

SQL> alter session set time_zone='-07:00';

Session altered.

SQL> select time_stamp_tz, time_stamp_ltz from date_table;

TIME_STAMP_TZ TIME_STAMP_LTZ
---------------------------------------------------------------------------------------------------------
21-SEP-07 04.27.01.109000 PM +05:30 20-SEP-07 08.57.01.109000 PM
21-SEP-07 07.58.50.109000 AM -03:00 20-SEP-07 08.58.50.109000 PM

When users retrieve TIMESTAMP WITH LOCAL TIME ZONE data, Oracle returns it in the users' session time zone. The session time zone also takes effect when a TIMESTAMP value is converted to the TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE datatype.

  • The main idea to grasp here is that the column with datatype TIMESTAMP WITH TIME ZONE stores and displays the explicit time supplied from the INSERT statement.
  • The column with datatype TIMESTAMP WITH LOCAL TIME ZONE stores the explicit time supplied but will display a value that is relative to the current session time zone.

This means that if there is a hard and fast requirement for TIMESTAMPS with time zone information, use the TIMESTAMP WITH TIME ZONE datatype. If the requirement is more with giving date and time information to customers in different time zones and represented in their local time, use the TIMESTAMP WITH LOCAL TIME ZONE datatype.


Michael Armstrong Smith has these notes on using the default_timezone:You can pick up your timezone using the dbtimezone function like this:

SELECT DBTIMEZONE FROM DUAL;

This gets you your session timezone:

SELECT SESSIONTIMEZONE FROM DUAL;

If you know the names of 2 time zones, try looking at the NEW_TIME function.

Syntax: NEW_TIME(date, this_zone, destination_zone)

Description: This function returns the date and time for the destination_zone based on the specified date and time in this_zone. This is a very useful and clever function that can be used to work out the current time in different parts of the world. Both this_zone and destination_zone must be supplied as three-letter abbreviations.

By the clever use of the TO_CHAR function, and knowing how many hours ahead or behind you are to other countries, you can work out the time anywhere in the world. To calculate the time in China, for example, we know that China is 15 hours ahead of California. Therefore, we need to add 15/24 to the current time.

Examples: In the following examples, suppose that the current time zone is CST (US Central Time).

SELECT NEW_TIME(SYSDATE, 'CST', 'GMT') FROM DUAL --returns the date in London.

SELECT TO_CHAR(NEW_TIME(SYSDATE, 'CST', 'GMT'),'HH24:MI') FROM DUAL --returns the time, based on the 24-hour clock, in London.

SELECT TO_CHAR(NEW_TIME(SYSDATE + (14 / 24), 'PST', 'PST'),'DD-MON-YY HH24:MI') FROM DUAL --returns the date and time in China.


If you are in a part of the world that does not have a supported time zone, do not despair. The solution is very simple. You calculate the time difference between your offices, insert that instead of the word 'diff' in the function that follows, and then run the function. Make sure both of the quoted time zones are the same. I inserted 'GMT', but this works with all supported zones.

SELECT TO_CHAR(NEW_TIME(SYSDATE + (diff / 24), 'GMT', 'GMT'),'DD-MON-YY HH24:MI') FROM DUAL; --returns the date and time of your office.



 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.