在日期字段上的Magento group by子句 [英] Magento group by clause on date field

问题描述

我需要在指定月份的一天内获取grand_total的订单总数，SUM，MIN MAX和AVG。这就是我所做的。

  $ collection-> getSelect（）
  - > columns（'SUM （base_grand_total）AS total'）
  - >列（'COUNT（*）AS orders_count'）
  - >列（'DATE_FORMAT（created_at，％d）AS order_day'）
  - >列（'DATE_FORMAT（created_at，％d /％m /％y）AS order_date'）
  - > columns（'AVG（base_grand_total）AS avg_total'）
  - >列（'MAX（base_grand_total）AS max_total'）
  - >列（'MIN（base_grand_total）AS min_total'）
  - >其中（'DATE_FORMAT（created_at，％m） =''，$ month）
  - >其中（'DATE_FORMAT（created_at，％Y）=？'，$ year）
  - > group（'DATE_FORMAT（created_at，％d - ％m-％y）'）; 
  

$ month为Sep，$ year为2014

上面的查询说26日有3个订单，但是magento的销售>订单网格表示26日只有一个订单。我想答案在DB中存储为
created_at ，存储为2014-09-04 02:50:04，但如果我们将其格式化为Sep 3， 2014 4:50:04 PM。

那么，任何人都可以建议如何在集合上应用group by date子句。

$ p

解决方案

这是由于Magento从数据库解析日期来设置它们在 System>下设置的时区配置>一般>区域设置选项>时区。但实际上在格林尼治标准时间保存在数据库中的值。

但是，这是一个信息，你可以得到和转换相同的方式：

解决方案1 ​​：考虑夏令时转换，但需要您正确配置MySQL服务器并正确加载时区。

要确定服务器上是否加载了时区，请运行此查询

  select * from mysql.time_zone_name ; 
  

如果您返回一个时区列表，您应该很好（但其他表格可能会必须正确填写，也请参阅此答案： https://stackoverflow.com/a/15419843/2123530 ）

如果您在此表中没有任何记录，请参阅MySQL手册，了解如何在服务器上加载这些信息： http://dev.mysql.com/doc/refman/5.7/en/ mysql-tzinfo-to-sql.html

然后，当你一切顺利的时候，这应该是正确的查询：

  $ GMTToLocaleTZDiff = Mage :: getStoreConfig（'general / locale / timezone'，0）; （''SUM（base_grand_total）AS''）
  - >列（'COUNT（*）AS orders_count '）
  - >列（'DATE_FORMAT（created_at，％d）AS order_day'）
  - >列（'DATE_FORMAT（created_at，％d /％m /％y） AS'order_date'）
  - >列（'AVG（base_grand_total）AS avg_total'）
  - >列（'MAX（base_grand_total）AS max_total'）
  - > MIN（base_grand_total）AS min_total'）
  - >列（CONVERT_TZ（created_at，'GMT'，'。$ GMTToLocaleTZDiff。'）AS created_at）
  - > where（'DATE_FORMAT （created_at，％m）=？'，$ month）
  - >其中（'DATE_FORMAT（created_at，％Y）=？'，$ year）
  - > group（ 'DATE_FORMAT（created_at，％d-％m-％y）'）; 
  

---

解决方案2 ：由于夏令时，这仍然可能导致您转换小时数

  $ GMTToLocaleTZDiff =法师:: getSingleton（'core / locale ） - > storeDate（） - >获得（Zend_Date的运算:: GMT_DIFF_SEP）; （''SUM（base_grand_total）AS''）
  - >列（'COUNT（*）AS orders_count '）
  - >列（'DATE_FORMAT（created_at，％d）AS order_day'）
  - >列（'DATE_FORMAT（created_at，％d /％m /％y） AS'order_date'）
  - >列（'AVG（base_grand_total）AS avg_total'）
  - >列（'MAX（base_grand_total）AS max_total'）
  - > MIN（base_grand_total）AS min_total'）
  - >列（CONVERT_TZ（created_at，'+ 00:00'，'。$ GMTToLocaleTZDiff。'）AS created_at）
  - > where （'DATE_FORMAT（created_at，％m）=？'，$ year）
  - >其中（'DATE_FORMAT（created_at，％y）=？'，$ year）
  - > ;组（'DATE_FORMAT（created_at，％d-％m-％y）'）; 
  

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.

解决方案

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

这篇关于在日期字段上的Magento group by子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！