Home > Enterprise >  Update the records of a TIMESTAMP field by adding 5 years
Update the records of a TIMESTAMP field by adding 5 years

Time:07-25

I currently have a date field of type TIMESTAMP registered in the database. These values ​​are already filled in. I would like to add 5 years to the current value of each of the records.

For example, if the first record has a date of 05/22/2018, it must go to 05/22/2023.

Each record has a different date, so even years are not common.

Some help? Thanks in advance

CodePudding user response:

One option is using DATE_ADD:

UPDATE tbl 
SET `your_date_column` = DATE_ADD(`your_date_column` , INTERVAL 5 YEAR) ;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4348737aea1acb655cc77c8d9582c176

CodePudding user response:

You can use INTERVAL

CREATE TABLE DAT(dt datetime)
INSERT INTO DAT VALUES('2018-02-02'),('2016-02-02'),('2022-02-02')
UPDATE DAT SET dt = dt   INTERVAL 5 YEAR
SELECT * FROM DAT
| dt                  |
| :------------------ |
| 2023-02-02 00:00:00 |
| 2021-02-02 00:00:00 |
| 2027-02-02 00:00:00 |

db<>fiddle here

  • Related