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!

Converting 12 hour time Unix timestamp

Many a times I need to transform time in AM/PM format to unix timestamp for better performance.  The external data coming in has date and time in two separate columns like '2011-01-01' as date and '11:59:59 PM' as time.   And some other time, I also need to convert timezones typically from a UTC to PST and to do that check this out.

For example, converting "2011-01-01 11:59:59 PM" into corresponding unix timestamp results in an integer 1293926399.

# Create a temp table...
create table tmp.test_dt (
  d date,
  t varchar(20)
)engine=InnoDB


# Insert a value to test...
insert into tmp.test_dt
(d, t) values
('2011-01-01', '11:59:59 PM')


# Run the conversion sql...
SELECT 
   unix_timestamp(concat(cast(d as char),  ' ',  cast(time_format(str_to_date(t,'%r'),'%T') as char)  ) )
FROM tmp.test_dt
# Output: 1293926399


# To test
SELECT from_unixtime(1293926399)
# Output: "2011-01-01 23:59:59"


In the conversion select statement, time_format converts the time (t column) from 11:59:59 PM to 23:59:59 of datetime type which is then cast as char to concatenate with date.  After concat the string looks like "2011-01-01 23:59:59" which becomes input to unix_timestamp.