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.