The operating system's time zone settings affect the values of database queries Sysdate and Systimestamp

The values of sysdate and systimestamp are not affected by database parameter dbtimezone, and environment variables (such as TZ) in the operating system time zone affect their input because sysdate and Systimestamp are actually calling the operating system's underlying interface directly to return values.

Dbtimezone settings affect only the values of two data types in the database: one is the timestamp with time zone and the other is the timestamp with Local time zone.

The setting of the operating system level TZ environment variable directly affects the values of sysdate and Systiestamp, and also affects the timestamp of database log writes.

Let's start with the following official explanations:

Systimestamp are the timestamp on the server machine itself and are obtained on Unix platforms by calling "gettimeofday< /c1> "and on Windows by calling"getsystemtime"to get the servers local time.

This means is systimestamp, just like sysdate depends on Unix platforms on the UNIX time configuration (= Unix TZ Variab Le) for the Unix session when the database and listener where started.

The Sysdate and Systimestamp function simply performs a system-call to the Operating system to get the time (a "Gettim Eofday"call).

The following is a simple experiment to prove:

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:25 <<<<< Here the output date is 17th number

Sql> connect Sys/[email protected] 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>!
[[Email protected] ~]$ Date
Fri Oct 11:51:38 CST <<<<< Here the output date is No. 17th

The above output is normal time, then make a comparison after modifying the time zone environment variable

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-oct-14 19:53:50 <<<<< Here the output date is 16th number

Sql> Connect sys/[email protected] 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>!
[[Email protected] ~]$ Date
Thu Oct 19:54:06 akdt <<<<< Here the output date is 16th number

To view the database alert log:

Fri Oct 11:51:57 CST <<< Stop Date is 17th number
ALTER DATABASE Dismount
Completed:alter DATABASE Dismount
Arch:archival disabled due to shutdown:1089
Shutting down archive processes
Archiving is disabled
Archive Process shutdown avoided:0 Active
Arch:archival disabled due to shutdown:1089
Shutting down archive processes
Archiving is disabled
Archive Process shutdown avoided:0 Active
Thu Oct 19:53:32 akdt <<< Qicu date 16th
Starting ORACLE instance (normal)
license_max_session = 0
license_sessions_warning = 0
Picked latch-free SCN Scheme 3

According to the above experiment, please set the operating system environment variable, avoid unnecessary trouble.

-------------------------------------------------------------------------------------------------

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

Reprint please mark source text link, otherwise investigate legal liability!

The operating system's time zone settings affect the values of database queries Sysdate and Systimestamp