1

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.

Ankit Sharma
  • 3,923
  • 2
  • 29
  • 49
Paul Werner
  • 87
  • 1
  • 8
  • What datatype has the value? Why its numeric representation differs from its string representation? You do not need in CONVERT at all if provided value `'2020-12-16 06:15:00'` is of string type - it will be converted to DATETIME implicitly and correctly. – Akina Dec 16 '20 at 05:41
  • PS. Never use double quotes in MySQL queries until quoting JSON paths/values. – Akina Dec 16 '20 at 05:42
  • its a string-value. i had to use convert() to perform the - 1 second operation. whithout this a string cannot be processed with minus 1 second – Paul Werner Dec 16 '20 at 05:44
  • I am not had this error when using this expression in a where clause. – Paul Werner Dec 16 '20 at 05:46
  • 1
    *i had to use convert() to perform the - 1 second operation. whithout this a string cannot be processed with minus 1 second* Not needed. `'2020-12-16 06:15:00' - INTERVAL 1 SECOND` will work correctly. – Akina Dec 16 '20 at 05:50
  • a way cleaner solution. thank you. but am i right, there is aneed to update each expressions? – Paul Werner Dec 16 '20 at 05:56
  • Of course, you must update all queries texts which uses incorrect expressions. – Akina Dec 16 '20 at 05:57
  • 1
    *i had to use convert() to perform the - 1 second operation.* Search Reference manual for an article about implicit type convertion during expression evaluation. You will find that in the substraction the DATETIME obtained is converted to a numeric datatype implicitly, so the result is not one you need. – Akina Dec 16 '20 at 05:59

1 Answers1

0

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).

ysth
  • 96,171
  • 6
  • 121
  • 214