How to change the date format in the hive?

Question

My table in the hive has a filing date in the format '2016/06/01'. but I believe that this is not in the format of '2016-06-01'. For example, they cannot compare. Both are strings. Therefore, I want to know how to make them in harmony and compare them. Or, on the other hand, how to change “2016/06/01” to “2016-06-01” so that they can compare.

Many thanks.

Answer 1

To convert a date string from one format to another, you need to use two hive date functions

• unix_timestamp(string date, string pattern) convert the temporary string with the given pattern for the unix timestamp (in seconds), return 0 if it fails.
• from_unixtime(bigint unixtime[, string format]) converts the number of seconds from the unix era (1970-01-01 00:00:00 UTC) to a string representing the timestamp of this moment in the current time zone of the system.

Using the above function, you can achieve the desired result.

The input and output of the sample can be seen from below:

Last request

 select from_unixtime(unix_timestamp('2016/06/01','yyyy/MM/dd'),'yyyy-MM-dd') from table1; 

where table1 is the name of the table contained in my hive database.

I hope this helps you.

Answer 2

Using:

 unix_timestamp(DATE_COLUMN, string pattern) 

The above command will help convert the date to the timestamp unix format, which you can format as you want using the Simple Date Function .

Date function

Answer 3

Try this: cast (to_date (from_unixtime (unix_timestamp (yourdate, 'MM-dd-yyyy'))) as the date)

Answer 4

Suppose you have a column 'birth_day' in your table that is in your format, you should use the following query to convert Birth_day to the required format.

 date_Format(birth_day, 'yyyy-MM-dd') 

You can use it in the request as follows

 select * from yourtable where date_Format(birth_day, 'yyyy-MM-dd') = '2019-04-16';