以“MM / DD / YY”格式转换日期字符串 [英] Convert date string in “MM/DD/YY” format
问题描述
Hive Metastore包含一个名为problem1的数据库,其中包含一个名为customer的表。客户记录包含9000万个客户记录(90,000,000),每个记录都有一个生日字段。
样本数据(生日粗体)
1904287 Christopher Rodriguez 2003年1月11日
96391595 Thomas Stewart 6/17/1969
2236067 John Nelson 08/22/54
对于解决方案表中的每一行,用MM / DD / YY中的日期字符串替换生日字段的内容格式。
MM是零填充月份(01-12),
DD是填零日期(01-31) ,
YY是零填充的2位数年份(00-99)
select from_unixtime(来自temp1的unix_timestamp(doj,'MM / dd / yyyy'));
你不能用通用的方式来做,你将不得不测试每种可能的情况。
您可以这样做
select coalesce(
from_unixtime(unix_timestamp(doj,MMM dd ,yyyy),MM / dd / yy),
from_unixtime(unix_timestamp(doj,MM / dd / yyyy),MM / dd / yy),
from_unixtime doj,MM / dd / yy),MM / dd / yy)
)
I just saw this example.How can I get this solved.
The Hive metastore contains a database named problem1 that contains a table named customer. The customertable contains 90 million customer records (90,000,000), each with a birthday field.
Sample Data (birthday is in bold)
1904287 Christopher Rodriguez Jan 11, 2003
96391595 Thomas Stewart 6/17/1969
2236067 John Nelson 08/22/54
For every row in the solution table, replace the contents of the birthday field with a date string in "MM/DD/YY" format.
MM is the zero-padded month (01-12),
DD is the zero-padded day (01-31),
YY is the zero-padded 2-digit year (00-99)
select from_unixtime(unix_timestamp(doj,'MM/dd/yyyy')) from temp1;
You can't do it in a generic way, you'll have to test each possible case. You can do it like this
select coalesce(
from_unixtime(unix_timestamp(doj ,"MMM dd, yyyy"), "MM/dd/yy"),
from_unixtime(unix_timestamp(doj ,"MM/dd/yyyy"), "MM/dd/yy"),
from_unixtime(unix_timestamp(doj ,"MM/dd/yy"), "MM/dd/yy")
)
这篇关于以“MM / DD / YY”格式转换日期字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!