postgresqlhivehiveqlgreenplum

SQL query conversion to Hive supported migration SQL

I am migrating the greenplum SQL query to HiveSQL, kindly help me as below statement.

select (date_trunc('week',current_date) - INTERVAL '7 week')::DATE 
select (date_trunc('week',current_date)+ '6 days'::INTERVAL)::DATE
select date_trunc('week',current_date)::DATE
Solution

  • Here are equivalent queries -

    select (date_trunc('week',current_date) - INTERVAL '7 week')::DATE --> select date_sub(next_day(date_sub(current_date, 7), 'MON'), 49)
select (date_trunc('week',current_date)+ '6 days'::INTERVAL)::DATE --> select date_add(next_day(date_sub(current_date, 7), 'MON'), 6)
select date_trunc('week',current_date)::DATE --> select next_day(date_sub(current_date, 7), 'MON')

    All above solution are using next_day() function which picks up next MONDAY from current date -7 days. And then first scrnario adds 7week/49days, second one adds 6 days, and third one just get Monday of current date.