Different time sysdate and current_date when connect to database since application server

All we need is an easy explanation of the problem, so here it is.

I have a problem with the time that Oracle gives me when working with applications.

I currently connect from an application server (apx1) to a server containing Oracle 19c (bdx1) for database operations.

Both the application server and the database server are in the Canary Islands, so it must show/work local time there (GMT +1 in summer, GMT in winter).

If I launch the date command on the servers, the time appears correct.

[[email protected] oracle]$ date
vie jul  1 14:25:49 BST 2022
[[email protected] ~]$ date
vie jul  1 14:25:46 BST 2022

However, after making the connection from apx1 to bdx1, I get this time offset:

SQL> select systimestamp, current_timestamp, localtimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
01/07/22 15:27:39,762772 +02:00
01/07/22 14:27:39,762775 +01:00
01/07/22 14:27:39,762775

I don’t know what I should configure to correct this offset.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Since the time on the server itself seems to be correct, it looks however you start the database overrides this setting. This is typically caused by the TZ environment variable.

Make sure you start the database from a session/script that uses the correct TZ value.

If you start the database manually or from a script, check if TZ is set anywhere. If yes, change it to the correct value:

export TZ=Atlantic/Canary

If the database is started through Grid Infrastructure, check if the environment variable was set:

srvctl getenv database -db DBNAME
grep ^TZ ${GRID_HOME}/crs/install/s_crsconfig_$(hostname)_env.txt

If it needs to be changed, change as:

srvctl setenv database -db DBNAME -env "TZ=Atlantic/Canary"

Or edit the above text file.

After changing the GI env file, a GI restart is needed for the changes to take effect. For the other changes, a DB restart is enough.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply