Peter Marklund

Peter Marklund's Home


Rails Tip: Migrate Your Database to UTC

UPDATE: Adam Meehan pointed out that my migration didn't work with DST, i.e. different UTC offsets for datetimes at different points of the year. I updated my migration to use a UTC conversion in the database (leading to a variable interval) instead of using a fixed interval adjustment.

If you want to make use of the timezone support in Rails 2.1 and later you'll need to migrate any existing times that you have in your db to UTC. Here is a migration for PostgreSQL I wrote to do that (you'll probably need to adjust it to work on MySQL):

# This migration will work with DST. Because of DST, if you have your datetimes
# spread across the year they will have different offset, i.e. in Stockholm we are UTC+1 usually
# but UTC+2 in the summer.
class MigrateDatabaseToUtc < ActiveRecord::Migration
  COLUMN_TYPES = [:datetime, :timestamp]

  def self.up

  def self.down
  def self.adjust(direction)
    connection = ActiveRecord::Base.connection
    connection.tables.each do |table|
      timestamp_columns = connection.columns(table).select do |column|

      sign = (direction == :down ? "+" : "-")
      update_clause = do |column|
        "#{} = (#{} #{sign} ((#{} AT TIME ZONE 'UTC')-#{}))"
      end.join(", ")

      execute "UPDATE #{table} SET #{update_clause}" unless update_clause.blank?

When I originally wrote this migration I used a fixed interval adjustment (+1 for Stockholm), i.e. the same approach that Simon Harris uses. However, as Adam Meehan points out in the comments this doesn't work with DST so I adjusted to using a AT TIME ZONE 'UTC' conversion instead that will result in a DST dependent interval. Thanks Adam for pointing this out!

7 comment(s)


wireframe01 said 2009-05-28 14:44:

I wrote this rake task to convert datetime and timestamp fields to UTC. I had migrated from rails 2.0.2 to rails 2.2.2 and wanted to stick with the UTC setting. Since this is in pure Ruby hopefully it takes care of all DST issues and such. desc "Reset all Datetime & Timestamp fields in database for UTC offset as per system timezone" task :reset_datetime_for_utc => :environment do _offset_secs = TZInfo::Timezone.get(`cat /etc/timezone`.chomp).period_for_local( _connection = ActiveRecord::Base.connection _connection.tables.each do |t| _time_stmp_columns = _connection.columns(t).select {|c| [:datetime, :timestamp].include?(c.type) } unless _time_stmp_columns.empty? puts "Processing #{t}" begin _model_klass = Kernel.eval(t.titlecase.gsub(/\s+/, '').singularize) rescue NameError => e puts "[WARN]: No class found for #{t} table (skipping): #{e}" next end _model_klass.find(:all).each do |e| _time_stmp_columns.each do |c| _old_value = e.send( unless _old_value.nil? _new_value = _old_value - _offset_secs.seconds e.send("#{}=", _new_value) end end e.save_with_validation(false) end end end end * Only works on UNIX * Is horribly slow. But then we don't do this everyday! ;-)

Simon Harris said 2009-03-12 01:42:


Adam Meehan said 2009-03-11 11:11:

No worries Peter. It was that this is a current problem for me and I have been thinking about the neatest way to do it. The 'AT TIME ON UTC' sounds like the perfect function. Unfortunately I have to do it an SQL Server 2000 database and no such function exists. To do it properly in T-SQL its about 50 lines!! It looks like I will have to do it in ruby to be safe. I will post what I come up with. It shouldn't be too hard. Adam

Peter Marklund said 2009-03-11 10:59:

Thanks Adam! I've changed the migration above to use a DST dependent variable interval adjustement rather than the fixed interval one that I used originally. I hope we have a working solution now!

Adam Meehan said 2009-03-11 09:07:

Thanks Peter. The problem would be however that despite utc_offset handling DST, if it is currently DST (where I am that is +1100) you would be applying that adjustment to *all* times on the database despite some of them being outside the DST period. So a time in June would be adjusted down 11 hours when actually it should be adjusted down 10 hours because it is outside the DST period. Also the reverse applies. You have great posts on your blog keep up the good work. Cheers, Adam.

Peter Marklund said 2009-03-11 08:50:

Adam, I verified that Time#utc_offset returns the correct offset now in March (+1 in Stockholm) as well as in July (+2 in Stockholm). So it does take DST into account. Peter

Adam Meehan said 2009-03-10 23:30:

Doesn't this ignore daylight savings time? I thought the only reliable way was to load the timestamp value as a Ruby object and adjust it to utc (or back) so that DST is taken into account. Also if you are currently in DST then the offset calculation would be wrong for all non-DST times.