Magento 1.7 - filter on date part of a timestamp column in grid

Question

I have the following column in a grid:

        $this->addColumn('order_date',
        array(
            'header'=> $this->__('Date'),
            'align' =>'left',
            'width' => '100px',
            'index' => 'order_date',
            'type' => 'date',
            'filter_index' => 'orders_alias.created_at'
        )
    );

Example data looks like this: http://imageshack.us/photo/my-images/502/scr028.jpg/

When filtering on date 13 Oct 2012 no rows are found. This makes sense because it is a timestamp column. http://imageshack.us/photo/my-images/29/scr029.jpg/

How can I do this to use the Magento date from/to selector and select 13/10/2012 and show all rows with this date regardless of the time part?

Answer 1

This a bug that only shows up when you are trying to show a value as date and database column is datetime or timestamp.

It happens because date is changed to YYYY-MM-DD HH:MM:SS and HH:MM:SS being 00:00:00 for date from and to. The date to should be set to 23:59:59.

One way to do this would be to change the code in app/code/core/Mage/Adminhtml/Block/Widget/Grid/Column/Filter/Date.php to support this. In setValue function just change

EDIT: This first solution would require changes in Datetime.php getValue (removing code that adds one day and removes one second) as well and because you shouldn't change core code the second solution would definitely be the preferred one.

$value['to'] = $this->_convertDate($value['to'], $value['locale']);

to

$value['to'] = $this->_convertDate($value['to'], $value['locale'])->addDay(1)->subSecond(1);

Since this changes the functionality of all the filters a better solution would be to change your local code:

$this->addColumn('order_date',
    array(
        'header'=> $this->__('Date'),
        'align' =>'left',
        'width' => '100px',
        'index' => 'order_date',
        'type' => 'datetime',
        'filter_index' => 'orders_alias.created_at',
        'frame_callback' => array( $this,'styleDate' )
    )
// ...

public function styleDate( $value,$row,$column,$isExport )
{
  $locale = Mage::app()->getLocale();
  $date = $locale->date( $value, $locale->getDateFormat(), $locale->getLocaleCode(), false )->toString( $locale->getDateFormat() ) ;
  return $date;
}

This will remove time part from being shown in the grid's fields but will let you use filters the way you described above.