Hive - month and year from timestamp column

Question

Hi I am trying to extract the month and year part of a timestamp column in hive using the below query

select from_unixtime(unix_timestamp(upd_gmt_ts,'yyyyMM')) from  abc.test;

The output looks like 2016-05-20 01:08:48

the desired output should be 201605

Appreciate any suggestions.

Answer 1

I'd prefer to use Hive date_format() (as of Hive 1.2.0). It support Java SimpleDateFormat patterns.

date_format() accepts date/timestamp/string. So your final query will be

select date_format(upd_gmt_ts,'yyyyMM') from abc.test;

Edit:

SimpleDateFormat acceptable patterns examples.