DBA Hub

📋Steps in this guide1/6

Setting Database Time Zones in Oracle

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

oracle miscconfigurationintermediate
by OracleDba
30 views
1

CREATE DATABASE

The 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. Each pluggable database can have a different setting, as described here. Let's clear up those new pluggable databases.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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;

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

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

ALTER DATABASE

The 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 parameter to a time zone region name and an absolute offset from UTC. Each time we check the current database time zone using the function. If the database contains a table with a populated column, the command will return an error.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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>
3

ALTER SESSION

The 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 function. We could also set the session to the local time when the session was started, or the database time zone.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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>

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

AT TIME ZONE

Datetime values can be converted between time zones using . In the following example we create a value with a time zone of London, then use to convert it to the equivalent time in New York. Alternatively we can use , which will convert the time zone to the sessions local time zone.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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>

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>
5

ORA_SDTZ Environment Variable

The environment variable allows us to set the time zone from the operating system before starting the client tool. The and settings are for the operating system local time zone, and the database time zone respectively.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
# 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'
6

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. - Upgrade the Database Time Zone File Using the DBMS_DST Package For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!