5

we are trying to filter out orders that were created longer than 60 minutes ago with status pending. We are having some problems with the date/time functions

We tried: - date('Y-m-d H:m:s') and subtracting hours - and Mage::getModel('core/date')

But both are not giving me the correct date/time as I see is being used for the column updated_at.

Question: What date/time function or method should I use to find a date-time that corresponds to the date-time that is used for the updated_at column in orders? (so when I have the correct date-time I can subtract and select orders)

$date = $this->get_offset_hours();
$orders = Mage::getModel('sales/order')->getCollection()
    ->addFieldToFilter('state', 'new')
    ->addFieldToFilter('status', 'pending')
    ->addFieldToFilter('updated_at', array('lt' => $date));
1

All magento transaction data it's saved in UTC so you need to start with the UTC now not your store or server now.

To get the NOW in UTC you can use:

// Get Store ID
$store = $this->getCurrentStoreId(); // You can change it

// Get Store Now (Zend_Date)
$nowStore = Mage::app()->getLocale()
    ->storeDate($store, null, true);

// Get Store Now (String)
$nowStoreDatetime = $nowStore->toString('y-MM-dd HH:mm:ss');

// Get Now UTC
$nowUtc = Mage::app()->getLocale()
    ->utcDate($store, $date, true);

Now you can play with Zend_Date:

/** @var Zend_Date $nowUtc */

$nowUtc->subHour(1);
$finalDatetime = $nowUtc->toString('y-MM-dd HH:mm:ss');

Using $finalDatetimein your query you should get the orders you want.

Note that updated_at date can change if you add a comment to the order or some cron/payment gateway do something, so if you want to track the exactly moment that the order was set to 'pending' you should add a join with status_history table. If that were your case, just let me know and i'll update the answer.

EDIT

Adding a Join with Sales_Order_Status_History to filter by the date when the order was set as 'pending'.

    $orders = Mage::getModel('sales/order')->getCollection()
    $select = $orders->getSelect();
    $select->join(
        array('sfosh' => 'sales_flat_order_status_history'),
        'sfosh.parent_id = main_table.entity_id',
        array()
    )
    ->group('main_table.entity_id');

    $orders
        ->setOrder('sfosh.created_at', 'ASC')
        ->addFieldToFilter('sfosh.created_at', array('lteq' => $finalDatetime))
        ->addFieldToFilter('main_table.status', array('eq' => 'pending'))
    ;
  • Yep ;) The exact moment status was set. Also we use this to release pending orders (release THE stock). So in essence i Would have to loop over all stores and get the date then offset it and then compare to the order per store? Just to release all pending orders order than 1 hour .... hmmm (super thanks btw) – snh_nl Jan 30 '16 at 21:04
  • mmm I think that you must to do it for each store (if they have different timezones), :( i'll update the answer with the status history join – MauroNigrele Jan 30 '16 at 21:13
  • hey i've just updated the answer. BTW tired of deal with this kind of issues i made a date helper a few years ago i've just uploaded to gist here gist.github.com/MauroNigrele/27ca96ab2eb58e462e4c fell free to use it. Using that helper you can grab the current UTC date just calling Mage::helper('vendor_module/date')->getNowUtc($store) – MauroNigrele Jan 30 '16 at 21:30

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.