Hive - month and year from timestamp column
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.
You can use CONCAT and FROM_UNIXTIME like below:
SELECT CONCAT(YEAR(FROM_UNIXTIME(1468215093)), MONTH(FROM_UNIXTIME(1468215093))) AS YEAR_MONTH
In your query:
SELECT CONCAT(YEAR(FROM_UNIXTIME(upd_gmt_ts)), MONTH(FROM_UNIXTIME(upd_gmt_ts))) AS YEAR_MONTHFROM abc.test;
Please use the following query
SELECT YEAR(FROM_UNIXTIME(unix_timestamp()))*100 + MONTH(FROM_UNIXTIME(unix_timestamp()))