oracle的sysdate,systimestamp,current_date,current_timestamp

最新推荐文章于 2024-04-13 10:10:58 发布

#慧#

最新推荐文章于 2024-04-13 10:10:58 发布

阅读量2.6k

点赞数

分类专栏： oracle 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_41561862/article/details/107072383

版权

oracle 专栏收录该内容

74 篇文章

订阅专栏

本文通过实验对比了Oracle数据库中sysdate、systimestamp、current_date和current_timestamp在不同系统时区（CST与UTC）下的表现，揭示了它们与数据库服务器时区的关系，以及会话时区如何影响CURRENT_TIMESTAMP的显示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、当前数据库服务器系统时区为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和当前会话的时区有关系，返回的日期和时间会根据时区转换。