Store timestamps with timezone in rails 3.2

activerecord, postgresql, ruby, ruby-on-rails, ruby-on-rails-3

Solution

After banging my head against this same problem, I learned the sad truth of the matter:

Postgres does not support storing time zones in any of its date / time types.

So there is simply no way for you to store both a single moment in time and its time zone in one column. Before I propose an alternative solution, let me just back that up with the Postgres docs:

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

So there is no good way for you to simply "append" the timezone to the timestamp. But that's not so terrible, I promise! It just means you need another column.

My (rather simple) proposed solution:

- Store the timezone in a string column (gross, I know).

- Instead of overwriting to_s, just write a getter.

Assuming you need this on the `explodes_at` column:

def local_explodes_at
  explodes_at.in_time_zone(self.time_zone)
end

If you want to automatically store the time zone, overwrite your setter:

def explodes_at=(t)
  self.explodes_at = t
  self.time_zone = t.zone #Assumes that the time stamp has the correct offset already
end

In order to ensure that t.zone returns the right time zone, Time.zone needs to be set to the correct zone. You can easily vary `Time.zone` for each application, user, or object using an around filter (Railscast). There are lots of ways to do this, I just like Ryan Bates' approach, so implement it in a way that makes sense for your application.

And if you want to get fancy, and you need this getter on multiple columns, you could loop through all of your columns and define a method for each datetime:

YourModel.columns.each do |c|
  if c.type == :datetime
    define_method "local_#{c.name}" do
      self.send(c.name).in_time_zone(self.time_zone)                                                                                                                           
    end
  end
end

YourModel.first.local_created_at #=> Works.
YourModel.first.local_updated_at #=> This, too.
YourModel.first.local_explodes_at #=> Ooo la la

This does not include a setter method because you really would not want every single datetime column to be able to write to self.time_zone. You'll have to decide where this gets used. And if you want to get really fancy, you could implement this across all of your models by defining it within a module and importing it into each model.

module AwesomeDateTimeReader
  self.columns.each do |c|
    if c.type == :datetime
      define_method "local_#{c.name}" do
        self.send(c.name).in_time_zone(self.time_zone)                                                                                                                           
      end
    end
  end
end

class YourModel < ActiveRecord::Base
  include AwesomeDateTimeReader
  ...
end

Here's a related helpful answer: Ignoring timezones altogether in Rails and PostgreSQL

Hope this helps!

Problem

Original source

Related problems

• Ignoring time zones altogether in Rails and PostgreSQL