MySQL Query For HeatMap

README.md

MySQL query that creates the data for a heatmap that takes in timezone. This will form the data necessary to produce a map like the Trulia Trends graph.

Initially I wrote a query that did not account for timezones

SELECT WEEKDAY(date_created)+1 as weekday, HOUR(date_created)+1 as hour, SUM(sub_total) as value FROM transaction WHERE voided = 0 GROUP BY weekday, hour;

This works great, if all of our customers lived in the Greenwhich Royal Observatory and shared that timezone. But as soon as we move outside that timezone, things get more complicated.

The second solution I came up with was to set an offset. And it looked like the following

SELECT WEEKDAY(DATE_SUB(date_created,INTERVAL 6 HOUR))+1 as weekday, HOUR(DATE_SUB(date_created,INTERVAL 6 HOUR))+1 as hour, SUM(sub_total) as value FROM transaction WHERE voided = 0 GROUP BY weekday, hour;

This works great if you aren't worried about Daylight Savings Time, but since most of our customers are in the continental United States, this query would not take that into account. Here I was assuming that "a time zone" = "an offset", which is incorrect, so I needed a better solution.

This lead me to the final solution and to a function in MySQL called CONVERT_TZ. If I wanted to get the current time in central time, I would use:

SELECT CONVERT_TZ(NOW(),@@session.time_zone,"US/Central")

Initially on my local machine this would return null. CONVERT_TZ requires the timezone tables to be loaded.

heatmap.java

//Using the mysql compatible timezones, this will create a query for the Heatmap that accounts for timezones and DST
public String sqlForHeatmap(String mySqlTimezone) {
	String adjustedDateQuery = "CONVERT_TZ(date_created,@@session.time_zone,'"+mySqlTimezone+"')";
	String sql = "SELECT WEEKDAY("+adjustedDateQuery+")+1 as weekday, HOUR("+adjustedDateQuery+")+1 as hour, SUM(sub_total) as value FROM transaction WHERE voided = 0 GROUP BY weekday, hour";
	return sql;
}

thumbnail.png