8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Setting Database Time Zones in Oracle
This article contains several methods for setting the time zone in an Oracle database.
- CREATE DATABASE
- ALTER DATABASE
- ALTER SESSION
- AT TIME ZONE
- ORA_SDTZ Environment Variable
- Timezone Files
Related articles.
- Oracle Dates, Timestamps and Intervals
- Multitenant : PDBs With Different Time Zones to the CDB in Oracle Database 12c Release 1 (12.1)
- Upgrade the Database Time Zone File Using the DBMS_DST Package
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:
- Datetime Data Types and Time Zone Support
- Oracle Dates, Timestamps and Intervals
- Multitenant : PDBs With Different Time Zones to the CDB in Oracle Database 12c Release 1 (12.1)
- Upgrade the Database Time Zone File Using the DBMS_DST Package
Hope this helps. Regards Tim...