-1

I have a field LOREM of type json in my mysql DB table BLAH.

LOREM has a property createdOn which is a date formatted like so: 2020-03-05T04:30:00.000+0000 (This is the value retrieved by running the command:

SELECT LOREM ->>'$.createdOn' FROM BLAH;)

I'm trying to create a virtual generated column for created on so that querying becomes a little easier. I've tried putting this in the ddl when creating the table:

CREATED_ON timestamp(3) GENERATED ALWAYS AS (CAST(LOREM ->> '$.createdOn' AS DATETIME)) VIRTUAL, but when inserting an entry I get an error:

1292 Truncated incorrect datetime value: '2020-03-05T04:30:00.000+0000'

I've tried changing the column timestamp precision from 3 to 6, but that didn't work. I've also tried

CREATED_ON timestamp(3) GENERATED ALWAYS AS (TIMESTAMP(CAST(LOREM ->> '$.createdOn' AS DATETIME))) VIRTUAL,, but got the same error.

Any idea why this may be happening and how I should solve it?

Dylan Colaco
  • 325
  • 2
  • 11

1 Answers1

0

11.2.2 The DATE, DATETIME, and TIMESTAMP Types

...

As of MySQL 8.0.19, you can specify a time zone offset when inserting TIMESTAMP and DATETIME values into a table. The offset is appended to the date part of a datetime literal, with no intravening spaces, and uses the same format used for setting the time_zone system variable, with the following exceptions:

  • For hour values less than than 10, a leading zero is required.
  • The value '-00:00' is rejected.
  • Time zone names such as 'EET' and 'Asia/Shanghai' cannot be used; 'SYSTEM' also cannot be used in this context.

...

2020-03-05T04:30:00.000+0000 != 2020-03-05T04:30:00.000+00:00

See dbfiddle.

wchiquito
  • 16,177
  • 2
  • 34
  • 45