Rails/MySQL: Different user timezones

Question

Question 1 - I have an app that is on shared hosting in Dallas, TX...so my database/web server are currently set to Central US time...I'm unable to change this, so UTC is off the table.

My application records and displays Date and Time data to the user, but every user could be in a different time zone.

If I configure my app (config.time_zone = 'Central Time (US & Canada)'), but then I allow users to select their timezone,

def user_time_zone(&block)
  Time.use_zone(current_user.time_zone, &block)
end

will RoR display any returned data in the timezone of the user's choice? Or do I need to evaluate the data and modify it accordingly before it is displayed?

Question 1.1 - I have related mailers that send daily/weekly/monthly reports to users. If I run these using CRON, would I need to schedule my jobs to at different time intervals to allow for the timezones? In other words, if one job contains

when 'daily'
   @dates = params[:f]['Date Range'] = "#{Date.current} - #{Date.current}"

Date.current will be 1 hour ahead on the East Coast, and 3 hours behind on the West and so forth. So (still using server timezone of Central), would I run one at 11pm (for East) and one at 2am (for West)?

Any and all advice/assistance is greatly appreciated. Thanks!

Answer 1

Very clear answer from Matt. You definitely want to save your time data in UTC TZ, then translate it to your user TZ (or server TZ for guests users)

You are using MySQL globally set to non-UTC TZ, you can override this setting with the following query

SET time_zone = 'UTC';

Problem is: this command is session wide in MySQL, you must set it again each time a new connection to you database is initialized. Rails ActiveRecord::ConnectionAdapters::ConnectionPool class can help you for that. See this answer: How do I execute queries upon DB connection in Rails?

Next you need to set up your server default TZ in application.rb (you did that) and set up each user to have his own TZ (and you did that as well)

Finaly you must follow Rails conventions each time you access time data. I found this article for you, full of very useful information it will answer all your question: http://www.elabs.se/blog/36-working-with-time-zones-in-ruby-on-rails

Extract from that article:

DOs

2.hours.ago # => Fri, 02 Mar 2012 20:04:47 JST +09:00
1.day.from_now # => Fri, 03 Mar 2012 22:04:47 JST +09:00
Date.today.to_time_in_current_zone # => Fri, 02 Mar 2012 22:04:47 JST +09:00
Date.current # => Fri, 02 Mar
Time.zone.parse("2012-03-02 16:05:37") # => Fri, 02 Mar 2012 16:05:37 JST +09:00
Time.zone.now # => Fri, 02 Mar 2012 22:04:47 JST +09:00
Time.current # Same thing but shorter. (Thank you Lukas Sarnacki pointing this out.)
Time.zone.today # If you really can't have a Time or DateTime for some reason
Time.zone.now.utc.iso8601 # When supliyng an API (you can actually skip .zone here, but I find it better to always use it, than miss it when it's needed)
Time.strptime(time_string, '%Y-%m-%dT%H:%M:%S%z').in_time_zone(Time.zone) # If you can't use Time#parse

DON’Ts

Time.now # => Returns system time and ignores your configured time zone.
Time.parse("2012-03-02 16:05:37") # => Will assume time string given is in the system's time zone.
Time.strptime(time_string, '%Y-%m-%dT%H:%M:%S%z') # Same problem as with Time#parse.
Date.today # This could be yesterday or tomorrow depending on the machine's time zone.
Date.today.to_time # => # Still not the configured time zone.

Answer 2

For best results, your application should be written such that the time zone of the computer that it is running on does not influence any data or behavior.

That usually means working in UTC in your code. For example, Time.now.utc instead of just Time.now. See this post for details for RoR.

It is a best practice to set the server time zone to UTC when possible, but that doesn't mean you should rely on that in your code.

Regarding your second question, keep in mind that every time zone has their own concept of a "day". See this site for a visualization.

You should run a separate job at midnight in each time zone that you are aligning your data to. Some applications (such as the StackExchange web sites) use a single time zone (often UTC) for all users. Other applications adjust the data for each user according to their own time zone. You need to decide what is appropriate for your application.

When scheduling your jobs, determine the UTC time that each time zone's midnight aligns to. Then schedule a job for that time. Be sure your scheduler understands that it is UTC. Don't try to adjust it to the server's local time zone, or you might introduce error when the local time zone goes through daylight saving time transitions.

Make sure that not only does the scheduled job run at the correct time, but that the report query uses the correct time zone in determining the UTC range that it should run for.

Note that this does mean that two users from different time zones reporting over the same data may end up producing different results - because each of their "days" is for a slightly different period.