Magento order created_at wrong date

Question

I'm trying to get all orders in magento, from a specific user entered date.

$orders = Mage::getModel('sales/order')->getCollection()->addAttributeToFilter('created_at', array('from' => $userdate));

But its not pulling out the correct orders. If I use today's date, It pulls orders half from yesterday and half from today.

After a bit of Googling, it looks like there's two dates stored in Magento

$order->getCreatedAt()

Seems to give a UTC/GMT time

$order->getCreatedAtStoreDate()

Gives the same time as I see in the frontend (ie. my local timezone).

If I'm correct in what I have found, then how do I addAttributeToFilter using the CreatedAtStoreDate. I have tried

('created_at_store_date', array('from' => $userdate)

Answer 1

<?php

ini_set('display_errors',true);
include 'app/Mage.php';
Mage::getIsDeveloperMode(true);
Mage::app();

$formatStr = 'Y-m-d H:i:s';
$startStr  = 'Yesterday 12:00:00AM';
$endStr    = 'Yesterday 11:59:59PM';

$date   = Mage::getSingleton('core/date');
/* @var $date Mage_Core_Model_Date */

$collection = Mage::getResourceModel('sales/order_collection');

$collection->getSelect()
            ->where(
                sprintf("created_at BETWEEN '%s' AND '%s'",
                    $date->gmtDate($formatStr,$startStr),
                    $date->gmtDate($formatStr,$endStr)
                )
            );

$collection->load(true);