CODEWITHSUNDEEP
mysqljsonautogeneratecolumn
user3791372
user3791372

Reputation: 4685

trying to insert json with a value that's being used with a generated datetime field

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

Upvotes: 0

Views: 173

Answers (1)

user3791372
user3791372

Reputation: 4685

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

Upvotes: 0

Related Questions