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));
Best Answer
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:
Now you can play with Zend_Date:
Using
$finalDatetime
in 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 withstatus_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'.