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.
To convert date string from one format to another you have to use two date function of 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.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.
I hope this help you!!!