After an amazing weekend battling with Rails, PostgreSQL and time zones, visiting a lot of references around the interwebs and trying different database and application configurations I finally came to a working setup, so I wanted to set things clear once and for all, and use it as a reference for my future self and any other that might find this information useful.

The problem

Say you need to store timestamps in your database and you are using Ruby on Rails as your development framework and PostgreSQL as the database.

In PostgreSQL, as it is known by everyone, exist two types for storing timestamps: with time zone and without. The difference, you may ask? Well, the latter stores just a date and a time with no reference to anything else, and the former add to the mix a reference to a geographical zone (the time zone), storing the value with reference to the zero time zone (UTC, GMT, +00 whatever).

It is best shown with an example. Say you have the timestamp “2014-10-06 12:00:00+02” which refers to today noon at time zone UTC+02 or Central Europe Summer Time. If you store this value inside your PostgreSQL database in a timestamp without time zone column, what you will have is just “2014-10-06 12:00:00” with the time zone information ignored. Everything is right, as long as your application is always used in the same place(or time zone).

Have a look now at the date when we store it in a timestamp with time zone column. What PostgreSQL really inserts is this value: “2014-10-06 10:00:00“, that is, the date shifted to UTC. Later, when you retrieve this value from the database, the value is presented in your timezone, which is defined by one configuration parameter in the database driver the client is using to connect.

You might ask, but why messing with time zones if I just want a date and time? Easy, say you are building a blog, and posts have a publication date and time. If you use a timestamp without time zone you would always get the same value from the database, wether you are seeing it from Hawaii or Japan, and it would be slightly strange to see a future publication date if you are in the former location (when it is 12:00:00 in central Europe in the summer time it is twelve hours less in Honolulu, so it’s 00:00:00).

Instead, it you used a timestamp with time zone, the date the user would see would be set to a concrete time zone, and if the user knows what that means (many times it is asking too much), she would be able to translate this time to her own time zone.

So it makes sense to use a type with time zone information, at least to not worry users with bizarre time travel issues.

The solution

Now that we have framed the problem we can observe we have two variables: the time zone on the application server and the time zone of the user connecting to the application. I don’t take into account the time zone of the server’s operating system because PostgresSQL doesn’t care at all about that, the only time zone it cares about is that of the client connection.

So, for Rails to know where it is in respect to time zones there is a configuration parameter in the file “config/application.rb“ called “config.time_zone” that you can set to your own time zone, or that of the server physical location. You can run “rake -D time” and get the list of available time zones for this parameter. If this parameter were not set, Rails would use UTC as the default time zone, and it is not a problem, unless you need to input dates and times from the user and store them into the database, in which case they would all be shifted to UTC when used in the application, whatever the user time zone is.

With this settings, all the timestamps would be set to the parameter set in the configuration, but the best for a user facing application is to show dates in the user time zone. So if you have registered users, you can have an additional parameter that specifies the user time zone, and you could use that parameter when working with dates and times. The way to use it is setting the user time zone in an around filter and then always manipulating dates and times with “Time.zone“, that gives all the usual methods of the class Time but translated to the user time zone.

around_filter :set_user_time_zone, :if => :the_user
def set_user_time_zone(&block) 
  Time.use_zone(the_user.time_zone, &block)
end

Then, using “Time.zone.now” would give the right time in the user time zone, and everything would be pink unicorns and rainbows.

The warning

ActiveRecord has another parameter for working with dates, times and time zones, and you should never set it if you are working inside a Ruby on Rails application and have “config.time_zone“ defined, because it would set an additional shift to your dates when they travel to the database, and you will lose several hours trying to know why all your dates are being stored in the wrong time zone (believe me, I’ve been there).

The parameter is “config.active_record.default_time_zone“, the default value is UTC, and it is intended for ActiveRecord only, so if you use Ruby on Rails, you should set it at the default value.