RAILS 4 + POSTGRESQL + TIMEZONE BEST PRACTICE

Suppose we have a Rails application and we are using Postgresql as a database. There is always a question of what is the best way to deal with the time zones in this situation. In most cases that won't be of any importance, but if you have users from around the world and they need to know the exact point in time when something happened (banking transactions), then it is extremely important that you have time zones properly set in the application and on the server.

Prerequisite

Let's start with a simple create script that we want to execute on the database server (you could use migrations in Rails, but I want to have a full control over the database so I like it this way):

 
 CREATE EXTENSION "uuid-ossp"; 

 CREATE TABLE TESTINGS ( 
   ID                                UUID PRIMARY KEY DEFAULT uuid_generate_v4(), 
   NAME                              VARCHAR(500) NOT NULL, 
   DESCRIPTION                       VARCHAR(500), 
   TESTING_TIME_1_AT                 TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), 
   TESTING_TIME_2_AT                 TIMESTAMP WITH TIME ZONE 
 ); 
 

Here we have two columns of type TIMESTAMP, you should always put your timestamp columns to be WITH TIME ZONE, there's no need to store time without time zone because you won't gain anything with it, but you could lose a lot even you don't need time zones. After you execute this script, you should create a model in your Rails application according to the table (testing.rb). Now let's begin to play with it.

Timezone on postgresql server

I suppose that you already installed your Postgresql server. If so, first you should check which time zone is set on the server with this command:

 
 show timezone; 
 

If the server isn't set to UTC time zone, you should do that by going to the file postgresql.conf. Locate timezone variable and change it to UTC (timezone = 'UTC'). Save the file, restart your server and check again with that command that the time zone is properly changed. By the way, this file is located under the path '\PostgreSQL\9.4\data' (if you have 9.4 version installed).

If, for some reason, you wish to configure different time zone on your server, be sure to set that same time zone inside your Rails application.

Timezone in rails application

In your Rails application, if you open file 'config/application.rb', you could see that the default time zone for the application is set to UTC, and you should leave it that way. But if you set different time zone on your Postgresql server, than you should set that same time zone in here. You can use this command to see which time zones are available in Rails:

 
 $ rake time:zones:all 
 

And this you can use to see the time zone which is set on your system, not in the application:

 
 $ rake time:zones:local 
 

Handling timezones

If you start your Rails console and type this:

 
 $ test1 = Testing.create(name: 'Test1') 
 

In your database you will create a data which will have for TESTING_TIME_1_AT current time in the UTC format, because previously you set your database to UTC and also you specified default value for this column. And if you do this:

 
 $ test2 = Testing.create(name: 'Test2', testing_time_2_at: '2015-03-13 2pm') 
 

You will create a data with TESTING_TIME_2_AT also set in the UTC format. But if you do this:

 
 $ Time.zone = ActiveSupport::TimeZone.new('Belgrade') 
 $ test3 = Testing.create(name: 'Test3', testing_time_2_at: '2015-03-13 2pm') 
 

You will change time zone in your Rails application so TESTING_TIME_2_AT will be set to time you entered but converted into UTC, because of the database, therefore '2015-03-13 13:00:00+00' if you do SELECT on the table.

Before we continue, one thing is important to notice. When one specific event occurs, time of that event is absolute, not relative, it's exactly one point in time. So if you have that point in time you can convert that time across different time zones. That's why it is important to store time zones in your database. If you don't do that, you won't have correct data when that event occurred because without time zone specified, that event might occur anytime during that day. If you have time in your database set to '2015-03-13 2pm' for example, you won't know what is the absolute time of that event regardless that hour of the day is entered. You won't know if that is London's time or Moscow's time...

You should try now to execute commands Time.now and Time.zone.now and you'll probably find yourself even more wondering what the hell is happening here. This is the explanation:

Time.now - gives you the time in system time zone, this is provided by Ruby and it doesn't know anything about our timezone configuration we just set.
Time.zone.now - is provided by Rails and gives you Time.zone value that we set in Rails.

