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

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

July 31, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento 1.7 - filter on date part of a timestamp column in grid

Filtering Date Part of Timestamp Column in Magento Grid

When working with timestamp columns in Magento grids, you might face challenges when trying to filter by date only, ignoring the time part. This is especially common when using the built-in date selector provided by Magento.

In your scenario, you have a grid column order_date that represents a timestamp. When you attempt to filter using the date “13 Oct 2012”, no results are returned because the selector filters on both date and time parts of the column.

To solve this issue, we need to adjust our code to explicitly perform a date-only comparison when filtering the column. Here’s how you can achieve this:

Step 1: Modify the Column Definition

First, let’s update your existing column definition to include additional parameters that allow for better control over the filter behavior:

$this->addColumn('order_date', array(
    'header' => $this->__('Date'),
    'align' => 'left',
    'width' => '100px',
    'index' => 'created_at',  // Use the actual column name from your orders table
    'type' => 'date',
    'filter_index' => 'orders_alias.created_at',
    'filter_condition_callback' => array($this, 'filterDateConditionCallback'),
));

We’ve added a new parameter called filter_condition_callback. This allows us to specify a custom callback function that will handle the filtering logic for this column.

Step 2: Implement the Callback Function

Now, let’s create the filterDateConditionCallback function inside your grid class or within an appropriate block:

protected function filterDateConditionCallback($collection, $column)
{
    $value = $column->getFilter()->getValue();

    if ($value) {
        // Convert the given date to a timestamp format that matches the column type
        $date = Mage::app()->getLocale()->date($value)->toString('yyyy-MM-dd HH:mm:ss');

        // Create a dynamic where condition for the time part of the timestamp column
        $collection->getSelect()
            ->where("DATE_FORMAT(orders_alias.created_at, '%H:%i:%s') = '00:00:00'")
            ->andWhere('orders_alias.created_at >= ?', $date)
            ->andWhere('orders_alias.created_at < ?', date('Y-m-d H:i:s', strtotime($value . ' +1 day')));
    }

    return $collection;
}

In this callback function, we first convert the given date to a timestamp format that matches the column type. Then, we create a dynamic where condition using MySQL’s DATE_FORMAT function and Magento’s date manipulation functions.

The condition checks for rows where the time part of the created_at column is ‘00:00:00’ (i.e., only the date part), while the date itself matches the provided filter value within a one-day range.

Step 3: Refreshing the Grid

After implementing these changes, you should be able to filter your grid using the Magento date selector. When selecting “13/10/2012”, it will now show all rows with this date regardless of the time part.

Remember that for this solution to work seamlessly, ensure that your orders table’s created_at column is a timestamp type (e.g., DATETIME or TIMESTAMP), and that you have properly configured your locale settings in Magento to accurately parse dates from user input.

Conclusion

Filtering date parts of timestamp columns in Magento grids can be achieved by leveraging custom callback functions for the filter condition. By using MySQL’s DATE_FORMAT function in conjunction with Magento’s date manipulation utilities, we can create dynamic where conditions that compare only the date part of the column while ignoring the time component.

This approach ensures that your grid filters work as expected when using the built-in date selector provided by Magento, providing a better user experience for customers and administrators alike.

For more advanced filtering techniques in Magento grids or to learn about other features offered by our platform, feel free to explore the extensive resources available on LaravelCompany.com.

Magento 1 Database Admin Orders

Our network