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.

[oracle@apx1 oracle]$ date
vie jul  1 14:25:49 BST 2022

[oracle@bdx1 ~]$ 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

The SESSIONTIMEZONE parameter returns the correct value, 1:

SQL> SELECT SESSIONTIMEZONE FROM DUAL;

SESSIONTIMEZONE
---------------------------------------------------------------------------
 01:00

However, if I run this query (from the application server to the database):

sac@apx1> sqlplus user/pass@bdx1
SQL> select to_char(sysdate, 'dd mm yyyy hh24:mi:ss') "SYSDATE", to_char(current_date, 'dd mm yyyy hh24:mi:ss') "CURRENT_DATE" from dual;

SYSDATE             CURRENT_DATE
------------------- -------------------
01 07 2022 16:26:23 01 07 2022 15:26:23

SYSDATE result is incorrect with respect to the time that should appear.

CodePudding user response：

What do you mean by "configure to correct this offset"?

• LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP.

• CURRENT_TIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP WITH TIME ZONE.

• SYSTIMESTAMP returns the current date and time in the time zone of database server's operating system in a value of datatype TIMESTAMP WITH TIME ZONE.

Set the SESSIONTIMEZONE and/or database server time zone properly.

CodePudding user response：

You may to try to set NLS_TERRITORY environment variabile:

NLS_TERRITORY = "SPAIN"   

                                                                                                          

or in your session instance:

ALTER SESSION SET NLS_TERRITORY = "SPAIN";

You may to try also NLS_SORT parameter :

NLS_SORT = "WEST_EUROPEAN"
ALTER SESSION SET NLS_SORT = "WEST_EUROPEAN";

Thank you