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

Question

I have the following column in the grid:

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

The sample data is as follows: http://imageshack.us/photo/my-images/502/scr028.jpg/

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

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

Answer 1

This is an error that only appears when you try to display a value, since the date and database column is a date or timestamp.

This is because the date is changed to YYYY-MM-DD HH: MM: SS and HH: MM: SS - 00:00:00 for a date from and to. Date must be set at 23:59:59.

One way to do this is 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 will require changes to Datetime.php getValue (removing code that adds one day and removes one second), and also because you should not change the kernel code, the second solution will definitely be preferred.

 $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 filters, the best 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 cause the temporary part to appear in the grid fields, but allow you to use filters as you described above.