Oracle Database Time Zone

1) dbtimezone only shows the database creation time TZ offset from OS environment.
For AEST, it could be either of the below depending on OS environment variable:
00:00
+10:00 ( non DST )
+11:00 ( DST )

SQL> select dbtimezone from dual;

DBTIME
------
+11:00


SQL> select created from v$database;

CREATED
-----------------
20161115-15:06:11    <------ DST

2) timezone file. For Australia, it needs to be greater than 14.

SQL> select * from v$timezone_file;

FILENAME              VERSION 
-------------------- ---------- 
timezlrg_18.dat       18

3) Database server time zone ( TIMESTAMP WITH TIME ZONE ):

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
-------------------------------------
24-MAY-17 04.12.15.769958 PM +10:00

4) Current session time zone :

SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
-------------------------------------------------
24-MAY-17 02.16.06.660376 PM +08:00 

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
-------------------------------------------------
+08:00

5) Time zone example :

SQL> create table table_tz ( t1 TIMESTAMP WITH TIME ZONE, t2 TIMESTAMP WITH LOCAL TIME ZONE);

Table created.


SQL> insert into table_tz values (SYSTIMESTAMP,SYSTIMESTAMP);

1 row created.

SQL> select t1,t2 from table_tz ;

T1
----------------------------------------
T2
----------------------------------------
24-MAY-17 04.28.20.428944 PM +10:00
24-MAY-17 02.28.20.428944 PM

SQL> insert into table_tz values (LOCALTIMESTAMP,LOCALTIMESTAMP );

1 row created.

SQL> select t1,t2 from table_tz;

T1
--------------------------------------------------------------
T2
--------------------------------------------------------------
24-MAY-17 04.28.20.428944 PM +10:00
24-MAY-17 02.28.20.428944 PM

24-MAY-17 02.30.26.599416 PM +08:00
24-MAY-17 02.30.26.599416 PM
Advertisements
This entry was posted in Configuration, Globalization and tagged , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s