sysdate and dbtimezone different in Oracle Database

Question

With this query: select sysdate from dual;

the result is: 27-09-2018 07:50:50 -- this UK time

with : Select dbtimezone from dual;

output: +10:00

I want the sysdate to be of the same timezone of dbtimezone

I was trying to do it with alter database set time_zone='AEST', but i'm bit confused if this is the right solution.

Any help most appreciated. Thanks.

Solution 1

It is a common misunderstanding that DBTIMEZONE is the time zone for SYSDATE and SYSTIMESTAMP

SYSDATE and SYSTIMESTAMP are returned in time zone of the operating system on which the database server resides.

DBTIMEZONE is the (internal) time zone of TIMESTAMP WITH LOCAL TIME values. I don't know any practical use of it. Note, you cannot change DBTIMEZONE on your database if the database contains a table with a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data.

If you want current time at DBTIMEZONE run

select SYSTIMESTAMP AT TIME ZONE DBTIMEZONE 
from dual;

CURRENT_TIMESTAMP AT TIME ZONE DBTIMEZONE is also working.

See also How to handle Day Light Saving in Oracle database

Solution 2

SYSDATE returns the current date and time set for the operating system on which the database server resides.

CURRENT_DATE returns the current date in the session time zone.

show parameter nls_date_format

NAME                           TYPE        VALUE
------------------------------ ----------- ------------------------
nls_date_format                string      dd-Mon-yyyy hh24:mi:ss


select sysdate, current_date, dbtimezone from dual;

SYSDATE              CURRENT_DATE         DBTIME
-------------------- -------------------- ------
27-Sep-2018 08:16:00 27-Sep-2018 08:16:00 +00:00


alter session set time_zone = '+08:00';

Session altered.


select sysdate, current_date, dbtimezone from dual;

SYSDATE              CURRENT_DATE         DBTIME
-------------------- -------------------- ------
27-Sep-2018 08:13:06 27-Sep-2018 15:13:06 +00:00