关于timestamp的几个函数:
sysdate 数据库服务器操作系统时间,显示不含时区(其实隐含了时区)。
systimestamp 数据库服务器操作系统时间以及时区
注意上述两个函数的返回值不会受到客户端影响。
localtimestamp 根据客户端时区转换成客户端当前时间,但显示并不含时区
current_timestamp 根据客户端时区转换成客户端当前时间,包含客户端时区
注意上述两个函数的返回值和客户端时区设置有关,会转换为客户端时区的时间。
【实验一】
数据库服务器操作系统时间及时区:
linux-qtx3:~ # date -R
Thu, 30 Mar 2017 07:18:52 -0400
![](http://img.blog.itpub.net/blog/attachment/201703/31/29827284_1490939921033e.png?x-oss-process=style/bb)
分别查询sysdate,systeimstamp,localtimestamp以及current_timestamp
命令 | 输出 |
select sysdate from dual; | 2017/3/30 7:22:47 |
select systimestamp from dual; | 30-MAR-17 07.22.56.731121 AM -04:00 |
select localtimestamp from dual; | 30-MAR-17 07.23.06.814472 PM |
select current_timestamp from dual; | 30-MAR-17 07.23.14.907555 PM +08:00 |
2.将客户端修改为东7区:
![](http://img.blog.itpub.net/blog/attachment/201703/31/29827284_1490946302tTAx.png?x-oss-process=style/bb)
再次执行上述命令:
命令 | 输出 |
select sysdate from dual; | 2017/3/30 7:31:27 |
select systimestamp from dual; | 30-MAR-17 07.32.41.710320 AM -04:00 |
select localtimestamp from dual; | 30-MAR-17 06.33.00.222382 PM |
select current_timestamp from dual; | 30-MAR-17 06.33.14.242552 PM +07:00 |
3.修改数据库服务器时间为3月29日
linux-qtx3:~ # date -R
Wed, 29 Mar 2017 03:00:02 -0400
接着按照步骤2中TIME_ZONE='+7:00'设置执行命令:
命令 | 输出 |
select sysdate from dual; | 2017/3/29 3:00:49 |
select systimestamp from dual; | 29-MAR-17 03.00.59.413491 AM -04:00 |
select localtimestamp from dual; | 29-MAR-17 02.01.08.518988 PM |
select current_timestamp from dual; | select current_timestamp from dual; |
点击(此处)折叠或打开
- CREATE TABLE T(ID number(2),time_tz timestamp with time zone,time_lc_tz timestamp with local time zone);
- select sessiontimezone from dual;
-
- SESSIONTIMEZONE
- ---------------------------------------------------------------------------
- -04:00
-
- insert into t values(1,to_timestamp('2017-03-30 11:09:00','YYYY-MM-DD HH24:MI:SS'),to_timestamp('2017-03-30 11:09:00','YYYY-MM-DD HH24:MI:SS'));
- commit;
点击(此处)折叠或打开
- SELECT sessiontimezone FROM dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+07:00
- SELECT * FROM t;
-
- ID TIME_TZ TIME_LC_TZ
- --- -----------------------------------------------------------------------------
- 1 30-MAR-17 11.09.00.000000 AM -04:00 30-MAR-17 10.09.00.000000 PM
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29827284/viewspace-2136445/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29827284/viewspace-2136445/