Automatically update a MySQL table with the last 24 months

Question

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,

Please can you edit your question and add some simple data as text and the expected output ? — SelVazi, Jul 10 '23 at 08:28
This is nonsense. Fill the table with data until, say, 2050, then select only the 24 months you want using a simple where clause. — Salman A, Jul 10 '23 at 08:28
@SalmanA: Yep I know, good idea but i only wanted to have in that table only the last 24 months and nothing more. — KazimiR, Jul 10 '23 at 08:34

score 0 · Answer 1 · answered Jul 10 '23 at 08:35

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

score 0 · Answer 2 · answered Jul 10 '23 at 08:40

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.

Automatically update a MySQL table with the last 24 months

2 Answers2