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!

Loading timezones into MySQL or InfiniDB

If you are running queries or reports that utilize mysql timezone convertion function (CONVERT_TZ) make sure that you have run mysql utility (mysql_tzinfo_to_sql) that loads timezone information from the system into mysql server first. Without having loaded the timezone information one will simply get NULL return value and no message or error. This is expected behavior because the required tables for MySQL already exist but they are not populated!

In many threads and forums I have seen developers asking question on how to convert one timezone to another while all they get is null return value.  I myself have bitten by it couple of times when I moved to new MySQL servers where the following steps were not done.

Here I am using Ubuntu which has timezone files under /usr/share/zoneinfo directory. In many flavors of unix including Mac OS you will find it there, if not search for it with find command ;)

Also, note you can load all timezones of the world in one go. The SQL created by this utility is large with more than 142K lines of SQL code! After completing the task the following tables must have been populated.

1. mysql.time_zone
2. mysql.time_zone_name
3. mysql.time_zone_transition
4. mysql.time_zone_transition_type

There is another related table mysql.time_zone_leap_second which we don't need at this point.

# To load all timezone
shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

# You may see output like:
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
# On my system some specific timezone of Riyadh are not defined.  And you can ignore these warnings.

Restart mysql or infinidb server to bring in the timezones and run a test.

# In case of infinidb idbmysql is just an alias
shell> alias idbmysql
alias idbmysql='/usr/local/Calpont/mysql/bin/mysql --defaults-file=/usr/local/Calpont/mysql/my.cnf -u root'

shell> idbmysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.39 MySQL Community / Calpont InfiniDB Community 2.2-2 Final (COSS LA)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql> SELECT utc_timestamp(), CONVERT_TZ(utc_timestamp(),'UTC','MET');
+---------------------+-----------------------------------------+
| utc_timestamp()     | CONVERT_TZ(utc_timestamp(),'UTC','MET') |
+---------------------+-----------------------------------------+
| 2011-07-29 01:14:40 | 2011-07-29 03:14:40                     |
+---------------------+-----------------------------------------+
1 row in set (0.00 sec)