这是由于 Magento 会解析数据库中的日期并将其设置为下设置的时区System > Configuration > General > Locale Options > Timezone
。但实际上是以 GMT 时间保存数据库中的值。
但这是您可以以相同方式获取和转换的信息:
解决方案1:考虑夏令时转变,但需要正确配置 MySQL 服务器并正确加载时区。
要确定您的服务器上是否加载了时区,请运行此查询
select * from mysql.time_zone_name;
如果返回时区列表,您应该可以开始(尽管其他表格可能必须正确填写,另请参阅此答案:https://stackoverflow.com/a/15419843/2123530 https://stackoverflow.com/a/15419843/2123530)
如果您的表中没有任何记录,请参阅 MySQL 手册了解如何在您的服务器上加载这些信息:http://dev.mysql.com/doc/refman/5.7/en/mysql-tzinfo-to-sql.html http://dev.mysql.com/doc/refman/5.7/en/mysql-tzinfo-to-sql.html
然后,当一切顺利时,这应该是正确的查询:
$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")' );
解决方案2:由于夏令时,这仍然可能导致您轮班一个小时
$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")' );