Question 204425 in Mysql Question posted in
The official MySQL documentation can be found here.

Automatically update a MySQL table with the last 24 months

I have the following already created table in DBeaver:

enter image description here

Let’s say that from this dataset in the ss On the 1st of August i want to add 2023-08-01 and delete 2021-07-01 from the 1st row.

I am using this to update a dashboard timeline with the last 24 months, taking into consideration the current timestamp.
Until now I updated it manually, but now I want to make an automatic procedure in DBeaver to run daily to check if the current timestamp is the 1st of the month, and if yes to add that date into the table and delete the 1st row of the table.
How can I do that?

For the moment I just surfed the web in search for answers, I am still a beginner in this,

Tags:

2

Answers


  1. 0

    Your approach is wrong. Fill the date_month table with data for all expected past and future dates, then use a simple query to select past 24 months:

    where t.date_month between 
  last_day(current_date) + interval 1 day - interval 24 month and
  last_day(current_date) + interval 1 day - interval 1 month
    Login or Signup to reply.
  2. 0

    Assuming you start with the table above, you can remove the first month and add the last in one UPDATE query:

    update date_24_months set date_month = date_add(date_month, interval 1 month);

    This adds 1 month to every date, thus advancing the whole table by one month.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search