Believe it or not, somehow Hired has gotten away with having zero concern for timezones beyond American Pacific throughout its ~2 years of operation. We managed to launch markets across the continent, and even host a global ‘international auction’ at the beginning of the visa season without ever needing to tackle the beast. This is pretty amazing, when I think about it, and I believe this is a testament to the strong product leadership we have. We’d have been wasting our time if we had spent an ounce of effort on it before now.
So the time has come, as it does inevitably for any company hoping to grow beyond their region. As such, I did a bit of research to make sure I didn’t miss any gotchas in the process of building this out, and this railscast helped me feel confident that there weren’t any black holes of doom around the corner that I should avoid. On the subject of black holes of doom, it’s pretty important for me to point out that I paired with two stellar rails developers on our team, Nate and Kyle, on various parts of this feature. Having a pair-programming partner is one of the best ways to avoid black holes of doom, no matter how much experience you may have.
We pretty much followed the railscast’s suggestions, although we did decide to store the time zone string using the identifier (ex- America/Los_Angeles) rather than the common name (ex- Pacific Time), simply because moment.js, the chronotastic JS library, accepts the identifier, rather than the name, when displaying timezones in the browser. This decision greatly simplified our use of the library, allowing us to to avoid later work when displaying specific timezones.
We still needed to capture the browser’s timezone on signup for future users and backfill the timezone based on known location data for existing users. Detecting the browser’s timezone and getting a nice identifer string was as easy as dropping in another JS library, jstz. But how should we migrate existing users? There are varying degrees of smart and lazy that we could be here, but it’s our job as programmers to find the optimal combination of the two. If we hadn’t run an international auction already, it probably would have been “good enough” to manually bucket people into one of the two or three timezones we are in right now; however, after spending 5 minutes writing a lazy man’s grouping script, it became clear that we’d still have way too many accounts without an accurate timezone. That is when Nate found this awesome file from (@lareuntdebricon)[https://github.com/laurentdebricon] containing useful approximate coordinates for all of the timezones across the globe. Thus was born the vision for a script that makes a best-guess at your timezone since we could cross-reference these coordinates with our user lat/lng data. The algorithm is quite simple:
For each user
find the closest TZ entry on the temp data table
set user timezone to that TZ's identifier
But we wanted to make sure we could run this without bringing the site down for maintenance, locking up tables for too long, or requiring further input from us. So our script pipes the data in straight from github into a temporary table, runs the find for each user, and finally deletes the temporary table. (This was run immediately after the migration adding the time_zone column to the users table.)
# load temp table
file = open "https://gist.githubusercontent.com/laurentdebricon/1769458/raw/dc79733325f9b468ff2d44ff3813924d6d1e6382/lat,lng,timezone"
conn = ActiveRecord::Base.connection
raw = conn.raw_connection
raw.exec %q{
CREATE TABLE time_zones_lat_lng (
latitude double precision,
longitude double precision,
timezone varchar
);
}
# We have to go through STDIN because we use heroku postgres
# and COPY requires super-user priveleges unless going through STDIN
result = nil
raw.exec("COPY time_zones_lat_lng FROM STDIN DELIMITER ' '")
file.each_line do |line|
raw.put_copy_data( line.rstrip << "\n" )
end
raw.put_copy_end
while res = raw.get_result; end #wait for the PG adapter to finish
# clean out some of the timezone locations not understood by rails,
since we'll be validating them as described by the railscast.
valid_timezones = ActiveSupport::TimeZone.all.map { |zone| "'#{zone.tzinfo.identifier}'" }
conn.execute %Q{
DELETE FROM time_zones_lat_lng
WHERE timezone NOT IN (#{valid_timezones.join(',')})
}
# query against temp table for all users
offset = 0
limit = 200
batch = User.limit(limit).offset(offset)
while batch.any?
batch.update_all %q{
time_zone = (
SELECT timezone FROM (
SELECT timezone, point(tz.longitude,tz.latitude) <-> point(users.lng,users.lat) AS distance
FROM time_zones_lat_lng tz
ORDER BY distance LIMIT 1
) t LIMIT 1
)
}
sleep 1.seconds
offset = offset + limit
batch = User.limit(limit).offset(offset)
end
# drop temp table
ActiveRecord::Base.connection.execute %q{ DROP TABLE time_zones_lat_lng }
We took a conservative approach when batching users to avoid table locks on massive updates with this slow sub-select. And we are taking advantage of the fact there is no ‘live’ feature that heavily uses this time_zone field yet, so it’s okay if it’s wrong for a few hours during low-traffic times.
Overall the deploy went really well, and we’re ready to continue global conquest.