Filter and Group by date

[pljspahn]

Here's a chunk of a query that collects ordered items and groups them by item number and date.

SELECT DISTINCT
DATE(sales_flat_order_item.created_at) AS Date,
sales_flat_order_item.sku AS Item_Number,
sales_flat_order_item.name AS Item_Description
GROUP BY Date, Item_Number
ORDER BY Date DESC

When I filter this set of results on the date, it doesn't include records on the start date. For example, if the date filter is March 4 start and March 5 end, I only see records from March 5.

If I remove the DATE() function and just use the created_at value (which includes time of day, so I'm not able to group by that accurately) then results will match the start date.

[kalenjordan]

Hm not totally sure I'm following but I'm reading this pretty quickly right now. Maybe you can PR?

[rgranadino]

@pljspahn were you able to get past the timezone issues? I think you need to add the timezone the group by as well?

[sreichel]

To Reproduce the query should be ... (added missing FROM)

SELECT DISTINCT
DATE(sales_flat_order_item.created_at) AS Date,
sales_flat_order_item.sku AS Item_Number,
sales_flat_order_item.name AS Item_Description
FROM sales_flat_order_item
GROUP BY Date, Item_Number
ORDER BY Date DESC

Grid config:

{
    "filterable": {"Date": "adminhtml/widget_grid_column_filter_date"}
}

Tested against sample data ...

Filter FROM: 2013-03-28
Filter TO: 04.04.2013

... shows only 15 orders from 2013-03-28.

Changing grid config to adminhtml/widget_grid_column_filter_datetime fixed it and shows up all 30 orders.

Solution found here: http://stackoverflow.com/a/14241230/5703627

Close? ;)