我想MM/dd/yyyy HH:mm:ss將以下列的格式轉換為 Hive
| 成人禮 |
|---|
| 2022/3/22 0:00:00 |
| 11-08-21 0:00 |
| 09-07-21 |
| 10/27/2023 0:00:00 |
uj5u.com熱心網友回復：
使用 from_unixtime(unix_timestamp(maturity_date,format_from),format_to) 合并：
with mydata as (--example of dates to be converted to MM/dd/yyyy HH:mm:ss
select '3/22/2022 0:00:00' as Maturity_Date union all
select '11-08-21 0:00' union all
select '09-07-21' union all
select '10/27/2023 0:00:00'
)
select coalesce(from_unixtime(unix_timestamp(Maturity_Date,'M/dd/yyyy H:mm:ss'), 'MM/dd/yyyy HH:mm:ss'),
from_unixtime(unix_timestamp(Maturity_Date,'dd-MM-yy H:mm'), 'MM/dd/yyyy HH:mm:ss'),
from_unixtime(unix_timestamp(Maturity_Date,'dd-MM-yy'), 'MM/dd/yyyy HH:mm:ss')
)
from mydata
結果：
03/22/2022 00:00:00
08/11/2021 00:00:00
07/09/2021 00:00:00
10/27/2023 00:00:00
您可能需要執行更嚴格的檢查來檢測格式，用例...然后...：
case when Maturity_Date rlike '^([1-9]|1[012])/(0[1-9]|[12][0-9]|3[01])/((19|20)\\d\\d) \\d:\\d{2}:\\d{2}$'
then from_unixtime(unix_timestamp(Maturity_Date,'M/dd/yyyy H:mm:ss'), 'MM/dd/yyyy HH:mm:ss')
when Maturity_Date rlike '^(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])-(\\d{2}) \\d:\\d{2}$'
then from_unixtime(unix_timestamp(Maturity_Date,'dd-MM-yy H:mm'), 'MM/dd/yyyy HH:mm:ss')
when Maturity_Date rlike '^(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])-(\\d{2})$'
then from_unixtime(unix_timestamp(Maturity_Date,'dd-MM-yy'), 'MM/dd/yyyy HH:mm:ss')
end
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/316020.html