Peter Marklund

Peter Marklund's Home

Tue March 10, 2009
Programming

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
    adjust(:up)
  end

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

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

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

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!

Comments

Adam Meehan said over 5 years ago:

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.

--------------------------------------------------------------------------------

Peter Marklund said over 5 years ago:

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 over 5 years ago:

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 over 5 years ago:

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 over 5 years ago:

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

--------------------------------------------------------------------------------

Simon Harris said over 5 years ago:

Nice!

--------------------------------------------------------------------------------

wireframe01 said over 4 years ago:

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(Time.now).utc_offset
_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(c.name)
unless _old_value.nil?
_new_value = _old_value - _offset_secs.seconds
e.send("#{c.name}=", _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! ;-)

--------------------------------------------------------------------------------