Handling timezones in Rails applications

In the Rails application, we may have two different timezones.

  1. Application timezone
  2. Database timezone

In most of the cases database timezone is set to UTC, but application timezone could be different from database timezone.

How to set application timezone?

  1. Set timezone at the application level

We can set  config.time_zone configuration in config/application.rb file.
ActiveRecord fetches UTC from the database and converts it to the timezone specified in config.time_zone.

Refer this article in case you need help with setting up timezone at application level.

2. Set the timezone based on the one specified by the user

Take timezone from users in a form and save it in the users' table in the database.

create_table :users do |t|
  t.string :time_zone, default: "UTC"

We can set timezone by using around_action in ApplicationController for logged-in users.

# app/controllers/application_controller.rb

around_action :set_time_zone, if: :current_user


def set_time_zone(&block)
  Time.use_zone(current_user.time_zone, &block)

We pass the timezone of the current user to the use_zone method and this sets the timezone for that block.
When the request gets completed, the original timezone is set back.

Check out this article for more on setting user specific timezone.

Let's take a look at the example below to understand how ActiveRecord converts time into database timezone(assume UTC timezone) while querying on the database.

[1] pry(main)> Time.zone.name
=> "Eastern Time (US & Canada)"

[2] pry(main)> start_date = DateTime.new(2019, 10, 07).to_time_in_current_zone.beginning_of_day
=> Mon, 07 Oct 2019 00:00:00 EDT -04:00

[3] pry(main)> end_date = DateTime.new(2019, 10, 13).to_time_in_current_zone.end_of_day
=> Sun, 13 Oct 2019 23:59:59 EDT -04:00

[4] pry(main)> User.where(created_at: start_date..end_date).count
   (0.7ms)  SELECT COUNT(*) FROM `users` WHERE (`users`.`created_at` BETWEEN '2019-10-07 04:00:00' AND '2019-10-14 03:59:59')
=> 806

In the above example, the application timezone is set to EST.
If you observe the Select query generated by ActiveRecord, you see that it converted the start_date and end_date into UTC format for EST timezone.
Here, in EST timezone, the day starts at UTC 4 am on October 7th and ends at UTC 3:59 am on October 8th.

Timezone issue at the database level

Now, let's take an example where we want to group data by week(i.e. where the week starts on Monday and ends on Sunday). For the grouping of data, we can make use of the WEEK function of MySQL.

[5] pry(main)> start_date
=> Mon, 07 Oct 2019 00:00:00 EDT -04:00

[6] pry(main)> end_date
=> Sun, 13 Oct 2019 23:59:59 EDT -04:00

[7] pry(main)> User.where(created_at: start_date..end_date).group("WEEK(created_at, 5)").count
   (0.9ms)  SELECT COUNT(*) AS count_all, WEEK(created_at, 5) AS week_created_at_5 FROM `users` WHERE (`users`.`created_at` BETWEEN '2019-10-07 04:00:00' AND '2019-10-14 03:59:59') GROUP BY WEEK(created_at, 5)
=> {40=>788, 41=>18}

Here, the date range that we passed is of one week where the week started on Mon, 07 Oct 2019 and ended on Sun, 13 Oct 2019. We are fetching user records created in a week and grouping them by week.
As you can see in the result, we have got week_number => # users created in that week. We were fetching results for one week only, but still, we receive count for 2 weeks i.e. week numbers 40 and 41.

Why did this happen?

The database is saving dates in UTC format. When WEEK function gets applied, it considers dates in UTC format. In UTC timezone, week #40 ended on Sun, 13 Oct 2019 at 23:59:59 pm, but as per EST timezone, week #40 ends on Mon, 14 Oct 2019 at 03:59:59 am.
The WEEK functions get applied at database level on dates in UTC format and it considers the record created between Sun, 13 Oct 2019 at 24:00:00 pm to 14 Oct 2019 at 03:59:59 am in week #41 instead of week #40.
Due to this, we get a count for 2 weeks even though we fetched data for a week only.

How to solve this issue?

To fix this issue, we need to consider timezone at the database level as well. MySQL provides CONVERT_TZ function for converting dates from one timezone into another. CONVERT_TZ (date, from_tz, to_tz) takes 3 arguments i.e. date as 1st argument, from timezone as 2nd argument and to timezone as 3rd one.

In the rails application, we can create the below method for converting timezone at the database level.

UTC_OFFSET = '+00:00'

def mysql_datetime_in_current_tz(field: nil)
  return '' if field.blank?
  "CONVERT_TZ(#{field}, '#{UTC_OFFSET}', '#{Time.zone.formatted_offset}')"

mysql_datetime_in_current_tz(field: 'users.created_at') => "CONVERT_TZ(users.created_at, '+00:00', '-04:00')"

It takes field name as an argument. It also considers the timezone set in the application and fetches formatted_offset value. In case of an EST timezone, it will return -04:00.
We can use this method for converting timezone as below:

[8] pry(main)> users_created_at = mysql_datetime_in_current_tz(field: 'users.created_at')
=> "CONVERT_TZ(users.created_at, '+00:00', '-04:00')"

[9] pry(main)> User.where(created_at: start_date..end_date).group("WEEK(#{users_created_at}, 5)").count
   (1.4ms)  SELECT COUNT(*) AS count_all, WEEK(CONVERT_TZ(created_at,'+00:00','-4:00'), 5) AS week_convert_tz_created_at_00_00_4_00_5 FROM `users` WHERE (`users`.`created_at` BETWEEN '2019-10-07 04:00:00' AND '2019-10-14 03:59:59') GROUP BY WEEK(CONVERT_TZ(created_at,'+00:00','-4:00'), 5)
=> {40=>806}

As you can see above, it has returned data only for the first week by considering the timezone of the logged-in user.

Hope this article will help you identify and deal with timezone issues encountered while using MySQL functions. Please do share it with your peers.

Thanks for reading!

Useful Articles