資料清洗小記：字串轉日期：時間戳記引來的問題，小記引來

資料清洗小記：字串轉日期：時間戳記引來的問題，小記引來

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處，否則有權追究著作權法律責任。

深藍的blog：http://blog.csdn.net/huangyanlong/article/details/46513787

 

【背景】

       在進行資料幫浦時，源端“時間意義”欄位資料為“時間戳記格式”，而且欄位類型為字串類型。但是目標端要求，進入資料為date類型，需要清洗。

 

【解決】

       遇到這種問題，起初可能會比較棘手，但通過對“時間戳記格式”進行簡單分析後，我們會發現有法可施。

例如下面這種格式：

'14-JUN-15 08.23.35.048000 PM +08:00','DD MON YYYY HH.MI AM'

可以使用這樣的處理方式：

select to_date(replace(substr('14-JUN-15 08.23.35.048000 PM +08:00',1,18),'.',':')||substr('14-JUN-15 08.23.35.048000 PM +08:00',26,3),'DD-MON-YY HH:MI:SS AM') from dual;

處理後的結果如下：

2015/6/14 20:23:35

 

        上面的處理方法，其實很簡單，就是把時間戳記拆分成兩部分，然後分別處理後做了一個拼接而已，如下這樣分步查詢出來就一目瞭然了，如下：

再來看看正確使用時間戳的方式，如下舉例：

create table 實驗表 (ID varchar2(32) default sys_guid(),DATE_TIMESTAMP date default systimestamp,memo varchar2(32));insert into 實驗表 (memo) values(1);insert into 實驗表 (memo) values(2);insert into 實驗表 (memo) values(3);commit;select * from 實驗表;

也可以使用下面這樣的方式，如下：

create table 實驗表2(ID varchar2(32) default sys_guid(),DATE_TIMESTAMP date default current_timestamp,memo varchar2(32));insert into 實驗表2 (memo) values(1);commit;select * from 實驗表2;

        通過上面示範，我們可以使用current_timestamp或systimestamp獲得時間戳記，可以看到在以date為類型的欄位插入時間戳記時，顯示的為正常的時間格式，因為已經被oracle進行了隱式轉換了，但是如果單獨查詢會是如何呢？按下面的方式查詢：

select sessiontimezone,current_timestamp from dual;

        可以看到，時間戳記預設是以“14-JUN-15 08.23.35.048000 PM +08:00”格式顯示的，這也就意味著如果建表時，“該欄位你沒有date類型”或“用了varchar2類型但是沒有強制轉換”的話，存入到該欄位的內容是這樣格式的。當把該欄位抽取到以date為類型的表時，便會遇到問題。

【實驗】

構建源端資料表（實驗表3）、目標表，如下：

create table 實驗表3(ID varchar2(32) default sys_guid(),DATE_TIMESTAMP varchar2(50) default current_timestamp,memo varchar2(50));select * from 實驗表3;insert into 實驗表3 (memo) values(1);insert into 實驗表3 (memo) values(2);insert into 實驗表3 (memo) values(3);insert into 實驗表3 (memo) values(4);commit;create table 目標表(ID varchar2(32),DATE_TIME date,memo varchar2(50));

未處理的抽取操作：

添加“清洗”操作後，抽取資料操作：

INSERT /*+append*/ INTO 目標表 nologgingSELECTID ID,to_date(replace(substr(DATE_TIMESTAMP,1,18),'.',':')||substr(DATE_TIMESTAMP,26,3),'DD-MON-YY HH:MI:SS AM') DATE_TIME,MEMO MEMOFROM 實驗表3;commit;select * from 目標表;

資料在抽取時已經完成了清洗，並且抽取過來了。

  

小知識，簡而記之。

 

*******************************************藍的成長記系列****************************************************

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處（http://blog.csdn.net/huangyanlong）。

藍的成長記——追逐DBA（1）：奔波於路上，挺進山東

藍的成長記——追逐DBA（2）：安裝！安裝！久違的記憶，引起我對DBA的重新認知

藍的成長記——追逐DBA（3）：古董上操作，資料匯入匯出成了問題

藍的成長記——追逐DBA（4）：追憶少年情愁，再探oracle安裝（Linux下10g、11g）

藍的成長記——追逐DBA（5）：不談技術談業務，惱人的應用系統

藍的成長記——追逐DBA（6）： 做事與做人：小技術，大為人

藍的成長記——追逐DBA（7）：基礎命令，地基之石

藍的成長記——追逐DBA（8）：重拾SP報告，回憶oracle的STATSPACK實驗

藍的成長記——追逐DBA（9）：國慶漸去，追逐DBA，新規劃，新啟程

藍的成長記——追逐DBA（10）：飛刀防身，熟絡而非專長：擺弄中介軟體Websphere

藍的成長記——追逐DBA（11）：回家後的安逸，暈暈乎乎醒了過來

藍的成長記——追逐DBA（12）：七天七收穫的SQL

藍的成長記——追逐DBA（13）：協調硬體廠商，六個故事：所見所感的“伺服器、儲存、交換器......”

藍的成長記——追逐DBA（14）：難忘的“雲”端，起步的hadoop部署

藍的成長記——追逐DBA（15）：以為FTP很“簡單”，誰成想一波三折

藍的成長記——追逐DBA（16）：DBA也喝酒，被捭闔了

藍的成長記——追逐DBA（17）：是分享，還是消費，在後IOE時代學會成長

******************************************************************************************************************

 

********************************************足球與oracle系列*************************************************

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處（http://blog.csdn.net/huangyanlong）。

足球與oracle系列（1）：32路諸侯點兵，oracle32進程聯盟 之A組巴西SMON進程的大局觀

足球與oracle系列（2）：巴西揭幕戰預演，oracle體繫結構雜談

足球與oracle系列（3）：oracle進程排名，世界盃次回合即將戰罷！

足球與oracle系列（4）：從巴西慘敗於德國，想到，差異的RAC拓撲對比！ 

足球與oracle系列（5）：fifa14遊戲缺失的directX庫類比於oracle的rpm包！

足球與oracle系列（6）：伴隨建庫的亞洲杯——加油中國隊

******************************************************************************************************************