Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle server timezone using SQL query

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?

like image 297
Dev Avatar asked Oct 16 '25 11:10

Dev


1 Answers

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.

like image 147
kfinity Avatar answered Oct 19 '25 01:10

kfinity