by Chadwick Wood
October 13th, 2010
A couple of days ago, an HourPatch customer emailed me with a bug he'd found. When he created a time entry on a Saturday night, that entry would "jump" a week ahead on the schedule. I was able to fix it pretty quickly, and it turned out the problem related to time zone conversions, so I thought I'd share a little of what I learned when fixing that bug.
HourPatch stores all of your time entries in the database in UTC. Storing all times in the database in the same time zone makes some things easier (e.g. handling daylight savings time). But when you view your time entries, they are shown in your own time zone. I use Rails built-in time zone support to achieve this conversion. So, let's say you're in Texas, and you schedule some work from 1pm-2pm on a Thursday (Central Time). That entry gets stored in the database as being from 6pm-7pm UTC.
When you view your weekly schedule, HourPatch is showing you all the time entries you've scheduled for that week. So, it shows you all of your entries between Sunday and Saturday, inclusive. The code was something like this:
# start_date and end_date are passed in from the user
@time_entries = current_account.time_entries.find(:all,
:conditions => ["BETWEEN :start_date AND :end_date",
{ params[:start_date], params[:end_date] }]
The problem is, those time entries are stored in UTC, which is probably not the same as your time zone. So again, if you're in Texas, you'll actually be shown time entries between 7pm on Saturday and 6:59pm the following Saturday (Central Time), because that translates to 12am Sunday and 11:59pm Saturday UTC.
The solution is to translate 12am Sunday and 11:59pm Saturday from the user's time zone to UTC, and then find the time entries that fall in that range. With Rails, this is easy to do once you wrap your head around what you're doing (which took me awhile). Here is the corrected code. The user is passing in a start and end date, and we'll convert those dates (in the user's time zone) to times (in UTC).
# params[:start_date] (and end_date) are just dates
# however, we need to translate these into times in the user's time zone!
# set time zone to user's time zone (for conversions)
Time.zone = current_user.time_zone
# find 12:00am for start_date in user's time zone
@start_time = Time.zone.parse(params[:start_date]).utc
# find 11:59pm for end_date in user's time zone
@end_time = Time.zone.parse(params[:end_date]).advance(:hours => 23, :minutes => 59).utc
# fetch the time entries
@time_entries = current_account.time_entries.find(:all,
:conditions => ["start_time BETWEEN :start_time AND :end_time",
{:start_time => @start_time, :end_time => @end_time}]
)
We're using Time.zone.parse() to take the date given and return a TimeWithZone in the user's time zone. When you convert a date to a time, the time portion is set to 12am. So for @start_date, we just convert that TimeWithZone to UTC. For @end_time, we want the time to be 11:59pm, so we used advance() to set that time before converting to UTC.
So, the basic process for taking a request in a user's time zone and converting it to a query in the database is:
This stuff truly fries my brain sometimes. If you have any questions, let me know in the comments.