0

I am working on creating a custom module to show orders report in admin. Right now I have fetched data from 'sales_order' table using sql query. I know I should not use sql queries to fetch data from database rather I should use magento collection object to fetch the data. But right now I am just checking and will update my code in future and will use magento way to fetch data.

So while fetching the data using sql query the time that it shows for created_at time is different than the time that is saved in database.

This is the screenshot of the time saved in database. enter image description here

While fetching in the frontend the time is different. This is the sql query that I am using enter image description here

And this is the data that I received. enter image description here

I have marked the increment_id and we can see that the id is same, but the time that I received in the array is different than the database.

And also the time in the sales order grid in admin is matching with the database time. enter image description here

So my question is why the time that I have fetched using sql query is different than the one in the database and how I can correct it. Plz could someone guide me in this.

2 Answers 2

0

The admin reports show a localized time based on the timezone configured for the store. To create a SQL query that matches, you can use the DATE_SUB and DATE_ADD functions as needed to match the store's timezone offset.

0

The issue was because of timezone. There is a CONVERT_TZ() function in mysql that helps you achieve this. More info on this function

https://www.w3resource.com/mysql/date-and-time-functions/mysql-convert_tz-function.php

My SQl query after using this function and also it gives the desired result.

$sql = "Select entity_id,increment_id,CONVERT_TZ(created_at, '+00:00','-4:00') as created_at,status,shipping_incl_tax,total_qty_ordered,base_tax_amount,grand_total,store_name,coupon_code,weight,total_due,customer_email,customer_group_id,base_subtotal_incl_tax FROM " . $tableName . " where increment_id NOT IN($oId)";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.