SQL query conversion to Hive supported migration SQL-CodePudding

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

CodePudding user response：

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.