Oracle server timezone using SQL query

Question

I ran

 select SYSDATE from dual;

Output:

SYSDATE            |
-------------------|
2019-10-09 08:55:29|

Then I ran,

SELECT DBTIMEZONE FROM DUAL;

Output:

DBTIMEZONE|
----------|
+00:00    |

In the first output, time is in EST and 2nd output suggests timezone is UTC.

How do I check oracle server timezone via SQL query?

Answer 1

From the docs:

The database time zone [DBTIMEZONE] is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle recommends that you set the database time zone to UTC (0:00)...

SYSDATE/SYSTIMESTAMP will return the time in the database server's OS timezone. Selecting a TIMESTAMP WITH LOCAL TIME ZONE datatype will return the time in your session's timezone (ie, SESSIONTIMEZONE).

select 
   CAST(systimestamp AS timestamp(0) with local time zone) as local_time,
   systimestamp as server_time
from dual;

DBTIMEZONE is only used as the base timezone stored in TIMESTAMP WITH LOCAL TIME ZONE columns - which you never see, because when you select from one of those columns it gets translated into your session timezone.

See this similar question for a very detailed answer.

Answer 2

It is a common misunderstanding that SYSDATE or SYSTIMESTAMP are returned at DBTIMEZONE

SYSDATE and SYSTIMESTAMP are given in the time zone of database server's operating system. If you like to interrogate the time zone of database server's operating system run

SELECT TO_CHAR(SYSTIMESTAMP, 'tzr') FROM dual;

see also How to handle Day Light Saving in Oracle database