iT邦幫忙

0

postgresql文字欄位轉日期使用的問題

請教大家
透過下列的語法
https://ithelp.ithome.com.tw/upload/images/20180220/20003705121ch0FZnk.png

我試著將字串欄位轉換成日期格式來排序,但為什麼都會出現下列的錯誤訊息呢?煩請大家指點迷津,感謝
invalid input syntax for type date:"1-22 Nov 2017,2:34 PM"

註:文字內容格式如下
1-22 Nov 2017, 2:34 PM
1-4 Dec 2017, 7:01 PM
1-5 Dec 2017, 8:22 PM
https://ithelp.ithome.com.tw/upload/images/20180220/20003705nil0TWHjAL.png
其前面會有個數字-日期顯示格式,無法使用to_date轉換

froce iT邦大師 5 級 ‧ 2018-02-20 12:22:56 檢舉
我比較好奇當初為啥要用string去存。
froce iT邦大師 5 級 ‧ 2018-02-20 12:45:36 檢舉
然後這是 to_date(),你應該會用到。
https://www.techonthenet.com/postgresql/functions/to_date.php

1 個回答

1
一級屠豬士
iT邦新手 2 級 ‧ 2018-02-20 16:02:10
最佳解答
select substring('1-22 Nov 2017, 2:34 PM' from 3)::date as datestyle
     , substring('1-22 Nov 2017, 2:34 PM' from 3)::timestamp as datetimestyle;

 datestyle  |    datetimestyle    
------------+---------------------
 2017-11-22 | 2017-11-22 14:34:00

搭配個substring()不就好了,這個函數很基本.不要都不動腦子啊.字串有多,就用字串函數處理啊.

看更多先前的回應...收起先前的回應...
ektrontek iT邦研究生 3 級 ‧ 2018-02-20 17:46:13 檢舉

感謝,但substring有想過此用法,但是它不是固定都從第3碼才開始變日期格式,且它實際儲存的內容是

<string>1-22 NOV 2017 2:34 PM</string>

https://ithelp.ithome.com.tw/upload/images/20180220/20003705b4cTfHs4wP.png

那你就實際的,具有代表性的,例如有幾種組合都貼出來啊.
你一開始就貼一碼,你是要我用神通過去你那裡看喔.

select '<string>1-22 NOV 2017 2:34 PM</string>'
     , '<string>23-19 Dec 2017 11:04 PM</string>'
     , trim(leading '<string>' from '<string>1-22 NOV 2017 2:34 PM</string>')
     , trim(leading '<string>' from '<string>23-19 Dec 2017 11:04 PM</string>');

-[ RECORD 1 ]--------------------------------------
?column? | <string>1-22 NOV 2017 2:34 PM</string>
?column? | <string>23-19 Dec 2017 11:04 PM</string>
ltrim    | 1-22 NOV 2017 2:34 PM</string>
ltrim    | 23-19 Dec 2017 11:04 PM</string>

這樣不就把 <string>去掉了.
也可以改用 ltrim()

select '<string>1-22 NOV 2017 2:34 PM</string>'
     , '<string>23-19 Dec 2017 11:04 PM</string>'
     , ltrim('<string>1-22 NOV 2017 2:34 PM</string>', '<string>')
     , ltrim('<string>23-19 Dec 2017 11:04 PM</string>', '<string>');
     
-[ RECORD 1 ]--------------------------------------
?column? | <string>1-22 NOV 2017 2:34 PM</string>
?column? | <string>23-19 Dec 2017 11:04 PM</string>
ltrim    | 1-22 NOV 2017 2:34 PM</string>
ltrim    | 23-19 Dec 2017 11:04 PM</string>

再來搭配 rtrim()

select rtrim(ltrim('<string>1-22 NOV 2017 2:34 PM</string>', '<string>'), '</string>')
     , rtrim(ltrim('<string>23-19 Dec 2017 11:04 PM</string>', '<string>'), '</string>');     
     
         rtrim         |          rtrim          
-----------------------+-------------------------
 1-22 NOV 2017 2:34 PM | 23-19 Dec 2017 11:04 PM

到這裡已經把 <string> </string> 去除了.

select split_part(rtrim(ltrim('<string>1-22 NOV 2017 2:34 PM</string>', '<string>'), '</string>'), '-', 2)
     , split_part(rtrim(ltrim('<string>23-19 Dec 2017 11:04 PM</string>', '<string>'), '</string>'), '-', 2);   
     
     split_part      |      split_part      
---------------------+----------------------
 22 NOV 2017 2:34 PM | 19 Dec 2017 11:04 PM

到這裡就可以轉型了

select split_part(rtrim(ltrim('<string>1-22 NOV 2017 2:34 PM</string>', '<string>'), '</string>'), '-', 2)::timestamp
     , split_part(rtrim(ltrim('<string>23-19 Dec 2017 11:04 PM</string>', '<string>'), '</string>'), '-', 2)::timestamp; 
     
     split_part      |     split_part      
---------------------+---------------------
 2017-11-22 14:34:00 | 2017-12-19 23:04:00
ektrontek iT邦研究生 3 級 ‧ 2018-02-21 20:24:02 檢舉

感謝幫忙

我要發表回答

立即登入回答