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('*');

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

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

foreach($collection as $_order) {
    $ordercomment = "Cancelling order since no payment";
    $order        = $this->orderFactory->create()->loadByIncrementId($_order->getIncrementId());
    $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 ?


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.

1 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()

        /*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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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