4 Ways to Change the Time Zone in Oracle

Posted on by Ian

In Oracle Database, time zones can be set in various places. Below are four ways to change the time zone when using Oracle.

Set the Database Time Zone

You can set the database time zone when creating the database. To do this, use the SET TIME_ZONE clause of the CREATE DATABASE statement.

Example:

CREATE DATABASE mySampleDb
...
SET TIME_ZONE='Australia/Sydney';

In this case, I used a time zone region name. See How to Return a List of Valid Time Zones in Oracle Database to get a full list of valid region names on your system.

Alternatively, you can use an actual time zone offset:

CREATE DATABASE mySampleDb
...
SET TIME_ZONE='+10:00';

If you don’t explicitly set the time zone when creating the database, then it defaults to the time zone of the server’s operating system.

You can also change the current time zone setting for the database. To do this, use the ALTER DATABASE statement.

Example:

ALTER DATABASE mySampleDb
...
SET TIME_ZONE='Australia/Sydney';

Note that the database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Also, Oracle recommends that you set the database time zone to UTC (0:00) to avoid data conversion and improve performance when data is transferred among databases.

Set the Session Time Zone

You can set the session time zone independently to the database time zone. When you return TIMESTAMP WITH LOCAL TIME ZONE data, it’s returned in the current session’s 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 data type.

You can do a couple of things to set the time zone at the session level.

The ORA_SDTZ Environment Variable

You can set the session time zone with the ORA_SDTZ environment variable. This can be set to the following values:

  • Operating system local time zone ('OS_TZ')
  • Database time zone ('DB_TZ')
  • Absolute offset from UTC (e.g.'-04:00')
  • Time zone region name (e.g. 'America/St_Kitts')

Here are some examples of setting this environment variable in a UNIX environment:

% setenv ORA_SDTZ 'OS_TZ'
% setenv ORA_SDTZ 'DB_TZ'
% setenv ORA_SDTZ 'America/St_Kitts'
% setenv ORA_SDTZ '-04:00'

The default value of the ORA_SDTZ variable is 'OD_TZ'. This value is used when the variable is not set or it is set to an invalid value.

The ALTER SESSION Statement

You can change the time zone for a specific SQL session with the SET TIME_ZONE clause of the ALTER SESSION statement.

TIME_ZONE can be set to the following values:

  • Default local time zone when the session was started (local)
  • Database time zone (dbtimezone)
  • Absolute offset from UTC (e.g. '-04:00')
  • Time zone region name (e.g. 'Canada/Eastern')

Here’s an example of setting TIME_ZONE to such values:

ALTER SESSION SET TIME_ZONE=local;
ALTER SESSION SET TIME_ZONE=dbtimezone;
ALTER SESSION SET TIME_ZONE='Canada/Eastern';
ALTER SESSION SET TIME_ZONE='-04:00';

You can check your current session’s time zone with the SESSIONTIMEZONE function.

Example:

SELECT SESSIONTIMEZONE FROM DUAL;

Result:

Australia/Brisbane

In my case, the session time zone is set to Australia/Brisbane.

The AT TIME ZONE Clause

A datetime expression can include an AT LOCAL clause or an AT TIME ZONE clause. If you include an AT LOCAL clause, then the result is returned in the current session time zone. If you include the AT TIME ZONE clause, then the time zone can be one of the following:

  • A time zone offset
  • A time zone region name
  • DBTIMEZONE (this function returns the time zone of the database)
  • SESSIONTIMEZONE (this function returns the time zone of the current session)
  • An expression that returns a character string with a valid time zone format.

Example:

SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', '-04:00') 
    AT TIME ZONE '+12:00'
FROM DUAL;

Result:

02/JAN/30 04:30:35.000000000 AM +12:00

In this case I used the FROM_TZ() function to convert a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value. I then used the AT TIME ZONE clause to specify a different time zone.

Here’s the same example, except this time I specify AT LOCAL:

SELECT 
    FROM_TZ(TIMESTAMP '2030-01-01 12:30:35', '-04:00') 
    AT LOCAL
FROM DUAL;

Result:

02/JAN/30 02:30:35.000000000 AM AUSTRALIA/BRISBANE