TIMESTAMP和TIMESTAMP WITH TIME ZONE之间的总结
TIMEZONE指的是当地时间与本初子午线英格兰格林威治时间的时差
北京是东八区(+08:00)，即北京时间-格林威治=8小时，北京比格林威治早8小时看到太阳
SELECT DBTIMEZONE,SESSIONTIMEZONE,LOCALTIMESTAMP,CURRENT_TIMESTAMP,SYSTIMESTAMP FROM DUAL;
+00:00 +08:00 2018/4/17 20:05:26.192987 2018/4/17 20:05:26.192987 +08:00 2018/4/17 20:05:26.192983 +08:00
alter session set time_zone='+09:00';
SELECT DBTIMEZONE,SESSIONTIMEZONE,LOCALTIMESTAMP,CURRENT_TIMESTAMP,SYSTIMESTAMP FROM DUAL;
+00:00 +09:00 2018/4/17 21:05:02.937731 2018/4/17 21:05:02.937731 +09:00 2018/4/17 20:05:02.937726 +08:00
DBTIMEZONE：返回数据库时区.
DBTIMEZONE returns the value of the database time zone
SESSIONTIMEZONE：返回当前会话时区
SESSIONTIMEZONE returns the time zone of the current session
LOCALTIMESTAMP：返回session端不带时区的timestamp格式的当前时间
LOCALTIMESTAMP returns the current date and time in the session time zone in a value of data type TIMESTAMP
CURRENT_TIMESTAMP：返回session端带时区的timestamp格式的当前时间
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of data type TIMESTAMP WITH TIME ZONE.
SYSTIMESTAMP：返回带时区的timestamp格式的当前数据库时间
SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.
alter session set time_zone='+08:00';
CREATE TABLE test1 (ID number(2),t_timezone timestamp with time zone,t_local_zone timestamp with local time zone);
insert into test1 values (2,systimestamp,systimestamp);
select * from test1;
2 2018/4/17 20:09:03.298221 +08:00 2018/4/17 20:09:03.298221
alter session set time_zone='+09:00';
select * from test1;
2 2018/4/17 20:09:03.298221 +08:00 2018/4/17 21:09:03.298221
总结：利用timestamp时间字段属性TIMESTAMP WITH TIME ZONE，可以把数据库服务器所在的时间转化为当前时区的时间，比如当伦敦时间为20180101 02:00:00，则在北京可以看到该时间为20180101 09:00:00。
TZ_OFFSET returns the time zone offset corresponding to the argument based on the date the statement is executed.
TZ_OFFSET根据输入的参数值，返回时区与0时区相差的小时和分钟数。
SELECT TZ_OFFSET('Asia/Shanghai'),TZ_OFFSET('US/Michigan'),TZ_OFFSET('Europe/London') FROM DUAL;
+08:00 -04:00 +01:00
select TZNAME from V$TIMEZONE_NAMES--查询所有的time_zone_name
FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value.
FROM_TZ将时间戳值和时区转换为具有时区值的时间戳。
SELECT FROM_TZ(TIMESTAMP'2018-04-30 08:00:00','Asia/Shanghai'),FROM_TZ(TIMESTAMP'2018-04-30 08:00:00','US/Michigan'),FROM_TZ(TIMESTAMP'2018-04-30 08:00:00','Europe/London') FROM DUAL;
2018/4/30 8:00:00.000000000 +08:00 2018/4/30 8:00:00.000000000 -04:00 2018/4/30 8:00:00.000000000 +01:00
SELECT FROM_TZ(LOCALTIMESTAMP,'Asia/Shanghai'),FROM_TZ(LOCALTIMESTAMP,'US/Michigan'),FROM_TZ(LOCALTIMESTAMP,'Europe/London') FROM DUAL;
2018/4/17 20:14:47.519347 +08:00 2018/4/17 20:14:47.519347 -04:00 2018/4/17 20:14:47.519347 +01:00
SELECT FROM_TZ(SYSTIMESTAMP,'Asia/Shanghai'),FROM_TZ(SYSTIMESTAMP,'US/Michigan'),FROM_TZ(SYSTIMESTAMP,'Europe/London') FROM DUAL;--因为SYSTIMESTAMP本身带有时区，所以报错ORA-00932: 数据类型不一致: 应为 TIMESTAMP, 但却获得 TIMESTAMP WITH TIME ZONE。
北京是东八区(+08:00)，即北京时间-格林威治=8小时，北京比格林威治早8小时看到太阳
SELECT DBTIMEZONE,SESSIONTIMEZONE,LOCALTIMESTAMP,CURRENT_TIMESTAMP,SYSTIMESTAMP FROM DUAL;
+00:00 +08:00 2018/4/17 20:05:26.192987 2018/4/17 20:05:26.192987 +08:00 2018/4/17 20:05:26.192983 +08:00
alter session set time_zone='+09:00';
SELECT DBTIMEZONE,SESSIONTIMEZONE,LOCALTIMESTAMP,CURRENT_TIMESTAMP,SYSTIMESTAMP FROM DUAL;
+00:00 +09:00 2018/4/17 21:05:02.937731 2018/4/17 21:05:02.937731 +09:00 2018/4/17 20:05:02.937726 +08:00
DBTIMEZONE：返回数据库时区.
DBTIMEZONE returns the value of the database time zone
SESSIONTIMEZONE：返回当前会话时区
SESSIONTIMEZONE returns the time zone of the current session
LOCALTIMESTAMP：返回session端不带时区的timestamp格式的当前时间
LOCALTIMESTAMP returns the current date and time in the session time zone in a value of data type TIMESTAMP
CURRENT_TIMESTAMP：返回session端带时区的timestamp格式的当前时间
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of data type TIMESTAMP WITH TIME ZONE.
SYSTIMESTAMP：返回带时区的timestamp格式的当前数据库时间
SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.
alter session set time_zone='+08:00';
CREATE TABLE test1 (ID number(2),t_timezone timestamp with time zone,t_local_zone timestamp with local time zone);
insert into test1 values (2,systimestamp,systimestamp);
select * from test1;
2 2018/4/17 20:09:03.298221 +08:00 2018/4/17 20:09:03.298221
alter session set time_zone='+09:00';
select * from test1;
2 2018/4/17 20:09:03.298221 +08:00 2018/4/17 21:09:03.298221
总结：利用timestamp时间字段属性TIMESTAMP WITH TIME ZONE，可以把数据库服务器所在的时间转化为当前时区的时间，比如当伦敦时间为20180101 02:00:00，则在北京可以看到该时间为20180101 09:00:00。
TZ_OFFSET returns the time zone offset corresponding to the argument based on the date the statement is executed.
TZ_OFFSET根据输入的参数值，返回时区与0时区相差的小时和分钟数。
SELECT TZ_OFFSET('Asia/Shanghai'),TZ_OFFSET('US/Michigan'),TZ_OFFSET('Europe/London') FROM DUAL;
+08:00 -04:00 +01:00
select TZNAME from V$TIMEZONE_NAMES--查询所有的time_zone_name
FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value.
FROM_TZ将时间戳值和时区转换为具有时区值的时间戳。
SELECT FROM_TZ(TIMESTAMP'2018-04-30 08:00:00','Asia/Shanghai'),FROM_TZ(TIMESTAMP'2018-04-30 08:00:00','US/Michigan'),FROM_TZ(TIMESTAMP'2018-04-30 08:00:00','Europe/London') FROM DUAL;
2018/4/30 8:00:00.000000000 +08:00 2018/4/30 8:00:00.000000000 -04:00 2018/4/30 8:00:00.000000000 +01:00
SELECT FROM_TZ(LOCALTIMESTAMP,'Asia/Shanghai'),FROM_TZ(LOCALTIMESTAMP,'US/Michigan'),FROM_TZ(LOCALTIMESTAMP,'Europe/London') FROM DUAL;
2018/4/17 20:14:47.519347 +08:00 2018/4/17 20:14:47.519347 -04:00 2018/4/17 20:14:47.519347 +01:00
SELECT FROM_TZ(SYSTIMESTAMP,'Asia/Shanghai'),FROM_TZ(SYSTIMESTAMP,'US/Michigan'),FROM_TZ(SYSTIMESTAMP,'Europe/London') FROM DUAL;--因为SYSTIMESTAMP本身带有时区，所以报错ORA-00932: 数据类型不一致: 应为 TIMESTAMP, 但却获得 TIMESTAMP WITH TIME ZONE。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30126024/viewspace-2153032/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
oracle外键约束的总结
下一篇：
查询某表最近5天内最后一次dml的时间
请登录后发表评论
登录
全部评论
