timestamp 的存储与timestamp with timezone

Timestamp with time zone是指当地时区与格林尼制时间的偏移。

SQL> create table t_timestamp(t1 date,

t2 timestamp(9),

t3 timestamp(9) with local time zone,

t4 timestamp(9) with time zone)

SQL> select dbtimezone from dual;

DBTIMEZONE

+08:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

+08:00

SQL> insert into t_timestamp

values

(to_date('2008-01-01 1:0:1','yyyy-mm-dd hh24:mi:ss'),

timestamp'2008-01-01 1:0:1.000000001',

timestamp'2008-01-01 1:0:1.000000001',

timestamp'2008-01-01 1:0:1.000000001')

成功

SQL> select t2,t3,54 from t_timestamp;

T2 T3 T4

01-1月-0801.00.01.000000001上午

01-1月-0801.00.01.000000001上午

01-1月-0801.00.01.000000001上午+08:00

SQL> alter session set time_zone='+6:00'

SQL> select * from t_timestamp;

T2 T3 T4

01-1月-0801.00.01.000000001上午

31-12月-0711.00.01.000000001下午

01-1月-0801.00.01.000000001上午+08:00

可以看到timestamp with local timezone与sessiontimezone有关。

而timestamp with time zone与dbtimezone有关

CURRENT_TIMEstamp返回的就是timestamp with local timezone.

Systimestamp返回的是timestamp类型

Timestamp的存储

Timestamp(precision)的precision是指timestamp的精度，取值0-9，默认为6，而timestamp的精度达到billion（十亿）分之一秒，是以11个字节存储。

SQL> insert into t_timestamp(t2) values (timestamp'2008-01-01 1:0:1.000000001');

1 row inserted

SQL> insert into t_timestamp(t2) values (timestamp'2008-01-01 1:0:1.000000002');

1 row inserted

SQL> insert into t_timestamp(t2) values (timestamp'2008-01-01 1:0:1.000000099');

1 row inserted

SQL> insert into t_timestamp(t2) values (timestamp'2008-01-01 1:0:1.000000255');

1 row inserted

SQL> commit;

Commit complete

SQL> select t2,dump(t2) from t_timestamp;

T2 DUMP(T2)

-------------------------------------- -------------------------------------------

01-1月-0801.00.01.000000001上午 Typ=180 Len=11: 120,108,1,1,2,1,2,0,0,0,1

01-1月-0801.00.01.000000002上午 Typ=180 Len=11: 120,108,1,1,2,1,2,0,0,0,2

01-1月-0801.00.01.000000099上午 Typ=180 Len=11: 120,108,1,1,2,1,2,0,0,0,99

01-1月-0801.00.01.000000255上午 Typ=180 Len=11: 120,108,1,1,2,1,2,0,0,0,255

会发现date部分的存储就是date类型数据的存储格式,7个字节。而timestamp精度的存储是以无符号整数的格式进行存储，而4个字节能达到billions的精度：255的4次幂。

Trackback URL: http://blog.itpub.net//trackback.php?id=464771