For some reason, it is not possible to convert TIMESTAMP WITH TIMEZONE correctly from one time zone to another, for example:

 SELECT SYSTIMESTAMP, DBTIMEZONE, CURRENT_TIMESTAMP SESSIONTIMEZONE FROM DUAL; 
 ============================================================================================================================================================================================================= | SYSTIMESTAMP | DBTIMEZONE | CURRENT_TIMESTAMP | SESSIONTIMEZONE | ============================================================================================================================================================================================================= | 20.07.2017 7:15:33 -04:00 | Europe/Moscow | 20.07.2017 14:15:33 +03:00 | +03:00 | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Second example:

 SELECT SYSTIMESTAMP, SYSTIMESTAMP AT TIME ZONE 'Europe/Moscow' Moscow, SYSTIMESTAMP AT TIME ZONE 'America/New_York' New_York FROM DUAL 
 ========================================================================================================================================================== | SYSTIMESTAMP | MOSCOW | NEW_YORK | ========================================================================================================================================================== | 20.07.2017 7:09:25 -04:00 | 20.07.2017 11:09:25 +00:00 | 20.07.2017 11:09:25 +00:00 | ----------------------------------------------------------------------------------------------------------------------------------------------------------

In this case, as a result of the conversion, the time zone was completely reset, despite the successful execution of the conversion.

  1. Why in the first example the result of CURRENT_TIMESTAMP is one hour behind the real one, which should be 15:15:33 , not 14:15:33 ?
  2. Why in the second example, despite the successful execution of the command, the conversion does not occur? Maybe some database parameters are not configured, or I do not understand how AT TIME ZONE works? I took the names of the time zones from V$TIMEZONE_NAMES .

Database Version: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

UPD: Data from nls_session_parameters

SQL Developer

 NLS_LANGUAGE RUSSIAN NLS_TERRITORY RUSSIA NLS_CURRENCY â‚˝ NLS_ISO_CURRENCY RUSSIA NLS_NUMERIC_CHARACTERS , NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD.MM.RR NLS_DATE_LANGUAGE RUSSIAN NLS_SORT RUSSIAN NLS_TIME_FORMAT HH24:MI:SSXFF NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR NLS_DUAL_CURRENCY â‚˝ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE

dbForge

 NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS CHAR NLS_NCHAR_CONV_EXCP FALSE
  • Specify, in the first example - SYSTIMESTAMP shows the time of NY, -4 and +3 = 7 hours of difference, it seems to be correct, but you do not complain that in New York the clock is also lagging behind. - 0xdb
  • @ 0xdb, I didn’t pay attention to this, to be honest, it turns out that it lags behind by an hour - Vladislav Khapin
  • Well, then with the 1st question it is clear, the system time is not set correctly. To the 2nd question: The conversion is made by the client. How do you use? What are the NLS settings, environment variables, what does nls_session_parameters return? Can you try sqlpus right on the machine where the base is installed? - 0xdb
  • @ 0xdb 1 really cured by setting the system time. With the second, it turned out that SQL Developer does the conversion correctly, but the dbForge \ IDEA plugin is not. I think you can write it as an answer and I will accept it then. Session parameters also dropped in the question. - Vladislav Khapin
  • Glad what happened. I will write the answer in the evening. In the question, as I understand it - dbForge. SQL Developer is also not sinless, and sqlpus has not yet been noticed. - 0xdb

1 answer 1

To the 1st question

SYSTIMESTAMP current time and time zone of the operating system of the machine on which the database server is installed.

CURRENT_TIMESTAMP returns the same data considering the session time zone on the client, which is determined by the client from the environment (region settings on the OS, variables) in which it is running and can be changed directly: setenv ORA_SDTZ="Europe/Moscow" or alter session set time_zone='+03:00' .

You can view the time zone of the session as follows:

 select sessiontimezone from dual;

In the example, the difference between Moscow and New York is -4 and +3 = 7 hours, which corresponds to reality. This means that the time that SYSTIMESTAMP SYSTIMESTAMP also a lag, i.e. The system time is not set correctly.

To the 2nd question

The date and time conversion is performed on the client side and depends on its NLS settings, time zone (see above), which can be viewed:

 select * from nls_session_parameters;

Some clients, as in this example with dbForge\IDEA , the time zone is “lost”, or sqlplus does not take into account changes in the time zone in Russia since October 2014, the conversion is not always true. Therefore, when in doubt, it makes sense to execute a query on different clients.

Not within question

Oracle recommends setting DBTIMEZONE to UTC if the data type TIMESTAMP WITH LOCAL TIME ZONE not used for any specific reason.