user1464262 user1464262 - 7 months ago 10
PHP Question

How to get total row counted in different timezone in mysql

This is my sql query. It return total rows in UTC timezone. I want to count completed_date field in different timezone. like America/Los_Angeles timezone format. I heard that i need to use something GROUP or DATE function but i don't get any idea.

SELECT COUNT(*)
FROM `team_users`
WHERE (`completed_date` BETWEEN '2016-04-27 00:00:00' AND '2016-04-27 23:59:59')

Answer

This should work to convert completed_date so that you can compare your dates with it:

SELECT COUNT(*) 
  FROM `team_users` 
  WHERE (CONVERT_TZ(`completed_date`, '+00:00', '-08:00')
  BETWEEN  '2016-04-27 00:00:00' AND  '2016-04-27 23:59:59')

You can use PST for Pacific Standard Time or America/Los_Angeles if your system has been configured to use named timezones: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html