How to get last six months data from database without giving any current date?

balaramr

Hello,
I am trying to automate output from the vertica database, I would like to get last 6 months data without giving any current date.

balaramr

something like this
select * from DATABASE.database where month(load_time)=month(now())-1;

Vertica_Curtis

Use interval.
select current_date() - interval '6 months';
The above example would also need to say >=, not "=", but that seems like a simple oversight. But it's also going to return 6 months + how many days there are in the current month. Which might be more than you want. Subtracting interval '6 months' will get you exactly 6 months into the past (or future). So, 6 months ago from today is 8/21/2020. If you want to go back to 8/1/2020, then use a more discrete operator like month().

marcothesane

Do you have an insert or last-change timestamp in the table?
Otherwise, it won' t be possible. You can't avoid using CURRENT_DATE in an expression if you want to do that.