Now type this:

 
 $ test4 = Testing.create(name: 'Test4', testing_time_2_at: Time.now) 
 $ test5 = Testing.create(name: 'Test5', testing_time_2_at: Time.zone.now) 
 

You might be surprised that inside your database you will get the same time for TESTING_TIME_2_AT in these two cases, not different one like you might think. This is fun, isn't it :) The catch here is that we set UTC time inside of the database and there is only one correct time IN THIS CURRENT MOMENT, not two, regardless which NOW method you use.

Since you are probably not in Honolulu, let's do this:

 
 $ Time.zone = ActiveSupport::TimeZone.new('Pacific/Honolulu') 
 $ test6 = Testing.create(name: 'Test6', testing_time_2_at: Time.zone.parse('2015-03-14 8pm')) 
 

Now when you select your data from the database, you'll get '2015-03-15 06:00:00+00' for TESTING_TIME_2_AT. That means that you inserted data with time zone from Honolulu and Postgresql converted it into UTC time which is 10 hours behind the UTC time zone. So when it's 8 pm in Honolulu on 14th of March, in London it will be 6 am on 15th of March, so we got everything right, we have exactly one point in time correctly inserted into our database.

Let's try something more here:

test6.testing_time_2_at = 'Sat, 14 Mar 2015 20:00:00 HST -10:00' - this gives time in time zone which is set in the Rails application.
test6.testing_time_2_at.localtime = '2015-03-15 07:00:00 +0100' - this gives time in local time zone, I am in Belgrade.
test6.testing_time_2_at.in_time_zone("Eastern Time (US & Canada)") = 'Sun, 15 Mar 2015 02:00:00 EDT -04:00' - you can convert time from database into any time zone you wish.

Wrap up

1) If you need to know a point in time when some event occurred then it isn't relevant what type of time zone you forward to the database because the database is set to the UTC time zone and it will always store time in UTC format, therefore you will have infallible data about time. The example is when you create a record in the database and time zone isn't important to the user of your application, you can forward Time.now or Time.zone.now to the database, it doesn't matter.

2) If it is important to store local time when user did some action to show it later to her, again it isn't important which time zone you forward to the database because in the database there will be set exact UTC time of that event. But when you need to show that time to the user, you need to convert it into local time for that user (for example, the time when some banking transaction occurred).

3) If user chooses a time inside of the application (for example you are building an application which sends some sort of notification to the users at the moment they defined), this is the case you need to take care of local time. Because if you don't do that, and user chooses time '2143-07-13 2pm', one day that distant year 2143 the user will receive the notification in the London timezone instead of the time that is valid in Honolulu. For this reason you need to forward to the database Time.zone.parse('2143-07-13 2pm'). It will be converted to UTC format and when you display that data to the user, you will manually convert that time into local time.

4) If you need to work on the procedures in the database, you'll always work with UTC time since every data is stored in UTC time zone, so no need to worry about converting into different time zones.

How to know the time zone of the user

One more thing you have to do to solve all problems with time zone settings is how to display that time for the user who uses your application. Since the time in your database is always in UTC format and your users can be anywhere in the world, you have to know the time zone of the person who uses your application. You can do that in two ways:

1) On the client side, using JavaScript or installing a gem that does that for you. Personally I don't like this way.

2) On the server side, take the user's location and based on that you can find the time zone. You can use gems like geocoder and timezone. Here you can have a problem if someone uses your application through a proxy, but these are borderline cases that doesn't happen often. Though if that can happen to you, in this case it is better to use client side approach with JavaScript because that data will be infallible (except if the time on that computer is incorrectly set).

Of course, you can always ask the user in a form to choose the time zone to which she belongs, so this information could be stored in the database as a user preference but that way the application loses quality because you ask a user something that you don't need to ask. This information can be obtained in a different way and thus free your user of unnecessary entries which drastically improves UX of your application.

The end

Certainly there are other ways how you can deal with time zones in your application, but I personally prefer to do it this way. However, if you work in this way or another, do not forget that the convention for working with time zones is very important when you build any application. You, as a developer, need to think of every single detail in the architecture of your system.