Magento group by clause on date field

Question

I need to fetch total number of order, SUM,MIN MAX and AVG of 'grand_total' in a day for a specified month. This is what I am doing.

$collection->getSelect()
               ->columns( 'SUM(base_grand_total) AS total' )
               ->columns( 'COUNT(*) AS orders_count' )
               ->columns( 'DATE_FORMAT(created_at, "%d") AS order_day' )
               ->columns( 'DATE_FORMAT(created_at, "%d/%m/%y") AS order_date' )
               ->columns( 'AVG(base_grand_total) AS avg_total' )
               ->columns( 'MAX(base_grand_total) AS max_total' )
               ->columns( 'MIN(base_grand_total) AS min_total' )
               ->where( 'DATE_FORMAT(created_at, "%m") = ?', $month )
               ->where( 'DATE_FORMAT(created_at, "%Y") = ?', $year )
               ->group( 'DATE_FORMAT(created_at, "%d-%m-%y")' );

$month is "Sep" and $year is "2014"

Above query says that there are 3 orders on 26th, but magento's Sales > Order grid says only one order is there for 26th. I guess the answer lies within, "created_at" stored as "2014-09-04 02:50:04" in DB, but if we format this its comes to "Sep 3, 2014 4:50:04 PM".

So, can anyone suggest how to apply group by date clause on Collection.

Thanks in Advance.

Answer 1

This is caused by the fact that Magento do parse dates from the database to set them in the timezone set under System > Configuration > General > Locale Options > Timezone. But actually saves the values in the database in GMT.

But that is an information you can get and convert the same way :

Solution 1: That consider the daylight saving shift, but needs you MySQL server to be configured properly and time zones to be loaded correctly.

To figure out if the time zones are loaded on your server, run this query

select * from mysql.time_zone_name;

If that returns you a list of time zones, you should be good to go (though other tables may have to be filled correctly, please also see this answer: https://stackoverflow.com/a/15419843/2123530)

If you don't have any records in this table, please refer to MySQL manual on how to load those information on your server: http://dev.mysql.com/doc/refman/5.7/en/mysql-tzinfo-to-sql.html

Then, when you are all good, that should be the proper query:

$GMTToLocaleTZDiff = Mage::getStoreConfig('general/locale/timezone',0);

$collection->getSelect()
               ->columns( 'SUM(base_grand_total) AS total' )
               ->columns( 'COUNT(*) AS orders_count' )
               ->columns( 'DATE_FORMAT(created_at, "%d") AS order_day' )
               ->columns( 'DATE_FORMAT(created_at, "%d/%m/%y") AS order_date' )
               ->columns( 'AVG(base_grand_total) AS avg_total' )
               ->columns( 'MAX(base_grand_total) AS max_total' )
               ->columns( 'MIN(base_grand_total) AS min_total' )
               ->columns( "CONVERT_TZ(created_at,'GMT','".$GMTToLocaleTZDiff."') AS created_at" )
               ->where( 'DATE_FORMAT(created_at, "%m") = ?', $month )
               ->where( 'DATE_FORMAT(created_at, "%Y") = ?', $year )
               ->group( 'DATE_FORMAT(created_at, "%d-%m-%y")' );

---

Solution 2: That could still lead you to an hour shift because of the daylight saving

$GMTToLocaleTZDiff = Mage::getSingleton('core/locale')->storeDate()->get(Zend_Date::GMT_DIFF_SEP);

$collection->getSelect()
               ->columns( 'SUM(base_grand_total) AS total' )
               ->columns( 'COUNT(*) AS orders_count' )
               ->columns( 'DATE_FORMAT(created_at, "%d") AS order_day' )
               ->columns( 'DATE_FORMAT(created_at, "%d/%m/%y") AS order_date' )
               ->columns( 'AVG(base_grand_total) AS avg_total' )
               ->columns( 'MAX(base_grand_total) AS max_total' )
               ->columns( 'MIN(base_grand_total) AS min_total' )
               ->columns( "CONVERT_TZ(created_at,'+00:00','".$GMTToLocaleTZDiff."') AS created_at" )
               ->where( 'DATE_FORMAT(created_at, "%m") = ?', $month )
               ->where( 'DATE_FORMAT(created_at, "%Y") = ?', $year )
               ->group( 'DATE_FORMAT(created_at, "%d-%m-%y")' );