Order collection issue when filtering with created_at

Question

I have a cron job to cancel orders with status pending after few hours of order creation.

$paymentMethod  = 'my_method';

$collection = $this->_orderCollectionFactory->create()->addAttributeToSelect('*');

$collection->getSelect()->join(
    ["sop" => "sales_order_payment"],
    'main_table.entity_id = sop.parent_id',
    array('method')
    )
    ->where('sop.method = ?',$paymentMethod);

$collection->addFieldToFilter('created_at', ['lteq' => $end_date]);
$collection->addFieldToFilter('state', ['eq' => 'new']);
$this->_logger->info($collection->getSelect());

foreach($collection as $_order) {
    $ordercomment = "Cancelling order since no payment";
    $order        = $this->orderFactory->create()->loadByIncrementId($_order->getIncrementId());
    $order->addStatusHistoryComment($ordercomment)->save();
    $this->orderManagement->cancel($_order->getId());
    $this->_logger->info("Cancel Pending Order - ".$_order->getIncrementId());

} // end of foreach orders

The select query prints like below...

SELECT `main_table`.*, `sop`.`method` FROM `sales_order` AS `main_table` INNER JOIN `sales_order_payment` AS `sop` ON main_table.entity_id = sop.parent_id WHERE (sop.method = 'my_method') AND (`created_at` <= '2021-04-22 08:48:06') AND (`state` = 'new');

When i am trying this query in mysql terminal, results are fine with date and time range.

But getting invalid date range rows when looping through foreach. eg: a row with created_at as '2021-04-22 05:52:18' ;

what will be the reason ?

Update

Just noticed, cron logs showing UTC time. But actually server, mysql and magento set with a different timezone.

This time difference has an impact on the above result. Because when printing the created_at, it shows that difference.

It's not printing same as what in database.

Answer 1

I also created cron for daily order report.

        //Get Object Manager Instance
        $objectManager  = \Magento\Framework\App\ObjectManager::getInstance();
        $objDate = $objectManager->create('Magento\Framework\Stdlib\DateTime\DateTime');

        /*for avoid date filter issue i hardcoded seconds here*/
        $currentDateTime = $objDate->gmtDate('Y-m-d');
        $currentfullDateTime = $objDate->gmtDate('Y-m-d H:i').':00';
        $currentfullDateTime1 = $objDate->gmtDate('Y-m-d H:i').':59';
        
        $ordersCollectionFactory = $objectManager->create('Magento\Sales\Model\ResourceModel\Order\CollectionFactory');
        $collection = $ordersCollectionFactory->create()
         ->addAttributeToSelect('*');

        /*for avoid date filter issue i minus 1 minute here*/
        $startDate = date("Y-m-d H:i:s", strtotime($currentfullDateTime .' -1 day'));
        $endDate = date("Y-m-d H:i:s",strtotime($currentfullDateTime1.'-1 minute'));

hope this help you.

please accept as a answer if work for you