Convert CURRENT_DATE or UNIX_TIMESTAMP to string in Hive (YYYY-DD-MM format)

Question

I have a weird dataset that I need to access in hive. Using traditional date/time functions (such as dateadd, etc) have proven difficult/ineffective.

There is a column in my dataset that is a string with the date in YYYY-MM-DD format.

I am wondering if it's possible to get the current date in YYYY-MM-DD format, and somehow cast it as a string?

I've done considerable amounts of research and have tried just about everything in the documentation (https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions)

Any help here will be greatly appreciated, as I've been grinding away at this problem for a considerable amount of time :)

Thanks!

But current_date returns date in yyyy-MM-dd fromat and it is compatible with string. You can compare string date with current date like this ` select '2018-01-01'<current_date` — leftjoin

Vijiy Vijiy · Accepted Answer · 2018-09-28T12:04:21

I didn't get how the date will be displayed for format YYYY-MM-DD, but you can try below.

hive> select cast(from_unixtime(unix_timestamp(cast(current_date() as string), 'yyyy-MM-dd'),'YYYY-MM-DD') as string);
OK
2018-09-271
Time taken: 0.114 seconds, Fetched: 1 row(s)
hive>

from_unixtime accepts string format and you can get all the options here - https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

hope this helps.

VIJAY

Convert CURRENT_DATE or UNIX_TIMESTAMP to string in Hive (YYYY-DD-MM format)

1 Answers