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.

Answer 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