TImezone conversion for hourly report

We recently built a real time hourly report where data is streamed in and summarized through multiple jobs and pushed into reporting tables.  The business users wanted the reports as quickly as possible and first phase of the project needed to be implemented in short time.  With very limited time from concept to design and implementation, I utilized 3rd party web charting service (www.chart.io) and pushed the highly summarized data to set of tables.

During a final reporting, I needed to convert date and hour in UTC to PST.  The table had date (date type) and hour (tinyint) columns.  Chart.io uses simple x, y chart (line, bar, etc.) and takes any co-ordinates passed for them and displays them.  See below for one of final chart.

X axis is date and hour with format 'YYYY-MM-DD HH'.  The following SQL converted the date and hour from corresponding 2 columns with format YYYY-MM-DD and HH respectively in UTC timezone to PST. 

SELECT substr(convert_tz(concat(date, ' ', if(length(hour) = 1, concat('0',hour), hour)), 'GMT', 'America/Los_Angeles'), 1, 13) as x,

...

FROM

WHERE ...

GROUP BY x...

ORDER BY x ASC

Since maintaining the order is important. For example '2011-11-11 02' hour is right after '2011-11-11 01' hour and before '2011-11-11 03', single digit hours (1 through 9) are concatenated with "0'.  Then convert_tz converted the GMT timezone to PST (America/Los_Angeles) time.  There was suggestion to simply use -8.00 in hardcoded timezone difference but will result in incorrect hour during the months day light saving time (summer).   Since data is stored for each hour in tables over many months and years, by having the above technique one doesn't need to worry about this issue.  The system will automatically and correctly offset the hours in the report.  Handling timezone conversion is a not a trivial one and see timezone wiki for different timezones around the world.  For timezone conversion to work in MySQL, one should load the appropriate data into tables like this.

The reports/charts generated were very useful to business user and helped them take some quick decisive decisions and help build better user experience and improve sales and rev!