在Hive中将Date列的不同Timestamp格式转换为一个格式

Question

我想在Hive中将下面列的格式转换为MM/dd/yyyy HH:mm:ss

tbody> <<tr>

Maturity_Date
3/22/2022 0:00:00
11-08-21 0:00
09-07-21
10/27/2023 0:00:00

使用from_unixtime(unix_timestamp(maturity_date,format_from)，format_to) + coalesce:

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