Oracle 11g Sql从blob字段转换日期

神谕 stores dates in tables as 7-bytes

字节1-世纪+100
字节2-(年份MOD 100)+100
字节3-月
字节4-天
字节5-小时+1
字节6-分钟+1
字节7-秒+1

所以 120,116,3,6,1,1,1 转换为:

字节1-世纪=120-100=20
字节2-年=116-100=16
字节3-月=3
字节4-天=6
字节5-小时=1-1=0
字节6-分钟=1-1=0
字节7-秒=1-1=0

所以 2016-03-06T00:00:00

Oracle设置 :

CREATE TABLE file_upload ( file_blob BLOB );

INSERT INTO file_upload VALUES (
  utl_raw.cast_to_raw(
    CHR(120) || CHR(116) || CHR(3) || CHR(6) || CHR(1) || CHR(1) || CHR(1)
  )
);

查询 :

SELECT DUMP( DBMS_LOB.SUBSTR( file_blob, 7, 1 ) ) AS dmp,
       TO_DATE(
         TO_CHAR(
           ( ASCII( SUBSTR( chars, 1, 1 ) ) - 100 ) * 100
             + ASCII( SUBSTR( chars, 2, 1 ) ) - 100,
           '0000'
         )
         || TO_CHAR( ASCII( SUBSTR( chars, 3, 1 ) ), '00' )
         || TO_CHAR( ASCII( SUBSTR( chars, 4, 1 ) ), '00' )
         || TO_CHAR( ASCII( SUBSTR( chars, 5, 1 ) ) - 1, '00' )
         || TO_CHAR( ASCII( SUBSTR( chars, 6, 1 ) ) - 1, '00' )
         || TO_CHAR( ASCII( SUBSTR( chars, 7, 1 ) ) - 1, '00' ),
         'YYYYMMDDHH24MISS'
       ) AS converted_date
FROM   (
  SELECT file_blob,
         UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR( file_blob, 7, 1 ) ) AS chars
  FROM   file_upload
);

输出 :

DMP                             CONVERTED_DATE    
------------------------------- -------------------
Typ=23 Len=7: 120,116,3,6,1,1,1 2016-03-06 00:00:00