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!
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!