RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Write Time Zone Aware Code in Oracle : Page 4

Oracle 9i introduced data types and support to handle time zones. Use these data types to make your Oracle code time zone aware.


Setting Up the Right Time Zone

You easily could run into pitfalls with Daylight Savings Time if your client sessions' time zones are specified using time-zone displacements instead of time zone region names Unfortunately, most client installations don't explicitly set the time zone, which causes Oracle to pick up the time zone displacement from the operating system. As a developer, you can get around this by explicitly adding ALTER SESSION SET TIME_ZONE statements to your code. However, if your client sessions are running in different regions, this is impractical. A better solution is to set up client installations so that they are configured with the appropriate time zone name.

For example, a single database that stores call records for call centers in Boston and Istanbul could have two Web servers set up to access it: one in the Boston call center, one in the Istanbul call center. The time zone for the Oracle client installation on the Boston call center's Web server is 'US/Eastern'; on the Istanbul center's Web server, it's set to 'Turkey'.

To set up a default time zone on a client installation, use the environment variable ORA_SDTZ. You can set ORA_SDTZ to the default values DB_TZ (to match the database time zone) or OS_TZ (to match the time zone returned by the client's OS, usually a displacement value), to a displacement value, or to any valid time zone region name:

ORA_SDTZ = 'DB_TZ' | 'OS_TZ' | '[+|-]HH:MI' | 'timezone_name'

In this example, you'd set ORA_SDTZ on the Boston Web server to 'US/Eastern', and set ORA_SDTZ on the Istanbul Web server to 'Turkey'.

For an example of how leaving ORA_SDTZ unset can get you into trouble, see Sidebar 4. Pitfall: DST and the Oracle Scheduler.

Make Your Oracle Code Time Zone Aware

You've seen how the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types interact with session and system parameters to provide Oracle's time zone support, and how Oracle's daylight savings time support works. Use these data types and parameters to make your Oracle code time zone aware.

Natalka Roshak is a database administrator, analyst, and architect based in Ontario, Canada. Find more of her articles and scripts at toolkit.rdbms-insight.com. Contact Natalka at www.rdbms-insight.com/contact.php.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date