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.

check this `select date_format(upd_gmt_ts,'yyyyMM') from abc.test;` — mrsrinivas, Jan 10 '17 at 06:43

score 34 · Accepted Answer · edited Jun 20 '20 at 09:12

34

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.

edited Jun 20 '20 at 09:12

Community

answered Jan 10 '17 at 06:47

mrsrinivas

score 4 · Answer 2 · edited Oct 29 '17 at 14:59

4

Please use the following query

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

edited Oct 29 '17 at 14:59

Ignacio Alorre

answered Jun 09 '17 at 11:30

Gaurav

score 2 · Answer 3 · answered Jan 10 '17 at 06:30

2

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;

answered Jan 10 '17 at 06:30

Pathik Vejani

Thanks for the query..but this gave me semanticException. did not work as expected.. – keeplearning Jan 10 '17 at 10:45
FAILED: SemanticException [Error 10014]: Line 1:19 Wrong arguments 'upd_gmt_ts': No matching method for class org.apache.hadoop.hive.ql.udf.UDFFromUnixTime with (timestamp). Possible choices: _FUNC_(bigint) _FUNC_(bigint, string) _FUNC_(int) _FUNC_(int, string) – keeplearning Jan 10 '17 at 11:37
is `upd_gmt_ts` your timestamp value? – Pathik Vejani Jan 10 '17 at 11:49

score 1 · Answer 4 · edited Jul 10 '17 at 10:24

1

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;

edited Jul 10 '17 at 10:24

Tiger-222

answered Jul 10 '17 at 09:54

Ramesh babu M

4 Answers4