Not sure if this is an issue with the data migration or something did not translate correctly during the migration.
When viewing the Orders in the Admin Grid the Order Timestamp in Magento 2.3, the timestamp appear to be off about +5:00 hours. I double checked the times in the Magento 1.9 database and times that were pulled over showed that they were created with a +5:00 time difference, in the MySQL database. Although, in the M1 Admin Grid, it projects the correct times but, the database created a different time. As I can see, it’s doing a time offset via a PHP script. I assume the way M1 handles the timezones are different than how M2 does it because when I created a test order in M2, it shows the correct time when the order was placed, in both the Admin Grid and the MySQL Database. I’ve made sure that my M2 install has the correct Timezone selected under [Stores > Configuration > Locale Options > Timezone]. So, I believe that the times need to be converted to the M2 time difference and wasn’t sure if this is possible and how it can be done.
Is it possible to update the time values from the imported M1 data to the proper times for M2, like a MySQL command to offset the timestamps for the order times?
11 answers
Hi there,
So, I believe that the times need to be converted to the M2 time difference and wasn’t sure if this is possible and how it can be done.
Is it possible to update the time values from the imported M1 data to the proper times for M2, like a MySQL command to offset the timestamps for the order times?
Yes, Magento2 was using the UTC timezone as default to saving the date time values of the sales data.
Thus, we need to convert date time values of the sales data from your M1’s timezone to UTL timezone before save it to M2 database.
What is the timezone in your M1 site?
Regards,
Mall.
We are using Eastern Standard Time (America/New_York) for both M1 and M2. That’s why I’m confused on what the differences might be between the two systems.
Hi there,
We are using Eastern Standard Time (America/New_York) for both M1 and M2.
I checked your Time zone (America/New_York) and saw which differed from 4 hours with UTC time (not +5:00 time difference as you mentioned).
In this case, we can help to add more tweak code in step #7 of our migration tool to convert the Sales order created time from your time zone (America/New_York) to UTC time when migrating data.
Please let me know which version of our migration tool in your instance? I will provide you tweak code accordingly. And then, you need to run delta in step #7 of our migration tool.
Regards,
Mall.
Appreciate you looking into this. Here is the version we are currently using: UB Data Migration Pro (CE) -- 3.1.9
Hi there,
We are using Eastern Standard Time (America/New_York) for both M1 and M2.
You can follow steps below to solve the issue on migrated sales orders time you mentioned:
Step 1: Upgrade our migration tool to the latest Pro version 3.2.0 first.
Step 2: After upgrading to Pro ver.3.2.0 successfully, apply additional tweak code for the Pro ver.3.2.0 as follows:
+ Download the PHP file here,
then upload to replace to the file at: pub/ub-tool/protected/models/UBMigrate.php
+ Download the PHP file here,
then upload to replace to the file at: pub/ub-tool/protected/controllers/BaseController.php
+ Download the PHP file here,
then upload to replace to the file at: pub/ub-tool/protected/controllers/Step7Controller.php
+ Download the PHP file here,
then upload to replace to the file at: pub/ub-tool/protected/commands/RunCommand.php
Step 3: Re-update the logging data of our migration tool by running the below SQL commands:
+ Run this SQL in your M1 database to get the minimum value of the sales order’s created time which need to be migrated:
SELECT MIN(created_at) FROM `sales_flat_order`;
After running that SQL, you will see the needed string date time value, then use that value to replace the data time string in bold in the following SQL:
UPDATE `ub_migrate_map_step_7` SET `created_time` = '2000-01-01 00:00:00', `offset` = 0 WHERE `entity_name` = 'sales_flat_order';
and then, run that SQL in your M2 database.
Step 4: Once done, open your terminal and go to your M2 folder and run this CLI command to run delta update for the sales order data in step #7:
php -f bin/ubdatamigration run --step=7 --mode=update --timezone='America/New_York';
Finally, clean M2 cache and check the issue on migrated sales order’s time you mentioned again.
PS. We have tested this workaround in our local instance and it was working fine at our end.
Regards,
Mall.
Thanks a bunch for putting these detailed instructions together. Very helpful. Really Appreciate it!!!
I did however, ran into one issue near the end of the Step #4. Everything went smoothly and when I ran the command:
php -f bin/ubdatamigration run --step=7 --mode=update --timezone='America/New_York';
There was a Satus:fail error:
Message: CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘37047’ for key ‘PRIMARY’. The SQL statement executed was: INSERT INTO `sales_order_tax` (`tax_id`, `order_id`, `amount`, `priority`, `position`, `base_amount`, `process`, `base_real_amount`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7)
I assume it has something to do with the data in my M1 tables but, I’m not able to find the duplicate entry ‘37047’ in the table ‘sales_order_tax’. Not sure if I’m looking in the right location or it’s a referenced table cell in some other table? Kinda stumped on this one. I ended up resetting Steps 7 & 8 in the migration extension and re-ran that code but, it still seems to be an issue with one of the orders.
Hi there,
1062 Duplicate entry ‘37047’ for key ‘PRIMARY’. The SQL statement executed was: INSERT INTO
sales_order_tax
That issue occurred because your M2 site has sales order tax data added in M2 while you checked the ‘KEEP ORIGINAL IDs’ settings in step #7 of our migration tool (The Keep Original IDs requires your M2 to be a fresh instance).
To solve that issue, you need to delete the sale tax data records which was added in M2 as follows:
+ Run the below SQL in your M2 database:
Delete From sales_order_tax Where tax_id = 37047;
+ Then, you can continue with data migration in step #7 as normal.
Regards,
Mall.
Thanks, I went ahead and deleted that entry. I did run into a few others and had to delete them and re-run the script a few times after it ran into another duplicate key. Thanks Again for that! 🙂
Kinda an odd thing though, when you view the Orders in the Admin Grid, the time is still incorrect but, when you go into the the Edit Order page in the M2 Admin, the Order’s time is correctly converted. I’m starting to think there is a setting in M2 that is affecting the time offset on the Admin Grid pages. *scratching head*
Hi there,
when you view the Orders in the Admin Grid, the time is still incorrect
Yes, that is right. In order to convert the sales order’s time for that data section, please follow additional steps below:
+ Download the PHP file here,
then upload to replace to the file at: pub/ub-tool/protected/controllers/Step7Controller.php
(We have added more tweak code to handle that case)
+ Run the below SQL in your M2 database:
UPDATE `ub_migrate_map_step_7` SET `created_time` = '2000-01-01 00:00:00', `offset` = 0 WHERE `entity_name` = 'sales_flat_order';
+ Once done, open your terminal and go to your M2 folder and run the delta update for the sales order data in step #7 again by running this CLI command:
php -f bin/ubdatamigration run --step=7 --mode=update --timezone='America/New_York';
Regards,
Mall.
Hi
I have the same issue and I have the latest version of the software. I exactly follow the same step but I get an error
CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘48251’ for key ‘PRIMARY’. The SQL statement executed was: INSERT INTO `sales_payment_transaction` (`order_id`, `payment_id`, `is_closed`, `transaction_id`, `txn_id`, `txn_type`, `created_at`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6)
I have 267976 number of order and running the update for last 8 hrs and suddenly this error popups us and now I check the order which are there in magento 2 and magento 1 but the time is not fixed.
What should I need to do to fix this?
Hi Argha Bhattacharya,
I’ve just replied to you for the same question via this post. Please check and follow up with me there.
Regards,
Mall.
