We have a store configured with timezone = America/Mexico_City (-05:00)
Orders are saved with the correct date in the sales_order table, in the created_at column, which is a timestamp type column, so they are saved in UTC and when displaying, they are shown in the timezone configured in the connection to mysql.
For example:
In UTC
2021-05-12 16:42:19.0
2021-05-12 01:59:42.0
With timezone '-05:00' (México)
2021-05-12 11:42:19.0
2021-05-11 20:59:42.0
The problem is that when I filter in the Sales Order report by date, Magento sends several queries to Mysql, and among them these:
2021-05-12T17:10:30.808452Z 2729 Query SET SQL_MODE=''
2021-05-12T17:10:30.808700Z 2730 Query SET time_zone = '+00:00'
2021-05-12T17:10:30.810789Z 2730 Query SET NAMES utf8
…
…
2021-05-12T17:10:32.166662Z 2730 Query SELECT COUNT(*) FROM `sales_order_grid` AS `main_table` LEFT JOIN `braintree_transaction_details` ON braintree_transaction_details.order_id = main_table.entity_id WHERE (`main_table`.`created_at` >= '2021-05-12 00:00:00') AND (`main_table`.`created_at` <= '2021-05-12 23:59:59')
2021-05-12T17:10:32.168084Z 2730 Query SELECT `main_table`.*, `braintree_transaction_details`.`transaction_source` FROM `sales_order_grid` AS `main_table` LEFT JOIN `braintree_transaction_details` ON braintree_transaction_details.order_id = main_table.entity_id WHERE (`main_table`.`created_at` >= '2021-05-12 00:00:00') AND (`main_table`.`created_at` <= '2021-05-12 23:59:59') ORDER BY main_table.created_at DESC
LIMIT 20
The problem is that when setting the timezone in UTC (+00:00), and filter by
created_at >= '2021-05-12 00:00:00' AND created_at <= '2021-05-12 23:59:59'
Bring the two registers (which is incorrect for those who use the admin with the store with timezone -05: 00)
2021-05-12 16:42:19.0
2021-05-12 01:59:42.0 -- This one brings it because in UTC it is from day 12, but at -05: 00 which is my timezone it is from day 11 and therefore it should not bring it
To see that it was not a theme with our store that has several customizations, I did the test on a Magento Commerce 2.4.1 totally out of the box
