Adding a Date Range to a Magento EAV Collection

Dates and time are always messy to work with in programming environments, and coming up with the right set of filters to accurately represent a date range in Magento is no exception. This is “extra” true given the trickiness of creating nested OR queries with Magento’s ORM.

Fortunately, someone on the Magento core team already went through the trouble. The new product block’s _beforeToHtml method adds a “new from” date filter to a product collection.

#File: app/code/core/Mage/Catalog/Block/Product/New.php
protected function _beforeToHtml()
{
    $todayStartOfDayDate  = Mage::app()->getLocale()->date()
        ->setTime('00:00:00')
        ->toString(Varien_Date::DATETIME_INTERNAL_FORMAT);

    $todayEndOfDayDate  = Mage::app()->getLocale()->date()
        ->setTime('23:59:59')
        ->toString(Varien_Date::DATETIME_INTERNAL_FORMAT);

    $collection = Mage::getResourceModel('catalog/product_collection');
    $collection->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds());


    $collection = $this->_addProductAttributesAndPrices($collection)
        ->addStoreFilter()
        ->addAttributeToFilter('news_from_date', array('or'=> array(
            0 => array('date' => true, 'to' => $todayEndOfDayDate),
            1 => array('is' => new Zend_Db_Expr('null')))
        ), 'left')
        ->addAttributeToFilter('news_to_date', array('or'=> array(
            0 => array('date' => true, 'from' => $todayStartOfDayDate),
            1 => array('is' => new Zend_Db_Expr('null')))
        ), 'left')
        ->addAttributeToFilter(
            array(
                array('attribute' => 'news_from_date', 'is'=>new Zend_Db_Expr('not null')),
                array('attribute' => 'news_to_date', 'is'=>new Zend_Db_Expr('not null'))
                )
          )
        ->addAttributeToSort('news_from_date', 'desc')
        ->setPageSize($this->getProductsCount())
        ->setCurPage(1)
    ;

    $this->setProductCollection($collection);

    return parent::_beforeToHtml();