mysql - 如何根据当前用户时区从mysql检索数据

Question

我有一个数据库表存储客户的订单

现在我尝试根据用户每天的总订单绘制折线图，​​但由于订单日期时间以 utc 格式分开保存，因此图表不正确，我编写了以下查询

SELECT count(*) AS RECORD_COUNT,cast(concat(vtiger_salesorder.purchase_date,' ', vtiger_salesorder.purchase_time) as DATETIME) AS 'SalesOrder_Purchase_Date_and_Time_Day' ,`vtiger_salesorder`.`purchase_date` FROM vtiger_salesorder inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid WHERE vtiger_salesorder.salesorderid > 0 AND vtiger_crmentity.deleted=0 and (( vtiger_salesorder.purchase_date BETWEEN '2016-06-08 07:00:00' AND '2016-07-08 06:59:59' ) ) GROUP BY SalesOrder_Purchase_Date_and_Time_Day ORDER BY `vtiger_salesorder`.`purchase_date` ASC

例如，如果我有两个订单，日期时间为 2016-04-03 03:00:00那么对于 2014-04-02，它应该为 03 显示 1 和 0，但根据 EST 时区，它为 03 提供 1，为 02 提供 0

我能做什么？

Answer 1

终于找到了使用CONVERT_TZ函数的解决方案

SELECT count(*) AS RECORD_COUNT,date_format( CONVERT_TZ( cast(concat(vtiger_salesorder.purchase_date,' ',vtiger_salesorder.purchase_time) as DATETIME), 'GMT','America/Los_Angeles') , '%D %b %Y') AS 'SalesOrder_Purchase_Date_and_Time_Day' ,`vtiger_salesorder`.`purchase_date` FROM vtiger_salesorder inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid WHERE vtiger_salesorder.salesorderid > 0 AND vtiger_crmentity.deleted=0 and (( vtiger_salesorder.purchase_date BETWEEN '2016-06-08 07:00:00' AND '2016-07-08 06:59:59' ) ) GROUP BY SalesOrder_Purchase_Date_and_Time_Day ORDER BY `vtiger_salesorder`.`purchase_date` ASC