Convert a timezone value to datetime and insert-CodePudding

I'm having trouble inserting a field into the database, type conversion keeps timezone

UPDATE order o
SET o.data_envio = CONVERT( '2022-01-05T14:47:00-03:00', DATETIME );

1292 - Truncated incorrect datetime value: '2022-01-05T14:47:00-03:00'

CodePudding user response：

This requires mysql 8; it will not work on 5.7 or earlier, or any version of mariadb (through at least 10.7.1).

Instead, you can do:

SET o.data_envio = convert_tz('2022-01-05T14:47:00','-03:00',' 00:00')

CodePudding user response：

You want to convert the timestamp with the STR_TO_DATE() function:

Like this:

   UPDATE order o
   SET o.data_envio = STR_TO_DATE( '2022-01-05T14:47:00-03:00', '%Y-%m-%dT%H:%i:%s')

Only drawback here is that you will lose the timezone information.