MySQL: Update timestamp column – add/subtract datetime from current value of timestamp column
[sql]UPDATE table_name SET <timestamp col>=<timestamp> + INTERVAL <expr> <unit> WHERE…;[/sql]
We can use the INTERVAL keyword to achieve the same in MySQL.
For example:
[sql]UPDATE table_name SET modified_at=modified_at + INTERVAL -1 DAY WHERE DATE(modified_at)=DATE(NOW());[/sql]
MySQL defines standard formats for expr and unit as illustrated in the following table:
| unit | expr |
|---|---|
| DAY | DAYS |
| DAY_HOUR | ‘DAYS HOURS’ |
| DAY_MICROSECOND | ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’ |
| DAY_MINUTE | ‘DAYS HOURS:MINUTES’ |
| DAY_SECOND | ‘DAYS HOURS:MINUTES:SECONDS’ |
| HOUR | HOURS |
| HOUR_MICROSECOND | ‘HOURS:MINUTES:SECONDS.MICROSECONDS’ |
| HOUR_MINUTE | ‘HOURS:MINUTES’ |
| HOUR_SECOND | ‘HOURS:MINUTES:SECONDS’ |
| MICROSECOND | MICROSECONDS |
| MINUTE | MINUTES |
| MINUTE_MICROSECOND | ‘MINUTES:SECONDS.MICROSECONDS’ |
| MINUTE_SECOND | ‘MINUTES:SECONDS’ |
| MONTH | MONTHS |
| QUARTER | QUARTERS |
| SECOND | SECONDS |
| SECOND_MICROSECOND | ‘SECONDS.MICROSECONDS’ |
| WEEK | WEEKS |
| YEAR | YEARS |
| YEAR_MONTH | ‘YEARS-MONTHS’ |
