SELECT CONVERT_TZ(NOW(), '+00:00', 'Etc/UTC'); not a sufficient as test

[salzig]

Hej,
just wanted to mention that SELECT CONVERT_TZ(NOW(), '+00:00', 'Etc/UTC'); isn't a sufficient test to check if groupdate works.

Running SELECT CONVERT_TZ(NOW(), '+00:00', 'Etc/UTC'); on the MySQL console, and running ActiveRecord::Base.connection.exec_query("SELECT CONVERT_TZ(NOW(), '+00:00', 'Etc/UTC');") work as expected (showing the converted time) in Development and Staging.

Sadly i'm running in a problem, where it's working in development but not on staging.
Executing the generated Query (Logged by activerecord for: Profile.group_by_month(:created_at).count) within the mysql console works like expected in both environments. But Profile.group_by_month(:created_at).count will only work in Development, and throw an exception (Groupdate::Error (Be sure to install time zone support - https://github.com/ankane/groupdate#for-mysql):) in Staging.

In Development Profile.group_by_month(:created_at).count will return a hash with Date=>Integer Mappings.
In Staging it returns: {nil=>1118}

- Development Query:
SELECT COUNT(*) AS count_all, CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', 'Etc/UTC'), '%Y-%m-01 00:00:00'), 'Etc/UTC', '+00:00') AS convert_tz_date_format_convert_tz_created_at_00_00_etc_utc_y_m_01_00_00_00_etc_utc_00_00 FROM `profiles` WHERE (created_at IS NOT NULL) GROUP BY CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', 'Etc/UTC'), '%Y-%m-01 00:00:00'), 'Etc/UTC', '+00:00')
- Staging Query:
SELECT COUNT(*) AS count_all, CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', 'Etc/UTC'), '%Y-%m-01 00:00:00'), 'Etc/UTC', '+00:00') AS convert_tz_date_format_convert_tz_created_at_00_00_etc_utc_y_m_01_00_00_00_etc_utc_00_00 FROM `profiles` WHERE (created_at IS NOT NULL) GROUP BY CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', 'Etc/UTC'), '%Y-%m-01 00:00:00'), 'Etc/UTC', '+00:00')

[ankane]

Hey @salzig, unfortunately, I'm not sure how to reproduce. What's the difference between your development and staging databases? Are you able to reproduce with a fresh staging database?

[salzig]

No, sadly i'm unable to reproduce it. My intention was to leave a hint that the test is maybe not sufficient.

[ankane]

Unfortunately, don't think there's much I can do without a way to reproduce on this one. Please let me know if you figure it out.