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

日期

ektrontek 2018-02-20 10:41:43 ‧ 7222 瀏覽

分享至

請教大家
透過下列的語法

我試著將字串欄位轉換成日期格式來排序，但為什麼都會出現下列的錯誤訊息呢？煩請大家指點迷津，感謝
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

其前面會有個數字-日期顯示格式，無法使用to_date轉換

froce iT邦大師 1 級 ‧ 2018-02-20 12:22:56 檢舉

我比較好奇當初為啥要用string去存。

froce iT邦大師 1 級 ‧ 2018-02-20 12:45:36 檢舉

然後這是 to_date()，你應該會用到。
https://www.techonthenet.com/postgresql/functions/to_date.php

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

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()不就好了,這個函數很基本.不要都不動腦子啊.字串有多,就用字串函數處理啊.

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

ektrontek iT邦研究生 1 級 ‧ 2018-02-20 17:46:13 檢舉

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

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

一級屠豬士 iT邦新手 2 級 ‧ 2018-02-20 19:59:04 檢舉

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

一級屠豬士 iT邦新手 2 級 ‧ 2018-02-20 20:20:07 檢舉

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

```
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邦研究生 1 級 ‧ 2018-02-21 20:24:02 檢舉

感謝幫忙

登入發表回應