hiveQL String to Date Conversion

Question

I'm a little new to hiveQL and could use some tips.

I'm working on creating a view in Beeswax and I wanted to convert a string to a date data type.

My goal is to knock the time info off, and just return the date.

Example:

Convert ‘2013-11-01 12:31:15’

to ‘2013-11-01’

Does anyone know of a way or function that does this?

Answer 1

You can use the in built function to_date(2013-11-01 12:31:15). This will convert it into 2013-11-01.

Check the description under Date Functions

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

Answer 2

You can change the string to date by using following code:

hive> select to_date(from_unixtime(unix_timestamp('2013-11-01 12:31:15', 'yyyy-dd-MM hh:mm:ss')));

unix_timestamp('string', 'format') - convert string to unixtimestamp from_unixtime(unixtimeformat) - convert unix time stamp to timestamp format to_date(timestamp) - fetch date part from the timestamp format