Getting different time for orders while fetching orders from custom code

Question

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.

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

And this is the data that I received.

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.

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.

Answer 1

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.

Answer 2

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)";