[20170315]資料型別interval day to second

[20170315]資料型別interval day to second.txt

--上午監測dg應用情況時,查詢如下檢視:

1.前提:
$ cat dg_status.sql
column name format a30;
column value format a30
column unit format a30
column time_computed format a30
select name,value,unit,to_char(to_date(time_computed,'mm/dd/yyyy hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') time_computed from v$dataguard_stats;

SYS@bookdg> @ &r/dg/dg_status.sql
NAME                           VALUE                          UNIT                           TIME_COMPUTED
------------------------------ ------------------------------ ------------------------------ ------------------------------
transport lag                  +00 00:00:00                   day(2) to second(0) interval   2017-03-15 14:45:44
apply lag                      +00 02:40:13                   day(2) to second(0) interval   2017-03-15 14:45:44
apply finish time              +00 00:00:04.201               day(2) to second(3) interval   2017-03-15 14:45:44
estimated startup time         7                              second                         2017-03-15 14:45:44

--//name='apply lag',values=+00 02:40:13,單位unit= day(2) to second(0) interval.
--//才知道oracle還有一種資料型別是interval day to second . 裡面的數字表示精度.簡單探究一下,我主要的目的是轉換成秒.

2.測試:
SCOTT@book> @ &r/ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t ( a interval day to second);
Table created.

SCOTT@book> @ &r/desc t
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        INTERVAL DAY(2) TO SECOND(6)
--//預設不指定 天數精度是2,秒6.

SCOTT@book> insert into t values (TO_DSINTERVAL('+00 02:40:13')) ;
1 row created.

SCOTT@book> insert into t values (INTERVAL '4 5:12:10.222' DAY TO SECOND(3) ) ;
1 row created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select * from t;
A
--------------------
+00 02:40:13.000000
+04 05:12:10.222000

--//簡單探究轉儲:
SCOTT@book> select a c20,dump(a,16) c50 ,dump(a,10) c50 from t;
C20                  C50                                                C50
-------------------- -------------------------------------------------- --------------------------------------------------
+00 02:40:13.000000  Typ=183 Len=11: 80,0,0,0,3e,64,49,80,0,0,0         Typ=183 Len=11: 128,0,0,0,62,100,73,128,0,0,0
+04 05:12:10.222000  Typ=183 Len=11: 80,0,0,4,41,48,46,8d,3b,73,80      Typ=183 Len=11: 128,0,0,4,65,72,70,141,59,115,128

--//猜測一下0x80估計表示+, 中間3位表示天數,後面的時分秒 再減去60就對上.
--//小數點表示的nanoseconds,需要長度11.

SCOTT@book> @ &r/10to16  222000000
10 to 16 HEX   REVERSE16
-------------- ------------------
000000d3b7380 0x80733b0d

--//只不過前面的0d變成8d.

3.如何轉換成秒呢?
--參考的連結:

SELECT EXTRACT(DAY FROM a) * 86400
+ EXTRACT(HOUR FROM a) * 3600
+ EXTRACT(MINUTE FROM a) * 60
+ EXTRACT(SECOND FROM a) INTERVAL
FROM t;

  INTERVAL
----------
      9613
364330.222

--//感覺有點麻煩!簡單一點寫成這樣.

SCOTT@book> select (sysdate-(sysdate- a))*86400 ss from t;
        SS
----------
      9613
    364331
--//注一定要這樣寫,如果寫成這樣.

SCOTT@book> select (sysdate-sysdate + a))*86400 ss from t;
select (sysdate-sysdate + a))*86400 ss from t
                            *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

--//另外還有函式TO_DSINTERVAL轉換成這種型別.

SYS@bookdg> select (sysdate-(sysdate- TO_DSINTERVAL(value)))*86400 ss,v$dataguard_stats.* from v$dataguard_stats where name ='apply lag';
        SS NAME                           VALUE                          UNIT                           TIME_COMPUTED                  DATUM_TIME
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
     12186 apply lag                      +00 03:23:06                   day(2) to second(0) interval   03/15/2017 15:28:36            03/15/2017 15:28:35