Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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.

Answers

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

  • 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().

  • 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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.