Oracle Database Time Zone

1) dbtimezone only shows the database creation time TZ offset from OS environment.
For AEST TZ, 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