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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With