这一节主要介绍 TIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE,INTERVAL YEAR TO MONTH,INTERVAL DAY TO SECOND,RAW类型.

环境:
os:centos 6.6
db version:11.2.0.4

#查看地区语言字符集信息
select * from v$nls_parameters;
/*
PARAMETER    VALUE
NLS_LANGUAGE    AMERICAN
NLS_TERRITORY    AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS    .,
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT    DD-MON-RR
NLS_DATE_LANGUAGE    AMERICAN
NLS_CHARACTERSET    AL32UTF8
NLS_SORT    BINARY
NLS_TIME_FORMAT    HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT    HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY    $
NLS_NCHAR_CHARACTERSET    AL16UTF16
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP    FALSE
*/

#建测试表,里面定义TIMESTAMP,TIMESTAMP WITH TIME ZONE,TIMESTAMP WITH LOCAL TIME ZONE,INTERVAL YEAR TO MONTH,INTERVAL DAY TO SECOND,RAW类型.
create table SCOTT.TB_TYPE02
(
  t_timestmap     TIMESTAMP(6),
  t_timestmap_tz  TIMESTAMP(6) WITH TIME ZONE,
  t_timestmap_ltz TIMESTAMP(6) WITH LOCAL TIME ZONE,
  t_interval_ytm  INTERVAL YEAR(2) TO MONTH,
  t_interval_dts  INTERVAL DAY(2) TO SECOND(6),
  t_raw           RAW(10)
);

#插入测试数据
insert into SCOTT.TB_TYPE02
select current_timestamp,current_timestamp,current_timestamp,INTERVAL '12-2' YEAR(2) TO MONTH,INTERVAL '12 00:00:01' DAY(2) TO SECOND(6),
utl_raw.cast_to_raw('selectshen') from dual;
commit;
 
#timestamp类型
#timestamp类型为固定长度,根据是否存储毫秒len=11或len=7,有毫秒位没有时区,内部类型号Typ=180
#前七个字节存放年月日时分秒,同data类型,前两个字节为世纪和年,世纪和年存储是有加100,接下来两个字节是月和日,最后三个字节是24小时格式的时分秒,时分秒存储是都有加1.
#后四个字节存放毫秒,毫秒位存储的16进制值合并后转为10进制即为毫秒值
select t_timestmap,dump(t_timestmap),dump(t_timestmap,16) from SCOTT.TB_TYPE02;
/*
T_TIMESTMAP    DUMP(T_TIMESTMAP)    DUMP(T_TIMESTMAP,16)
04-FEB-16 02.37.37.825548 PM    Typ=180 Len=11: 120,116,2,4,15,38,38,49,52,220,224    Typ=180 Len=11: 78,74,2,4,f,26,26,31,34,dc,e0
*/
#04-FEB-16 02.37.37.825548 PM=120,116,2,4,15,38,38,49,52,220,224(十进制),其中世纪120-100=20,年=116-100=16,月=2,日=4,时=15-1=14,分=38-1=37,秒=38-1=37
#04-FEB-16 02.37.37.825548 PM=78,74,2,4,f,26,26,31,34,dc,e0(十六进制)
毫秒位十进制49,52,220,224=十六进制31,34,dc,e0
select to_number('3134dce0','xxxxxxxx') from dual;
/*
TO_NUMBER('3134DCE0','XXXXXXXX'
825548000
*/
小数位825548000->0.825548000=0.825548

#可以看到,如果把t_timestmap更新为sysdate,长度len=7,不存储毫秒
update SCOTT.TB_TYPE02 set t_timestmap=sysdate;
select t_timestmap,dump(t_timestmap) from SCOTT.TB_TYPE02;
/*
T_TIMESTMAP    DUMP(T_TIMESTMAP)
04-FEB-16 02.39.17.000000 PM    Typ=180 Len=7: 120,116,2,4,15,40,18
*/


