0

I have a simple table with a json field and a generated field from a value in the json:

create table foo (
    id integer primary key auto_increment
  , somedata json
  , a_date datetime generated always as convert(somedata->>theDateAsAString, datetime))
);

When I try to insert some data I get the error:

insert into foo(somedata) values ('{"theDateAsAString":"2017-08-23T23:00:00.000Z"}');

ERROR 1292 (22007): Truncated incorrect datetime value: '2017-08-23T23:00:00.000Z'

However, if I drop the generated column, add the above data, then alter the table to add the column, I receive no such error and the field is displayed as it should. Why?

I have tried removing NO_ZERO_DATE and NO_ZERO_IN_DATE from the SQL mode by setting it in my.cnf, restarting and verified with SHOW VARIABLES LIKE 'sql_mode'.

Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
user3791372
  • 4,445
  • 6
  • 44
  • 78
  • 1
    Because MySQL function CONVERT can't convert date with the ISO 8601 format to datetime format without losing correctness that's why you get the error message.. so you to format the ISO 8601 yourself with the functions DATE_FORMAT or STR_TO_DATE – Raymond Nijland Sep 26 '17 at 14:43
  • @RaymondNijland that may be true, but that wouldn't explain why it's possible to drop the generated column, insert the data, then add the generated column for it to work. – user3791372 Sep 26 '17 at 14:45
  • true i can't explain that MySQL sometimes has nice random features... most likly you get al kinds of truncate warnings when selecting from that table.. try SHOW WARNINGS: after your SELECT to see them if there are warnings. – Raymond Nijland Sep 26 '17 at 14:49
  • there are warnings - which are the same, but only 2 ( from inserting twice i imagine). selecting doesn't create more warnings. it feels like there's more to it – user3791372 Sep 26 '17 at 14:54

1 Answers1

0

After a lot of experimentation I tried using str_to_date in the definition of my generated column, and that worked without error!

user3791372
  • 4,445
  • 6
  • 44
  • 78