Magento checking order updated_at using Magento date and time

Question

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

Answer 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'))
    ;