I have a report that uses a an sql query similar to this:
SELECT HOUR(somedatetime) AS hour, COUNT(*) AS count
FROM table
GROUP BY HOUR(surveyenddatetime)
I've simplified it here by leaving out the where clause that can limit the results to a given time period. It works quite nicely for getting the total records grouped by hour of day they were entered for the time period selected.
However, it has one fatal flaw. When the time period selected includes records not in the same 'timezone' as the user running the report, such as records from before daylight savings and records after daylight savings, it doesn't work. All records are stored in GMT/UTC on in the datetime fields and converted to local times in php via the web front end. But this query runs before any of that happens and php has no way of knowing what the original dates where for the totals grouped into each hour.
Is there a way I can solve this query to take daylight savings into account using just a MySQL query or am I going to have to do something more complicated?