Wrong datetime format

[alfredosilvestre-natixis]

On some datetime values that are exported we have the following error:

ERROR 1292 (22007) at line 677: Incorrect datetime value: '2022-01-31 16:18:56+01'

Right now we are using sed to replace:

sed -i -e 's/\+[[:digit:]]\{2\}//gi' "${MYSQL_DUMP}.sql"

[timsehn]

Thanks for the report. I saw this and yesterday's reports. We are not actively working on this right now but I'll see if @zachmu or I can carve out some time to fix these.

[timsehn]

So this should be covered by this line:

pg2mysql/pg2mysql.pl

Line 379 in 1c2683c

$line =~ s/'(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}.\d{6})(-|\+)\d{2}'/'$1'/g; # timestamp literal strings need timezones stripped

Possibly they are getting through in a non-insert?

[timsehn]

I think #48 should fix this but I need to write some tests

[timsehn]

This is fixed by #48

[johanek]

Hi, I'm still seeing this issue in some cases, i.e.

 cat test.pgsql
INSERT INTO public.dcim_device VALUES ('a8d7ba20-73dc-4816-b84a-ed292392c323', '2023-07-24', '2023-07-24 09:36:17.91806+01', '{"xml_file": "file", "radius_key": "secret", "management_ipv4": "127.0.0.1"}', NULL, NULL, 'hostname', 'fsp150-xg418', '', NULL, NULL, '', NULL, NULL, 'FSP150 XG418 in lab', NULL, 'f4b35e1e-3fd1-4641-aecf-f06b310dab5a', 'c202ef2f-88c6-42af-a45c-edd1ebcbc9cc', NULL, 'ba19e097-c38e-4a8e-adf6-ad2eff4c371a', NULL, NULL, NULL, '8156e641-8f3a-483d-90ba-e4c89435fbd3', '17cdf3c2-28c3-4f94-a691-77211d5eeca4', NULL, NULL, NULL, NULL, NULL, NULL, NULL);

$ perl pg2mysql.pl < test.pgsql
--
-- Generated by pg2mysql
--
set foreign_key_checks = off;
--
line 1 ended, num quotes is 28 and in_insert is 0
marking statement ended at pg2mysql.pl line 404, <> line 1.
INSERT INTO public.dcim_device VALUES ('a8d7ba20-73dc-4816-b84a-ed292392c323', '2023-07-24', '2023-07-24 09:36:17.91806+01', '{"xml_file": "file", "radius_key": "secret", "management_ipv4": "127.0.0.1"}', NULL, NULL, 'hostname', 'fsp150-xg418', '', NULL, NULL, '', NULL, NULL, 'FSP150 XG418 in lab', NULL, 'f4b35e1e-3fd1-4641-aecf-f06b310dab5a', 'c202ef2f-88c6-42af-a45c-edd1ebcbc9cc', NULL, 'ba19e097-c38e-4a8e-adf6-ad2eff4c371a', NULL, NULL, NULL, '8156e641-8f3a-483d-90ba-e4c89435fbd3', '17cdf3c2-28c3-4f94-a691-77211d5eeca4', NULL, NULL, NULL, NULL, NULL, NULL, NULL);