mysql systimestamp_操作系统的时区设置会影响数据库查询SYSDATE和SYSTIMESTAMP的值

SYSDATE和SYSTIMESTAMP的并不受数据库参数DBTIMEZONE的影响，操作系统时区的环境变量(如TZ)会影响它们的输入，因为SYSDATE和SYSTIMESTAMP实际是调用操作系统底层接口直接返回。 DBTIMEZONE的设置只会影响数据库内两种数据类型的：一种是TimeStamp with Time

SYSDATE和SYSTIMESTAMP的值并不受数据库参数DBTIMEZONE的影响，操作系统时区的环境变量(如TZ)会影响它们的输入，因为SYSDATE和SYSTIMESTAMP实际是调用操作系统底层接口直接返回值。

DBTIMEZONE的设置只会影响数据库内两种数据类型的值：一种是TimeStamp with Time Zone，另一种是TimeStamp with Local Time Zone。

操作系统层面TZ环境变量的设置直接影响sysdate和systiestamp的值，同时也会影响数据库日志写入的时间戳。

先来以下一段官方相关解释:

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 SYSTIMESTAMP, 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 SYSTIMESTAMP function simply performs a system-call to the Operating System to get the time (a "gettimeofday" call).

以下通过简单的实验来证明：

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 <<<<

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

Fri Oct 17 11:51:38 CST 2014 <<<<

以上输出正常的时间，接下来修改时区环境变量之后做对比

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-1419:53:50 <<<<

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 <<<<

查看数据库alert日志：

Fri Oct 17 11:51:57 CST 2014

<<

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 16 19:53:32 AKDT 2014 <<

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

结合以上实验做阐述，请一定正常设置操作系统环境变量，避免不必要的麻烦。

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

本文来自于我的技术博客 http://blog.csdn.net/robo23

转载请标注源文链接,否则追究法律责任!

本文原创发布php中文网，转载请注明出处，感谢您的尊重！

上一篇：列转行

下一篇：表解锁