How to Change the "Database Time" ( SYSDATE and SYSTIMESTAMP) To Another Time / Timezone
(Doc ID 1988586.1)
Last updated on FEBRUARY 06, 2024
Applies to:
Oracle Database - Standard Edition - Version 9.0.1.0 and laterOracle Database - Enterprise Edition - Version 9.0.1.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.
Symptoms
Possible reasons to follow this note:
* The result / timezone of SYSDATE and SYSTIMESTAMP need to be changed.
Please DO check first of all if the result that need to be changed is SYSDATE or SYSTIMESTAMP !
If some application needs "an other database time" then make sure WHAT function is used to "get the time in the database" in that application.
Ask the application vendor or developers what exactly they do to "get the database time".
In 99% of the case it is SYSDATE or SYSTIMESTAMP , but it's better to be sure.
There are for example other functions who give the time in a timezone defined by the *client* -> <Note 340512.1> Timestamps & timezones - Frequently Asked Questions 9) What is the difference between CURRENT_DATE, LOCALTIMESTAMP and CURRENT_TIMESTAMP?
If the application vendor/programmers say they use SYSDATE / SYSTIMESTAMP and this need to changed to an other time /timezone then this is the note to follow.
Ask the application vendor or developers what exactly they do to "get the database time".
In 99% of the case it is SYSDATE or SYSTIMESTAMP , but it's better to be sure.
There are for example other functions who give the time in a timezone defined by the *client* -> <Note 340512.1> Timestamps & timezones - Frequently Asked Questions 9) What is the difference between CURRENT_DATE, LOCALTIMESTAMP and CURRENT_TIMESTAMP?
If the application vendor/programmers say they use SYSDATE / SYSTIMESTAMP and this need to changed to an other time /timezone then this is the note to follow.
For "wrong time" issues please see <note 1627439.1> How to diagnose wrong time ( SYSDATE and SYSTIMESTAMP) after DST change , server reboot , database restart or installation when connecting to a database on an Unix server
Changes
This may be needed due an application requirement .
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| A) Is there any "Oracle database" setting or init.ora parameter that impacts or defines the timezone / result of SYSDATE and SYSTIMESTAMP ? |
| B) The FIRST question to ask is what is the required Unix Operating System setting to get the required new time / timezone : |
| B.1) Check if the current time in UTC is correct on OS level . |
| B.2) Check that the timezone chosen is correct / gives the time you require . |
| B.3) Check that the timezone chosen gives the correct OFFSET . |
| B.4) Note that on Unix setting the TZ setting in a Unix prompt does NOT change the timezone for any other Unix session or already running program (even if they are using the same OS user) . |
| B.5) Note that on most Unix systems setting the TZ parameter to an invalid value it will NOT report an error, the time shown is then typically in UTC . |
| B.6) Note that the Etc/GMT<offset>, GMT<offset> syntax for TZ is inverse of what is often assumed . |
| C) Once the required new Operating System TZ setting is known then check and change the Unix env used by Oracle: |
| C.1) When using Grid Infrastructure (if Grid Infrastructure is NOT used go to step C.2) . |
| C.2) When using RAC (if RAC is NOT used go to step C.3) . |
| C.3) When using ASM (if ASM is NOT used go to step C.4) . |
| C.4) Check the default setting for the Unix user (typically the Unix "oracle" user) used to stop and start the oracle environment . |
| D) Once everything is checked restart the oracle stack. |
| D.1) If Grid control is used and s_crsconfig_<nodename>_env.txt was changed on any of the nodes (or all) then use crsctl to restart the cluster . |
| D.2) If RAC is used and the TZ value seen in srvctl was changed then use srvctl to restart the databases and listeners . |
| D.3) When not using Grid control or RAC then restart the database using sqlplus and the listener using lsnrctl . |
| D.4) Make a remote client connection using sqlplus and check the result of "sysdate" and "systimestamp" . |
| E) Check also the OEM agent configuration . |
| F) Other often misunderstood settings (who have NO impact on "sysdate" but are often confused with or looked at) or posed questions . |
| References |