CODEWITHSUNDEEP
hive
Ramya
Ramya

Reputation: 45

Highest date from hive table with string data type

I am a newbie to hive and need your help. My requirement is to get the highest date from the table and my date datatype is string. I tried with max(), but it's not working for string data type... please help me on this.

Upvotes: 0

Views: 16562

Answers (3)

Kiran Vajja
Kiran Vajja

Reputation: 23

Since 0.12.0 version max(date) will just work.

If all the values in that column match the pattern 'yyyy-mm-dd' the above syntax should do the job

Upvotes: 0

mattinbits
mattinbits

Reputation: 10428

You're asserting the MAX doesn't work on Strings in Hive, but in fact it does:

Select MAX(dt) FROM (Select explode(Array("20150103", "20150102")) as dt) a;

As long as your date string is in a format which can be sorted lexographically, MAX should work fine.

Upvotes: 1

luoluo
luoluo

Reputation: 5533

Use built-in date functions unix_timestamp(string date, string pattern).

The unix_timestamp covert a string date to unix_timestamp as int, which is comparable.

Assume your table name is t and the time column is tt.

select max(unix_timestamp(tt, 'yyyyMMdd')) from t would find the max unix_timestamp for you, which is the latest date

Upvotes: 4

Related Questions