2

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')
fusion3k
  • 11,568
  • 4
  • 25
  • 47
Vijay
  • 114
  • 2
  • 12

1 Answers1

2

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

Using the timezone offsets is problematic as America/Los_Angeles is -07:00 to day because of Daylight saving time, but otherwise -08:00.

You can use UTC and then 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

AbraCadaver
  • 78,200
  • 7
  • 66
  • 87
  • Does `PST` work without loading the timezone data in MySQL? – Mike Apr 28 '16 at 17:33
  • @Mike: Probably not: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html – AbraCadaver Apr 28 '16 at 17:34
  • If you have to load the timezone data in MySQL anyway presumably `America/Los_Angeles` should always work as well, wouldn't it? – Mike Apr 28 '16 at 17:38
  • Basically I'm trying to think about how you would go from `America/Los_Angeles` to `PST` or `-08:00` programatically. – Mike Apr 28 '16 at 17:39
  • Mike! i used -07:00 and it worked but it doesn't work with UTC PST.... I should set timezone? – Vijay Apr 28 '16 at 17:42
  • Well, it works with -7 now because of daylight savings time. Normally -8. – AbraCadaver Apr 28 '16 at 17:43
  • I would use named timezones *always*. Don't try to convert to an offset or you're into a world of hurt. If timezones aren't loaded in MySQL and you can't load them, you can do the conversion in PHP instead. As far as I know they are always available in PHP (even if they may be outdated and need to be updated manually). See http://php.net/manual/en/timezones.php. – Mike Apr 28 '16 at 17:56
  • Also for loading timezones into MySQL: http://stackoverflow.com/a/15419843/811240 – Mike Apr 28 '16 at 18:03