CODEWITHSUNDEEP
datehadoophivesql-timestamp
keeplearning
keeplearning

Reputation: 379

Hive - month and year from timestamp column

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.

Upvotes: 15

Views: 102269

Answers (4)

Gaurav
Gaurav

Reputation: 1109

Please use the following query

SELECT YEAR(FROM_UNIXTIME(unix_timestamp()))*100 + MONTH(FROM_UNIXTIME(unix_timestamp()))

Upvotes: 5

Ramesh babu M
Ramesh babu M

Reputation: 51

To see date in yyyy-mm-dd hh:mm:ss format, you can use it as below:

select to_utc_timestamp(col_name, 'PST') * from table;

Upvotes: 1

mrsrinivas
mrsrinivas

Reputation: 35434

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.

enter image description here

Upvotes: 34

Pathik Vejani
Pathik Vejani

Reputation: 4501

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_MONTH
FROM abc.test;

Upvotes: 3

Related Questions