MySQL: How to update timestamp column - add/subtract datetime from current value of timestamp column

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’