Magento group parameter in the date field

Question

I need to get the total number of orders, SUM, MIN MAX and AVG "grand_total" per day for the specified month. This is what I do.

$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 the request, it says that there are 3 orders on the 26th, but magento Sales> Order grid says that only one order exists on the 26th. I think the answer lies inside, " created_at " is saved as "2014-09-04 02:50:04" in the database, but if we format it, it will be "September 3, 2014 16:50:04".

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

Thanks at Advance.

Answer 1

This is due to the fact that Magento parses dates from the database to set them in the time zone set in System > Configuration > General > Locale Options > Timezone . But actually stores the values ​​in the database in GMT.

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

Solution 1 This refers to the daylight saving time shift, but you need to configure the MySQL server correctly and configure the time zones correctly.

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

 select * from mysql.time_zone_name; 

If this returns you a list of time zones, you should be good to go (although other tables may be populated correctly, see also this answer: fooobar.com/questions/79446 / ... )

If you do not have entries in this table, refer to the MySQL manual on how to upload this information to your server: http://dev.mysql.com/doc/refman/5.7/en/mysql-tzinfo-to-sql. html

Then, when you're fine, this should be the correct request:

 $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 This may result in hourly switching due to daylight saving time.

 $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")' );