8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Setting Database Time Zones in Oracle

This article contains several methods for setting the time zone in an Oracle database.

Related articles.

CREATE DATABASE

The TIME_ZONE initialisation parameter can be set during database creation, or pluggable database creation. In the following examples we create two pluggable databases with different time zones. The first uses a named region time zone, and the second uses an absolute offset from UTC.

conn / as sysdba

create pluggable database pdb2
  admin user pdbadmin identified by password1
  file_name_convert=('/pdbseed/','/pdb2/');
  set time_zone='Europe/London';
  
alter pluggable database pdb2 open;


create pluggable database pdb3 
  admin user pdbadmin identified by password1
  file_name_convert=('/pdbseed/','/pdb3/');
  set time_zone='-05:00';

alter pluggable database pdb3 open;

Each pluggable database can have a different TIME_ZONE setting, as described here.

Let's clear up those new pluggable databases.

alter pluggable database pdb2 close;
drop pluggable database pdb2 including datafiles;

alter pluggable database pdb3 close;
drop pluggable database pdb3 including datafiles;

ALTER DATABASE

The TIME_ZONE initialisation parameter can be set for an existing database or pluggable database. This works for the root container, a pluggable database or in a non-CDB database. The change only take effect after a restart of the database, or pluggable database.

In the following examples we reset the TIME_ZONE parameter to a time zone region name and an absolute offset from UTC. Each time we check the current database time zone using the DBTIMEZONE function.

alter database set time_zone='Europe/London';
shutdown immediate;
startup;


select dbtimezone from dual;

DBTIMEZONE
-------------
Europe/London

SQL>


alter database set time_zone='-05:00';
shutdown immediate;
startup;


select dbtimezone from dual;
DBTIME
------
-05:00

SQL>

If the database contains a table with a populated TIMESTAMP WITH LOCAL TIME ZONE column, the command will return an error.

ALTER SESSION

The TIME_ZONE parameter can be set at the session level. In the following examples we set it to a time zone region name and an absolute offset from UTC. Each time we check the setting with the SESSIONTIMEZONE function.

alter session set time_zone='Europe/London';

select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
Europe/London

SQL>


alter session set time_zone='-05:00';

select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
-05:00

SQL>

We could also set the session to the local time when the session was started, or the database time zone.

alter session set time_zone=local;
alter session set time_zone=dbtimezone;

AT TIME ZONE

Datetime values can be converted between time zones using AT TIME ZONE.

In the following example we create a TIMESTAMP WITH TIME ZONE value with a time zone of London, then use AT TIME ZONE to convert it to the equivalent time in New York.

set linesize 120
column london format a50
column new_york format a50

alter session set time_zone='Europe/London';

select to_timestamp_tz('2023-07-24 18:00:00', 'yyyy-mm-dd hh24:mi:ss') as london,
       to_timestamp_tz('2023-07-24 18:00:00', 'yyyy-mm-dd hh24:mi:ss') at time zone 'America/New_York' as new_york
from dual;

LONDON                                             NEW_YORK
-------------------------------------------------- --------------------------------------------------
24-JUL-23 06.00.00.000000000 PM EUROPE/LONDON      24-JUL-23 01.00.00.000000000 PM AMERICA/NEW_YORK

SQL>

Alternatively we can use AT LOCAL, which will convert the time zone to the sessions local time zone.

column local format a50

select to_timestamp_tz('2023-07-24 18:00:00', 'yyyy-mm-dd hh24:mi:ss') as london,
       to_timestamp_tz('2023-07-24 18:00:00', 'yyyy-mm-dd hh24:mi:ss') at local as local
from dual;

LONDON                                             LOCAL
-------------------------------------------------- --------------------------------------------------
24-JUL-23 06.00.00.000000000 PM EUROPE/LONDON      24-JUL-23 06.00.00.000000000 PM EUROPE/LONDON

SQL>

ORA_SDTZ Environment Variable

The ORA_SDTZ environment variable allows us to set the time zone from the operating system before starting the client tool.

# UNIX/Linux
export ORA_SDTZ='Europe/London'
export ORA_SDTZ='-05:00'
export ORA_SDTZ='OS_TZ'
export ORA_SDTZ='DB_TZ'

Rem Windows
set ORA_SDTZ='Europe/London'
set ORA_SDTZ='-05:00'
set ORA_SDTZ='OS_TZ'
set ORA_SDTZ='DB_TZ'

The OS_TZ and DB_TZ settings are for the operating system local time zone, and the database time zone respectively.

Timezone Files

Countries occasionally change their time zones, or alter the way they handle daylight saving time (DST). From Oracle 11gR2 onward, new time zone files are shipped with upgrades and patches, but they are not automatically applied to the database. This article demonstrates how to update time zone files.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.