The Time Zone setting of the operating system affects the value of SYSDATE and SYSTIMESTAMP in the database query.

SYSDATE and policimestamp are not affected by the database parameter DBTIMEZONE. environment variables (such as TZ) in the operating system time zone will affect their input, because SYSDATE and SYSTIMESTAMP are directly returned by calling the underlying interface of the operating system. DBTIMEZONE setting only affects two data types in the database: TimeStampwithTime

SYSDATE and policimestamp are not affected by the database parameter DBTIMEZONE. environment variables (such as TZ) in the operating system time zone will affect their input, because SYSDATE and SYSTIMESTAMP are directly returned by calling the underlying interface of the operating system. DBTIMEZONE setting only affects two data types in the database: TimeStamp with Time

The values of SYSDATE and policimestamp are not affected by the database parameter DBTIMEZONE. environment variables (such as TZ) in the operating system time zone will affect their input, because SYSDATE and SYSTIMESTAMP are actually directly returned values by calling the underlying interface of the operating system.

DBTIMEZONE setting only affects two data types in the database: TimeStamp with Time Zone and TimeStamp with Local Time Zone.

The setting of TZ environment variables at the operating system level directly affects the values of sysdate and systiestamp, and also affects the timestamp of Database Log writing.

Here is an official explanation:

SYSTIMESTAMP is the timestamp on the server machine itself and is obtained on Unix platforms by calling"GetTimeOfDay"And on Windows by calling"GetSystemTime"To get the servers local time.

This means that policimestamp, just like SYSDATE depends on Unix platforms on the UNIX time configuration (= Unix TZ variable) for the Unix session when the database and listener where started.

The SYSDATE and policimestamp function simply performs a system-call to the Operating System to get the time ("Gettimeofday"Call ).

The following is a simple experiment:

SQL> select to_char (sysdate, 'dd-MON-YY HH24: MI: ss') from dual;

TO_CHAR (SYSDATE, 'dd-MON-YYHH24: MI: ss ')
------------------------------------------------------
17--14 1411:51:25 < <这里输出日期为17号< p>

SQL> connect sys/oracle @ ora10g as sysdba
Connected.
SQL> select to_char (sysdate, 'dd-MON-YY HH24: MI: ss') from dual;

TO_CHAR (SYSDATE, 'dd-MON-YYHH24: MI: ss ')
------------------------------------------------------
17-oct-14 11:51:33

SQL>!
[Oracle @ tivoli02 ~] $ Date
FriOct 1711:51:38 CST 2014 < <这里输出日期为17号< p>

The above output is the normal time, and then the environment variable of the time zone is modified and compared.

Export TZ = America/Anchorage

SQL> select to_char (sysdate, 'dd-MON-YY HH24: MI: ss') from dual;

TO_CHAR (SYSDATE, 'dd-MON-YYHH24: MI: ss ')
------------------------------------------------------
16--14 1419:53:50 < <这里输出日期为16号< p>

SQL> connect sys/oracle @ ora10g as sysdba
Connected.
SQL> select to_char (sysdate, 'dd-MON-YY HH24: MI: ss') from dual;

TO_CHAR (SYSDATE, 'dd-MON-YYHH24: MI: ss ')
------------------------------------------------------
16-oct-14 19:53:58

SQL>!
[Oracle @ tivoli02 ~] $ Date
Thu Oct 16 19:54:06 AKDT 2014 < <这里输出日期为16号< p>

View Database alert logs:

Fri Oct 17 11:51:57 CST 2014< <停库日期为17号
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thu Oct 16 19:53:32 AKDT 2014 < <启库日期为16号
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3

As described in the above experiment, you must set the operating system environment variables properly to avoid unnecessary troubles.

Bytes -------------------------------------------------------------------------------------------------

This article comes from my technical blog http://blog.csdn.net/robo23

For reprinting, please mark the source text link; otherwise, you will be held legally responsible!