0

MYSQL can't update date but in happens very seldom

I have it error very seldom(few time by month),for some dates like this. It isn't problem of code. I can't update DB field with the same date by IDE too.

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2022-03-13 02:59:14' for column 'date' at row 1 (SQL: update logs set logs.date = 2022-03-13 02:59:14 where ID = 2)

Other date not updates: 2022-03-13 02:58:16, 2022-03-13 02:57:15, 2022-03-13 02:55:15, 2022-03-13 02:54:16, 2022-03-13 02:53:14, 2022-03-13 02:52:15, 2022-03-13 02:51:15 .

Updates successfully: 2022-04-11 09:17:07

My query:

update logs set logs.date = '2022-03-13 02:59:14' where ID = 2;

Guys do you have any ideas, why in happens?

Table info:

enter image description here

DB info:

Dialect: MySQL DBMS: MySQL (ver. 5.7.29-0ubuntu0.16.04.1)

Case sensitivity: plain=exact, delimited=exact

Driver: MariaDB Connector/J (ver. 2.6.0, JDBC4.2)

charset = utf8;

Ivan Pirus
  • 1,026
  • 11
  • 21

1 Answers1

1

There's nothing wrong with these values. See below
Please give us the table definition and actuel sql update query that you are running.

CREATE TABLE d (d datetime);
INSERT INTO d VALUES ('2022-02-02');

✓

✓

 update d set d = '2022-03-13 02:58:16';
 select d from d;
 update d set d = '2022-03-13 02:57:15';
 select d from d;
 update d set d = '2022-03-13 02:55:15';
 select d from d;
 update d set d = '2022-03-13 02:54:16';
 select d from d;
 update d set d = '2022-03-13 02:53:14';
 select d from d;
 update d set d = '2022-03-13 02:52:15';
 select d from d;
 update d set d = '2022-03-13 02:51:15';
 select d from d;

✓

| d                   |
| :------------------ |
| 2022-03-13 02:58:16 |

✓

| d                   |
| :------------------ |
| 2022-03-13 02:57:15 |

✓

| d                   |
| :------------------ |
| 2022-03-13 02:55:15 |

✓

| d                   |
| :------------------ |
| 2022-03-13 02:54:16 |

✓

| d                   |
| :------------------ |
| 2022-03-13 02:53:14 |

✓

| d                   |
| :------------------ |
| 2022-03-13 02:52:15 |

✓

| d                   |
| :------------------ |
| 2022-03-13 02:51:15 |

db<>fiddle here

  • @Ivan Pirus What did you do differently that this answer corrected? – P.Salmon Apr 11 '22 at 10:48
  • @Kendle I add Table info and table info – Ivan Pirus Apr 12 '22 at 14:29
  • @P.Salmon I did test. I understood that a problem in a DB – Ivan Pirus Apr 12 '22 at 14:30
  • There is a problem with the DB build version or something else. Fine: MariaDB (ver. 10.4.19-MariaDB-1:10.4.19+maria~focal), MariaDB (ver. 10.4.17-MariaDB-1:10.4.17+maria~focal). Not good, problems with date: MySQL (ver. 5.7.29-0ubuntu0.16.04.1) – Ivan Pirus Apr 12 '22 at 17:25
  • @Kendle pls edit post add my answer below, what I wrote in a last my comment. And I will vote again. – Ivan Pirus Apr 12 '22 at 17:29
  • 1
    Ivan: I've checked and it seems to be working in all versions of mariaDB on DBfiddle see. https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=de8c156d1a63acb21b6ee35100b670b9 –  Apr 12 '22 at 17:36
  • Obviously if it's a date column it won't record the time –  Apr 12 '22 at 17:39
  • @Kendle Ok. I mean it is a problem of current installed DB or something else on server. I tested it on the beta server no problems too. Not big deal, thank for help – Ivan Pirus Apr 13 '22 at 14:28
  • TIMESTAMP supports date time – Ivan Pirus Apr 13 '22 at 14:31
  • @Kendle I had field type timestamp. I changed to datetime. Problem solved. Pls add variant answer below. Like Try to change field type timestamp to datetime – Ivan Pirus Apr 13 '22 at 15:12
  • 1
    Ivan Glad you've solved the problem. My example works fine with datatype `timestamp` - see https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3563bcc2c2ca020a714c535df8938b5d - there must be something more? –  Apr 13 '22 at 15:14
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/243857/discussion-between-ivan-pirus-and-kendle). – Ivan Pirus Apr 13 '22 at 15:28