Skip to content
Advertisement

Selecting YYYYMM of the previous month in HIVE

I am using Hive, so the SQL syntax might be slightly different. How do I get the data from the previous month? For example, if today is 2015-04-30, I need the data from March in this format 201503? Thanks!

select 
    employee_id, hours,
    previous_month_date--YYYYMM,
from 
    employees 
where 
    previous_month_date = cast(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') as int)

Advertisement

Answer

You could do (year('2015-04-30')*100+month('2015-04-30'))-1 for the above mentioned date, it will return 201503 or something like (year(from_unixtime(unix_timestamp()))*100+month(from_unixtime(unix_timestamp())))-1 for today’s previous month. Assuming your date column is in ‘yyyy-mm-dd’ format you can use the first example and substitute the date string with your table column name; for any other format the second example will do, add the column name in the unix_timestamp() operator.

4 People found this is helpful
Advertisement