cancel
Showing results for 
Search instead for 
Did you mean: 

Error in date filter in sales order admin report

Error in date filter in sales order admin report

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