CODEWITHSUNDEEP
mysqldatetimestamp
AI.
AI.

Reputation: 996

Add date in timestamp MYSQL

Add date in timestamp

I want to run below query

UPDATE table
SET columnA = '2017-03-21 23:57:19'
WHERE .....

However, I want to construct the timestamp by taking whatever yesterday's date is, at 23:57:19 hours.

The datatype for columnA is timestamp. 2017-03-21 is yesterday's date. Am I supposed to use subdate(CURDATE(),1)?

UPDATE table
SET columnA = 'subdate(CURDATE(),1) 23:57:19' WHERE .....  ?

Or I have another way to do this ?

Upvotes: 0

Views: 525

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Use DATE_SUB()

UPDATE table
SET columnA = DATE_SUB('2017-03-22 23:57:19', INTERVAL 1 DAY)

or

UPDATE table
SET columnA = DATE_SUB(NOW(), INTERVAL 1 DAY)

If you wanted to get yesterday at 23:57:19 then try this:

DATE_ADD(TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)), INTERVAL '23:57:19' HOUR_SECOND)

DATE_SUB(CURDATE(), INTERVAL 1 DAY) - yesterday's date
TIMESTAMP(...)                      - resets yesterday to midnight
DATE_ADD(..., INTERVAL '23:57:19')  - adds 23:57:19 to yesterday from midnight

Demo

Upvotes: 2

Related Questions