CODEWITHSUNDEEP
sqlhivehiveql
sharp
sharp

Reputation: 2158

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)

Upvotes: 1

Views: 14366

Answers (5)

Amitava 'Ami' Pal
Amitava 'Ami' Pal

Reputation: 41

below works across year boundaries w/o complex calcs:

date_format(add_months(current_date, -1), 'yyyyMM') --previous month's yyyyMM

in general,

date_format(add_months(current_date, -n), 'yyyyMM') --previous n-th month's yyyyMM

use proper sign for needed direction (back/ahead)

Upvotes: 2

Dhiraj
Dhiraj

Reputation: 3696

provided you get rid of those hyphens in your input string , previous date's month id in YYYYMM format you can get by:-

select if(  ((${hiveconf:MonthId}-1)%100)=0  ,${hiveconf:MonthId}-89,${hiveconf:MonthId}-1  ) as PreviousMonthId;

Upvotes: 0

Samson Scharfrichter
Samson Scharfrichter

Reputation: 9067

From experience, it's safer to use DATE_ADD(Today, -1-Day(Today)) to compute last-day-of-previous-month without having to worry about edge cases. From there you can do what you want e.g.

select
  from_unixtime(unix_timestamp(), 'yyyy-MM-dd') as TODAY,
  date_add(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), -1-cast(from_unixtime(unix_timestamp(), 'd') as int)) as LAST_DAY_PREV_MONTH,
  substr(date_add(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), -1-cast(from_unixtime(unix_timestamp(), 'd') as int)), 1,7) as PREV_MONTH,
  cast(substr(regexp_replace(date_add(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), -1-cast(from_unixtime(unix_timestamp(), 'd') as int)), '-',''), 1,6) as int) as PREV_MONTH_NUM
from WHATEVER limit 1

-- today       last_day_prev_month  prev_month  prev_month_num
-- 2015-08-13  2015-07-30           2015-07             201507

See Hive documentation about date functions, string functions etc.

Upvotes: 2

careful_rower
careful_rower

Reputation: 11

Angelo's reply is a good start but it returns 201500 if the original date was 2015-01-XX. Building on his answer, I suggest using the following:

IF(month(${DATE}) = 1,
    (year(${DATE})-1)*100 + 12,
    year(${DATE})*100 + month(${DATE})-1
) as month_key

Upvotes: 1

Angelo Di Donato
Angelo Di Donato

Reputation: 1093

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.

Upvotes: 1

Related Questions