How to add offset to all timestamps / DATETIME in MySQL database?

Question

I have several MySQL databases that contain several tables containing (among others) some DATETIME columns. I am looking for a way to add some amount of time (say, one year) to all DATETIME columns in the entire database.

This can be useful if the system time was incorrect when the data was originally written to the database.

OR as in my case

to create the latest DEMO data for the application from historical data.

• Is there a way to shift all the DATETIME fields in the database at once?
• If not, how can I move the DATATIME column of all the entries in the ONE table (i.e. add an offset)?

Thank you for your responses!

Answer 1

UPDATE table SET date_column = DATE_ADD(date_column, INTERVAL 1 YEAR);

That should do the trick.

Answer 2

Try using a keyword INTERVALlike

UPDATE table_name SET column_name = column_name + INTERVAL 1 unit

Or maybe it's

ADDTIME()adds expr2 to expr1 and returns the result. expr1 is an expression of time or date and time, and expr2 is an expression in time.

SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');

The values ​​of the modules are as follows

unit Value  Expected expr Format  
MICROSECOND MICROSECONDS  
SECOND  SECONDS  
MINUTE  MINUTES  
HOUR    HOURS  
DAY         DAYS  
WEEK    WEEKS  
MONTH   MONTHS  
QUARTER QUARTERS  
YEAR    YEARS