Store timestamps with timezone in rails 3.2

Question

I'm trying to store all timestamps in a rails application with their included timezone. I'm fine with ActiveRecord converting them to utc, but I have multiple applications hitting the same database, some of which are implemented with a timezone requirement. So what I want to do is get activerecord to convert my timestamps as usual, then write them to the database with the string 'America/Los_Angeles', or whatever appropriate timezone, appended to the timestamp. I am currently running rails 3.2.13 on jruby 1.7.8, which implements the ruby 1.9.3 api. My database is postgres 9.2.4, connected with the activerecord-jdbcpostgresql-adapter gem. The column type is timestamp with time zone.

I have already changed the natural activerecord mappings with the activerecord-native_db_types_override gem, by adding the following lines to my environment.rb:

  NativeDbTypesOverride.configure({
    postgres: {
      datetime: { name: "timestamp with time zone" },
      timestamp: { name: "timestamp with time zone" }
    }
  })

My application.rb currently contains

  config.active_record.default_timezone = :utc
  config.time_zone = "Pacific Time (US & Canada)"

I suspect I can rewrite ActiveSupport::TimeWithZone.to_s and change it's :db format to output the proper string, but I haven't been able to make that work just yet. Any help is much appreciated.

Answer 1

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:

1. Store the timezone in a string column (gross, I know).
2. 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!

Answer 2

May i suggest saving them in iso8601

That will allow you to:

• Have the option of storing them as UTC as well as with a timezone offset

• Being international standards compliant

• Use the same storage format in both cases with offset and without.

So one of the db columns can be with a offset one in just UTC form (usual).

From the Ruby side it is as simple as

Time.now.iso8601
Time.now.utc.iso8601

ActiveRecord should work seamlessly with the conversion.

Also, most API's use this format (google) hence best for cross app compatibility.

to_char() for postgresql should give you the right format in case there is any hiccup with the default setup.

Answer 3

One approach, as you suggest, would be to override ActiveSupport::TimeWithZone.to_s

You might try something like this:

def to_s(format = :default)
  if format == :db
    time_with_timezone_format
  elsif formatter = ::Time::DATE_FORMATS[format]
    formatter.respond_to?(:call) ? formatter.call(self).to_s : strftime(formatter)
  else
    time_with_timezone_format
  end
end

private

def time_with_timezone_format
  "#{time.strftime("%Y-%m-%d %H:%M:%S")} #{formatted_offset(false, 'UTC')}" # mimicking Ruby 1.9 Time#to_s format
end

I haven't tested this but looking at Postgres' docs on Time Stamps, this looks valid and would give a time like: "2013-12-26 10:41:50 +0000"

The problem with this, as far as I can tell, is that you would still have trouble returning the right timezone:

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone.

This is exactly what the original ActiveSupport::TimeWithZone.to_s is already doing.

So perhaps the best way to get the correct Time Zone is to set an explicit Time Zone value as a new column in the database.

This would mean that you would be able to keep the native date functionality of both Postgres and Rails while also being able to display the time in the correct timezone where necessary.

You could use this new column to then display the right zone using Ruby's Time::getlocal or Rails' ActiveSupport::TimeWithZone.in_time_zone.