How to avoid error on MYSQL CONVERT() method, when passing a 0 second string and subtract from that value?

Question

I am using MySQL 8 and have a problem with this type of query: INSERT INTO review (name, create_date) VALUES('name', CONVERT(timestamp, DATETIME) - 1) I have not had this error when using this expression in a where clause. When the value for the timestamp is like '2020-12-16 06:15:01' it's working.

But with a value of 0 seconds (like: '2020-12-16 06:15:00') an error is dropped.

Incorrect datetime value: '20201216061499' for column 'create_date' at row 1
code: ER_TRUNCATED_WRONG_VALUE
errno: 1292
sqlState: 22007

I used this type of expression in my whole project. Is there a simple solution to this problem, without changing each expression? Is that one a bug?

One solution to this problem is:

DATE_SUB(CONVERT(timestamp,DATETIME) INTERVAL 1 SECOND). 

But as I already mention this requires changing each expression.

Answer 1

You do need to update each expression. When you subtract a number from your timestamp, it first converts your timestamp into a number (e.g. 20201216061500), then you are subtracting one and, because the column you are inserting is a datetime, it tries to interpret the resulting number as a date/time, failing when the subtraction produced 20201216061499. The correct way to subtract one second is to say - INTERVAL 1 SECOND or use DATE_SUB(..., INTERVAL 1 SECOND).