ROBIN ROBIN - 7 months ago 94
SQL Question

Magento group by clause on date field

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"

Current Output for the above query on Custom Grid System

what Sales > Order says for same time period

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

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 timezones to be loaded correctly.

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

select * from mysql.time_zone_name;

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

If you don't have any recors 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")' );