SQL 如何更改配置单元中的日期格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37558697/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to change date format in hive?
提问by yanachen
My table in hive has a filed of date in the format of '2016/06/01'. but i find that it is not in harmory with the format of '2016-06-01'. They can not compare for instance. Both of them are string . So I want to know how to make them in harmory and can compare them. Or on the other hand, how to change the '2016/06/01' to '2016-06-01' so that them can compare.
我在 hive 中的表的日期格式为“2016/06/01”。但我发现它与“2016-06-01”的格式不符。例如，他们无法比较。它们都是 string 。所以我想知道如何让它们和谐并可以比较它们。或者另一方面，如何将“2016/06/01”更改为“2016-06-01”以便它们进行比较。
Many thanks.
非常感谢。
回答by Farooque
To convert date string from one format to another you have to use two date function of hive
要将日期字符串从一种格式转换为另一种格式，您必须使用 hive 的两个日期函数
unix_timestamp(string date, string pattern)convert time string with given pattern to unix time stamp (in seconds), return 0 if fail.from_unixtime(bigint unixtime[, string format])converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone.
unix_timestamp(string date, string pattern)将具有给定模式的时间字符串转换为 unix 时间戳（以秒为单位），如果失败则返回 0。from_unixtime(bigint unixtime[, string format])将 unix epoch (1970-01-01 00:00:00 UTC) 的秒数转换为表示当前系统时区中该时刻时间戳的字符串。
Using above two function you can achieve your desired result.
使用以上两个功能，您可以达到您想要的结果。
The sample input and output can be seen from below image:
示例输入和输出可以从下图中看到：
The final query is
最后的查询是
select from_unixtime(unix_timestamp('2016/06/01','yyyy/MM/dd'),'yyyy-MM-dd') from table1;
where table1 is the table name present in my hive database.
其中 table1 是我的 hive 数据库中的表名。
I hope this help you!!!
我希望这对你有帮助！！！
回答by Ani Menon
Use :
用 ：
unix_timestamp(DATE_COLUMN, string pattern)
The above command would help convert the date to unix timestamp format which you may format as you want using the Simple Date Function.
上面的命令将有助于将日期转换为 unix 时间戳格式，您可以使用Simple Date Function将其格式化为您想要的格式。
回答by Terminator17
Let's say you have a column 'birth_day' in your table which is in your format, you should use the following query to convert birth_day into the required format.
假设您的表中有一个“birth_day”列采用您的格式，您应该使用以下查询将birth_day 转换为所需的格式。
date_Format(birth_day, 'yyyy-MM-dd')
You can use it in a query in the following way
您可以通过以下方式在查询中使用它
select * from yourtable
where
date_Format(birth_day, 'yyyy-MM-dd') = '2019-04-16';
回答by Ashah
cast(to_date(from_unixtime(unix_timestamp(yourdate , 'MM-dd-yyyy'))) as date)
