Change default_timezone of active record

Question

In rails, I have the below config for activerecord at first.

config.active_record.default_timezone = :utc

Now, I want to use the local timezone, so I changed it to:

config.active_record.default_timezone = :local

The problem is, I need to shift all the existing data in the date/datetime column to the local timezone. What is the best way to achieve this?

_{Why I have to do this is because I have to do aggregation on the local timezone, for example, :group => 'DATE(created_at)', GROUP BY DATE(created_at) will be based on the UTC, but I want to aggregate with one day in local timezone.

I knew how to write a migration file to migrate a certain datetime column. But there are a lot of such column, so I'm seeking for a better solution.}

Have you stored any information indicating which time zone a given user is in? As noted below, switching to a local time zone will set every value to the time zone of the server unless told otherwise. — Chris, May 01 '13 at 04:31
@Chris The users are limited in the same timezone same with the server. — xdazz, May 01 '13 at 04:33

score 3 · Accepted Answer · answered May 05 '13 at 16:30

This is dangerous, but here is what I'd do in the migration:

class MigrateDateTimesFromUTCToLocal < ActiveRecord::Migration

  def self.up
    # Eager load the application, in order to find all the models
    # Check your application.rb's load_paths is minimal and doesn't do anything adverse
    Rails.application.eager_load!

    # Now all the models are loaded. Let's loop through them
    # But first, Rails can have multiple models inheriting the same table
    # Let's get the unique tables
    uniq_models = ActiveRecord::Base.models.uniq_by{ |model| model.table_name }

    begin
      # Now let's loop
      uniq_models.each do |model|
        # Since we may be in the middle of many migrations,
        # Let's refresh the latest schema for that model
        model.reset_column_information

        # Filter only the date/time columns
        datetime_columns = model.columns.select{ |column| [ :datetime, :date, :time].include? column.type }

        # Process them
        # Remember *not* to loop through model.all.each, or something like that
        # Use plain SQL, since the migrations for many columns in that model may not have run yet
        datetime_columns.each do |column|
          execute <<-SQL
            UPDATE #{model.table_name} SET #{column.name} = /* DB-specific date/time conversion */
          SQL
        end

      rescue
        # Probably time to think about your rescue strategy
        # If you have tested the code properly in Test/Staging environments
        # Then it should run fine in Production
        # So if an exception happens, better re-throw it and handle it manually
      end

    end
  end
end

score 1 · Answer 2 · answered Apr 30 '13 at 17:53

My first advice is to strongly encourage you to not do this. You are opening yourself up to a world of hurt. That said, here is what you want:

class ShootMyFutureSelfInTheFootMigration
  def up
    Walrus.find_each do |walrus|
      married_at_utc = walrus.married_at
      walrus.update_column(:married_at, married_at_utc.in_time_zone)
    end
  end

  def down
    Walrus.find_each do |walrus|
      married_at_local = walrus.married_at
      walrus.update_column(:married_at, married_at_local.utc)
    end
  end
end

You may pass in your preferred timezone into DateTime#in_time_zone, like so:

central_time_zone = ActiveSupport::TimeZone.new("Central Time (US & Canada)")
walrus.update_column(:married_at, married_at_utc.in_time_zone(central_time_zone))

Or you can leave it and Rails will use your current timezone. Note that this isn't where you are, it is where your server is. So if you have users in Iceland and Shanghai, but your server is in California, every single 'local' time zone will be US Pacific Time.

Thanks for the reply. I added some information in the question. — xdazz, May 01 '13 at 00:32
If you want to do this is a Rails migration, update_column is as efficient as you're going to get. You can cut down on memory usage by only selecting the fields you intend to alter, but since you're going to run this once, time isn't a huge concern. — Chris, May 01 '13 at 04:32
But there are a lot of columns you have to list, I'm looking for some other solution which could detect all the datatime columns need to migrate. — xdazz, May 01 '13 at 04:39

score 0 · Answer 3 · edited May 23 '17 at 10:25

0

Must you change the data in the database? Can you instead display the dates in local time zone. Does this help: Convert UTC to local time in Rails 3

edited May 23 '17 at 10:25

Community

1
1

answered Feb 28 '13 at 02:14

Peter Degen-Portnoy

786
1
8
16

I have to do aggregation on the local timezone, for example, `:group => 'DATE(created_at)'`, `GROUP BY DATE(created_at)` will be based on the `UTC`, but I want to aggregate with one day in local timezone. – xdazz Feb 28 '13 at 02:21
What database are you using? Postgres has a series of timezone functions (http://www.postgresql.org/docs/9.2/static/functions-datetime.html, see section: 9.9.3. AT TIME ZONE). This will let you specify a time zone when performing your query. – Peter Degen-Portnoy Mar 04 '13 at 15:30

score 0 · Answer 4 · answered May 04 '13 at 17:29

Like a lot of other people said, you probably don't want to do this.

You can convert the time to a different zone before grouping, all in the database. For example, with postgres, converting to Mountain Standard Time:

SELECT COUNT(id), DATE(created_at AT TIME ZONE 'MST') AS created_at_in_mst
FROM users GROUP BY created_at_in_mst;

Change default_timezone of active record

4 Answers4