gem groupdate issue with 2 timezones for same column

Question

I'm using the gems ahoy to track visits and groupdate to show daily stats.

To count daily visits I use this query in Visit model:

Visit.group_by_day(:started_at).order('started_at').count

It worked fine until last day 18, when my timezone changed from BRT (-03:00) to BRST (-02:00).

Now, the above query returns 0 visits count for each day after 18:

2.1.3 :026 > puts Visit.group_by_day(:started_at).order('started_at desc').limit(10).count.to_yaml
   (0.3ms)  SELECT COUNT(*) AS count_all, DATE_ADD(CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(DATE_SUB(started_at, INTERVAL 0 HOUR), '+00:00', 'America/Sao_Paulo'), '%Y-%m-%d 00:00:00'), 'America/Sao_Paulo', '+00:00'), INTERVAL 0 HOUR) AS day FROM `visits` WHERE (started_at IS NOT NULL) GROUP BY DATE_ADD(CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(DATE_SUB(started_at, INTERVAL 0 HOUR), '+00:00', 'America/Sao_Paulo'), '%Y-%m-%d 00:00:00'), 'America/Sao_Paulo', '+00:00'), INTERVAL 0 HOUR) ORDER BY started_at desc LIMIT 10
---
2015-10-14 03:00:00.000000000 Z: 158
2015-10-15 03:00:00.000000000 Z: 127
2015-10-16 03:00:00.000000000 Z: 101
2015-10-17 03:00:00.000000000 Z: 112
2015-10-18 03:00:00.000000000 Z: 111
2015-10-19 03:00:00.000000000 Z: 0
2015-10-20 03:00:00.000000000 Z: 0
2015-10-21 03:00:00.000000000 Z: 0
2015-10-22 03:00:00.000000000 Z: 0

But, if I select only the last 5 days (which are all in the BRST timezone), it works:

2.1.3 :027 > puts Visit.group_by_day(:started_at).order('started_at desc').limit(5).count.to_yaml
   (0.3ms)  SELECT COUNT(*) AS count_all, DATE_ADD(CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(DATE_SUB(started_at, INTERVAL 0 HOUR), '+00:00', 'America/Sao_Paulo'), '%Y-%m-%d 00:00:00'), 'America/Sao_Paulo', '+00:00'), INTERVAL 0 HOUR) AS day FROM `visits` WHERE (started_at IS NOT NULL) GROUP BY DATE_ADD(CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(DATE_SUB(started_at, INTERVAL 0 HOUR), '+00:00', 'America/Sao_Paulo'), '%Y-%m-%d 00:00:00'), 'America/Sao_Paulo', '+00:00'), INTERVAL 0 HOUR) ORDER BY started_at desc LIMIT 5
---
2015-10-19 02:00:00.000000000 Z: 85
2015-10-20 02:00:00.000000000 Z: 72
2015-10-21 02:00:00.000000000 Z: 84
2015-10-22 02:00:00.000000000 Z: 80
2015-10-23 02:00:00.000000000 Z: 21

I did these 2 queries directly in mysql server, and both returns correct results.

ie, when the query covers records with 2 different timezones, groupdate can't count the records of the last one.

I want to make sure this is a groupdate gem bug. Or I missing something?

Answer 1

I believe it is because you are doing zero-ing out the HH:mm:ss in between the two convert_tz's

Please update your SELECT for the 'day' variable:

1. remove the DATE_FORMAT
2. put the whole thing inside DATE() AS day

And then replace the GROUP BY value with just: day - or you can copy-paste the changes above into the group by