#TIMESTAMP WITH TIME ZONE类型
#TIMESTAMP WITH TIME ZONE类型为固定长度len=13,有毫秒位和时区,内部类型号Typ=181
#前七个字节存放年月日时分秒,同data类型,前两个字节为世纪和年,世纪和年存储是有加100,接下来两个字节是月和日,最后三个字节是24小时格式的时分秒,时分秒存储是都有加1.
#接下来四个字节存放毫秒,毫秒位存储的16进制值合并后转为10进制即为毫秒值
#最后两个字节存放时区小时和时区分钟
select t_timestmap_tz,dump(t_timestmap_tz),DUMP(t_timestmap_tz,16)from SCOTT.TB_TYPE02;
/*
T_TIMESTMAP_TZ    DUMP(T_TIMESTMAP_TZ)    DUMP(T_TIMESTMAP_TZ,16)
04-FEB-16 02.31.35.276282 PM +08:00    Typ=181 Len=13: 120,116,2,4,7,32,36,16,119,186,144,28,60    Typ=181 Len=13: 78,74,2,4,7,20,24,10,77,ba,90,1c,3c
*/
#04-FEB-16 02.31.35.276282 PM +08:00=120,116,2,4,7,32,36,16,119,186,144,28,60(十进制)
年月时分秒算法同TIMESTAMP类型,略.
时区小时=倒数第二个字节-20=28-20=+8,时区分钟=倒数第一个字节-60=60-60=0.
算出来的时间是2016-02-04 6:31:35.276282加上时区小时和分就是2016-02-04 14:31:35.276282=04-FEB-16 02.31.35.276282 PM +08:00


#TIMESTAMP WITH LOCAL TIME ZONE类型
#TIMESTAMP WITH LOCAL TIME ZONE类型为固定长度len=11,有毫秒位,内部类型号Typ=231
#它存储在数据库中的是时区小时0时区分钟0的时间,会通过当前会话的时区显示时间
#前七个字节存放年月日时分秒,同data类型,前两个字节为世纪和年,世纪和年存储是有加100,接下来两个字节是月和日,最后三个字节是24小时格式的时分秒,时分秒存储是都有加1.
#接下来四个字节存放毫秒,毫秒位存储的16进制值合并后转为10进制即为毫秒值
select t_timestmap_ltz,dump(t_timestmap_ltz),dump(t_timestmap_ltz,16) from SCOTT.TB_TYPE02;
/*
T_TIMESTMAP_LTZ    DUMP(T_TIMESTMAP_LTZ)    DUMP(T_TIMESTMAP_LTZ,16)
04-FEB-16 02.31.35.276282 PM    Typ=231 Len=11: 120,116,2,4,7,32,36,16,119,186,144    Typ=231 Len=11: 78,74,2,4,7,20,24,10,77,ba,90
*/
#上面并不有时区信息,dump中存储的是6点,显示的时间是下午2点,因为当前会话时区+8:00


#INTERVAL YEAR TO MONTH类型
#INTERVAL YEAR TO MONTH类型固定长度len=5,内部类型号Typ=182.
#前四个字节存放年,dump的值有加0x80000000
#最后一个字节存放月,dump的值有加60
select t_interval_ytm,dump(t_interval_ytm) from SCOTT.TB_TYPE02;
/*
T_INTERVAL_YTM    DUMP(T_INTERVAL_YTM)
+12-02    Typ=182 Len=5: 128,0,0,12,62
*/

#INTERVAL DAY TO SECOND类型
#INTERVAL DAY TO SECOND类型固定长度len=11,内部类型号Typ=183
#前四个字节存放天,dump的值有加0x80000000
#接下来三个字节存放时分秒,dump的值有各加60
#最后四个字节存放毫秒,dump的值有加0x80000000
select t_interval_dts,dump(t_interval_dts) from SCOTT.TB_TYPE02;
/*
T_INTERVAL_DTS    DUMP(T_INTERVAL_DTS)
+12 00:00:01.000000    Typ=183 Len=11: 128,0,0,12,60,60,61,128,0,0,0
*/

#raw类型
#raw类型存放二进制数据,可变长度,最大长度2000字节,部类型号Typ=23
#显示的值和dump的16进程值相同
select t_raw,dump(t_raw),dump(t_raw,16) from SCOTT.TB_TYPE02;
/*
T_RAW    DUMP(T_RAW)    DUMP(T_RAW,16)
73656C6563747368656E    Typ=23 Len=10: 115,101,108,101,99,116,115,104,101,110    Typ=23 Len=10: 73,65,6c,65,63,74,73,68,65,6e
*/