oracle的sysdate,systimestamp,current

一、当前数据库服务器系统时区为CST

set line 200 col SYSDATE for a20 col SYSTIMESTAMP for a40 col CURRENT_DATE for a20 col CURRENT_TIMESTAMP for a40 select sysdate,systimestamp,current_date,current_timestamp from dual; SQL> select sysdate,systimestamp,current_date,current_timestamp from dual; SYSDATE SYSTIMESTAMP CURRENT_DATE CURRENT_TIMESTAMP -------------------- ---------------------------------------- -------------------- ---------------------------------------- 2020-07-01 13:37:40 01-JUL-20 01.37.40.850582 PM +08:00 2020-07-01 13:37:40 01-JUL-20 01.37.40.850590 PM +08:00 alter session set time_zone='+00:00'; SQL> select sysdate,systimestamp,current_date,current_timestamp from dual; SYSDATE SYSTIMESTAMP CURRENT_DATE CURRENT_TIMESTAMP -------------------- ---------------------------------------- -------------------- ---------------------------------------- 2020-07-01 13:38:14 01-JUL-20 01.38.14.566978 PM +08:00 2020-07-01 05:38:14 01-JUL-20 05.38.14.566985 AM +00:00 alter database set time_zone='+00:00'; shutdown immediate; startup SQL> select sysdate,systimestamp,current_date,current_timestamp from dual; SYSDATE SYSTIMESTAMP CURRENT_DATE CURRENT_TIMESTAMP -------------------- ---------------------------------------- -------------------- ---------------------------------------- 2020-07-01 13:40:27 01-JUL-20 01.40.27.382635 PM +08:00 2020-07-01 13:40:27 01-JUL-20 01.40.27.382642 PM +08:00

二、当前数据库服务器系统时区为UTC

SQL> select sysdate,systimestamp,current_date,current_timestamp from dual; SYSDATE SYSTIMESTAMP CURRENT_DATE CURRENT_TIMESTAMP -------------------- ---------------------------------------- -------------------- ---------------------------------------- 2020-07-01 05:43:09 01-JUL-20 05.43.09.674300 AM +00:00 2020-07-01 05:43:09 01-JUL-20 05.43.09.674303 AM +00:00 alter database set time_zone='+08:00'; shutdown immediate; startup SQL> select sysdate,systimestamp,current_date,current_timestamp from dual; SYSDATE SYSTIMESTAMP CURRENT_DATE CURRENT_TIMESTAMP -------------------- ---------------------------------------- -------------------- ---------------------------------------- 2020-07-01 05:44:42 01-JUL-20 05.44.42.375811 AM +00:00 2020-07-01 05:44:42 01-JUL-20 05.44.42.375814 AM +00:00 alter session set time_zone='+08:00'; SQL> select sysdate,systimestamp,current_date,current_timestamp from dual; SYSDATE SYSTIMESTAMP CURRENT_DATE CURRENT_TIMESTAMP -------------------- ---------------------------------------- -------------------- ---------------------------------------- 2020-07-01 05:46:58 01-JUL-20 05.46.58.744106 AM +00:00 2020-07-01 13:46:58 01-JUL-20 01.46.58.744111 PM +08:00

从实验结果可以得到如下结论：

（1）sysdate的时间和数据库服务器的系统时间是一致的。 （2）会话时区继承自操作系统时区，CURRENT_TIMESTAMP和当前会话的时区有关系，返回的日期和时间会根据时区转换。