Add timestamps to an existing table

Question

I need to add timestamps (created_at & updated_at) to an existing table. I tried the following code but it didn't work.

class AddTimestampsToUser < ActiveRecord::Migration
    def change_table
        add_timestamps(:users)
    end
end

Desmarais

Answer 1

The timestamp helper is only available in the create_table block. You can add these columns by specifying the column types manually:

class AddTimestampsToUser < ActiveRecord::Migration
  def change_table
    add_column :users, :created_at, :datetime, null: false
    add_column :users, :updated_at, :datetime, null: false
  end
end

While this does not have the same terse syntax as the add_timestamps method you have specified above, Rails will still treat these columns as timestamp columns, and update the values normally.

Difference

This did not work for me in Rails 4. The below solution by "mu is too short" is working. – Seigniorage

rails g migration AddTimestampsToUser created_at:datetime updated_at:datetime - a shortcut to generate the migration above. – Gujarati

Running this migration leads to error PG::NotNullViolation: ERROR: column "created_at" contains null value because my table already contains data which violates not null constraint. Any better way of doing this than removing the not null contraint at first and then adding it later ? – Lionel

@M.Habib I don't think so, but this answer encapsulates it all in one migration nicely. – Missioner

@M.Habib depends on what you think makes the most sense for the default value, you could do add_column :users, :updated_at, :datetime, null: false, default: Time.zone.now. Time.zone.now is just an example, you should use whatever value makes sense for your logic. – Paisano

It didn't worked for me on Rails 6... I used instead: def change \n change_table :users do |t| \n t.timestamps \n end \n end – Rosewater

Answer 2

Migrations are just two class methods (or instance methods in 3.1): up and down (and sometimes a change instance method in 3.1). You want your changes to go into the up method:

class AddTimestampsToUser < ActiveRecord::Migration
  def self.up # Or `def up` in 3.1
    change_table :users do |t|
      t.timestamps
    end
  end
  def self.down # Or `def down` in 3.1
    remove_column :users, :created_at
    remove_column :users, :updated_at
  end
end

If you're in 3.1 then you could also use change (thanks Dave):

class AddTimestampsToUser < ActiveRecord::Migration
  def change
    change_table(:users) { |t| t.timestamps }
  end
end

Perhaps you're confusing def change, def change_table, and change_table.

See the migration guide for further details.

Albino

(Well, there's the change method now, although in this case, not the issue :) – Lithiasis

@Dave: True enough, I went for generic to avoid the version issues but change is worth a mention so I'll add that too. – Albino

True mu but I have heard that that is really changing with 3.1 and the 'down' is really going away. Rails to figure out the down method automatically. Have you heard about that? – Emendation

@Michael: I've been using MongoDB exclusively with the 3.1 app I'm working on so I haven't worked with 3.1 AR migrations. The docs indicate that everything is moving towards instance methods (for unknown reasons). – Albino

@MichaelDurrant, there are many scenarios that "change" doesn't cover right now, if up/down go away there will be some angry people :) (add an "unless" clause in your change migration to avoid migration collisions, and try rolling that back...) Even 3 years after you made this comment, I don't think it's changing. :) – Germann

@RichPeck: Wow, how did you find the code under all that dust? – Albino

Answer 3

@user1899434's response picked up on the fact that an "existing" table here could mean a table with records already in it, records that you might not want to drop. So when you add timestamps with null: false, which is the default and often desirable, those existing records are all invalid.

But I think that answer can be improved upon, by combining the two steps into one migration, as well as using the more semantic add_timestamps method:

def change
  add_timestamps :projects, default: Time.zone.now
  change_column_default :projects, :created_at, nil
  change_column_default :projects, :updated_at, nil
end

You could substitute some other timestamp for DateTime.now, like if you wanted preexisting records to be created/updated at the dawn of time instead.

Stitt

Amazing. Thank you! Just one note - Time.zone.now is what should be used if we want our code to obey the correct time zone. – Precept

There is a problem with setting the default to Time.zone.now which is that it will return the Time instance that is created when the migration is run and just use that time as the default. New objects wont get a new Time instance. – Lerma

This answer should be on the top! – Postilion

Answer 4

Your original code is very close to right, you just need to use a different method name. If you're using Rails 3.1 or later, you need to define a change method instead of change_table:

class AddTimestampsToUser < ActiveRecord::Migration
  def change
    add_timestamps(:users)
  end
end

If you're using an older version you need to define up and down methods instead of change_table:

class AddTimestampsToUser < ActiveRecord::Migration
  def up
    add_timestamps(:users)
  end

  def down
    remove_timestamps(:users)
  end
end

Presa

That should be the accepted answer – Howlan

Answer 5

class AddTimestampsToUser < ActiveRecord::Migration
  def change
    change_table :users do |t|
      t.timestamps
    end
  end
end

Available transformations are

change_table :table do |t|
  t.column
  t.index
  t.timestamps
  t.change
  t.change_default
  t.rename
  t.references
  t.belongs_to
  t.string
  t.text
  t.integer
  t.float
  t.decimal
  t.datetime
  t.timestamp
  t.time
  t.date
  t.binary
  t.boolean
  t.remove
  t.remove_references
  t.remove_belongs_to
  t.remove_index
  t.remove_timestamps
end

http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Table.html

Trumaine

Answer 6

Nick Davies answer is the most complete in terms of adding timestamp columns to a table with existing data. Its only downside is that it will raise ActiveRecord::IrreversibleMigration on a db:rollback.

It should be modified like so to work in both directions:

def change
  add_timestamps :campaigns, default: DateTime.now
  change_column_default :campaigns, :created_at, from: DateTime.now, to: nil
  change_column_default :campaigns, :updated_at, from: DateTime.now, to: nil
end

Misericord

This didn't work exactly as written for me on Rails 4.2.7 (I think change_column_default doesn't support from and to in that version?), but I took this idea and created up/down methods instead of a single change method and it worked like a charm! – Calamanco

Answer 7

The issue with most of the answers here is that if you default to Time.zone.now all records will have the time that the migration was run as their default time, which is probably not what you want. In rails 5 you can instead use now(). This will set the timestamps for existing records as the time the migration was run, and as the start time of the commit transaction for newly inserted records.

class AddTimestampsToUsers < ActiveRecord::Migration def change add_timestamps :users, default: -> { 'now()' }, null: false end end

Midget

You probably don't want the timestamps for existing objects to be set to the time the migration is run. This would lead to misleading data/analytics. If the data isn't available from another source, then leaving it null is a better option. – Justle

@Justle you have a point. I think the decision to set time stamps for existing records just depends on your needs. Leaving them null might cause issues if you also add constraints on them. It also might make sense to set them to a date far in the past to signify that they were involved in this migration. Ideally you wouldn't get into a position where you are adding timestamps to a table after the tables creation. – Midget

Answer 8

Using Time.current is a good style https://github.com/rubocop-hq/rails-style-guide#timenow

def change
  change_table :users do |t|
    t.timestamps default: Time.current
    t.change_default :created_at, from: Time.current, to: nil
    t.change_default :updated_at, from: Time.current, to: nil
  end
end

or

def change
  add_timestamps :users, default: Time.current
  change_column_default :users, :created_at, from: Time.current, to: nil
  change_column_default :users, :updated_at, from: Time.current, to: nil
end

Prunella

This worked for me in Rails 6! – Millwright

Answer 9

def change
  add_timestamps :table_name
end

Zaid

PG::NotNullViolation: ERROR: column "created_at" contains null values – Grilled

Answer 10

I'm on rails 5.0 and none of these options worked.

The only thing that worked was using the type to be :timestamp and not :datetime

def change
    add_column :users, :created_at, :timestamp
    add_column :users, :updated_at, :timestamp
end

Lambrequin

This is the answer I needed. Rspec tests were failing for me with datetime specified as the type (they wouldnt set the created_at by default) – Trophoplasm

Answer 11

This seems like a clean solution in Rails 5.0.7 (discovered the change_column_null method):

def change
  add_timestamps :candidate_offices, default: nil, null: true
  change_column_null(:candidate_offices, :created_at, false, Time.zone.now)
  change_column_null(:candidate_offices, :updated_at, false, Time.zone.now)
end

Shipyard

Answer 12

not sure when exactly this was introduced, but in rails 5.2.1 you can do this:

class AddTimestampsToMyTable < ActiveRecord::Migration[5.2]
  def change
    add_timestamps :my_table
  end
end

for more see "using the change method" in the active record migrations docs.

Malleolus

I didn't make it work with Migration[5.1]; then I've changed the number to [5.2] and Rails told me that I could only use 5.1, 5.0 or 4.2. I've try with 5.0 with no success, then in 4.2 with success. – Greer

Old, I know, but if you have existing records add: , null: true after the :my_table – Longlimbed

Answer 13

A lot of answers here, but I'll post mine too because none of the previous ones really worked for me :)

As some have noted, #add_timestamps unfortunately adds the null: false restriction, which will cause old rows to be invalid because they don't have these values populated. Most answers here suggest that we set some default value (Time.zone.now), but I wouldn't like to do that because these default timestamps for old data will not be correct. I don't see the value in adding incorrect data to the table.

So my migration was simply:

class AddTimestampsToUser < ActiveRecord::Migration
  def change_table
    add_column :projects, :created_at, :datetime
    add_column :projects, :updated_at, :datetime
  end
end

No null: false, no other restrictions. Old rows will continue being valid with created_at as NULL, and update_at as NULL (until some update is performed to the row). New rows will have created_at and updated_at populated as expected.

Ferullo

Answer 14

In rails 6 (and possibly earlier) if you try to add timestamps to an existing table with records already present like this:

def change
  add_timestamps :table_name
end

you will get an error owing to the fact that add_timestamps by default declares the new colums as NOT NULL. You can work around this simply by adding null: true as an argument:

def change
  add_timestamps :table_name, null: true
end

Angell

Answer 15

I made a simple function that you can call to add to each table (assuming you have a existing database) the created_at and updated_at fields:

  # add created_at and updated_at to each table found.
  def add_datetime
    tables = ActiveRecord::Base.connection.tables
    tables.each do |t|
      ActiveRecord::Base.connection.add_timestamps t  
    end    
  end

Scharf

Answer 16

add_timestamps(table_name, options = {}) public

Adds timestamps (created_at and updated_at) columns to table_name. Additional options (like null: false) are forwarded to #add_column.

class AddTimestampsToUsers < ActiveRecord::Migration
  def change
    add_timestamps(:users, null: false)
  end
end

Sari

Answer 17

This is a simple one to add timestamp in existing table.

class AddTimeStampToCustomFieldMeatadata < ActiveRecord::Migration
  def change
    add_timestamps :custom_field_metadata
  end
end

Humic

Answer 18

The answers before seem right however I faced issues if my table already has entries.

I would get 'ERROR: column created_at contains null values'.

To fix, I used:

def up
  add_column :projects, :created_at, :datetime, default: nil, null: false
  add_column :projects, :updated_at, :datetime, default: nil, null: false
end

I then used the gem migration_data to add the time for current projects on the migration such as:

def data
  Project.update_all created_at: Time.now
end

Then all projects created after this migration will be correctly updated. Make sure the server is restarted too so that Rails ActiveRecord starts tracking the timestamps on the record.

Electrocardiograph

Answer 19

You can use a migration like this to add a created_at and updated_at columns to an existing table with existing records. This migration sets the created_at and updated_at fields of existing records to the current date time.

For the sake of this example say the table name is users and the model name is User

class AddTimestampsToTcmOrders < ActiveRecord::Migration[6.0]
  def up
    # Add timestamps to the users table with null as true cause there are existing records
    add_timestamps(:users, null: true)

    # Update existing records with non-nil timestamp values
    User.update_all(created_at: DateTime.now, updated_at: DateTime.now)

    # change columns so they can't be nil
    change_column(:users, :updated_at, :datetime, null: false, precision: 6)
    change_column(:users, :created_at, :datetime, null: false, precision: 6)
  end

  def down
    remove_column :users, :updated_at
    remove_column :users, :created_at
  end
end

Vicereine

Answer 20

I am using Rails 6.0.2.1 & Ruby 2.6.5. I tried many solutions but issue I was facing was, if default time set, the time when migration is run, was being set to default. So when creating new record, same time was set in timestamps. If no default given, it was throwing error for null value present error for already present records. Finally searched chatgpt and found below solution and it worked for me.

class AddTimestampsToTableName < ActiveRecord::Migration[6.0] 
  def change
    add_timestamps :table_name, null: false, default: -> { 'CURRENT_TIMESTAMP' }
  end
end

Aria

Answer 21

For those who don't use Rails but do use activerecord, the following also adds a column to an existing model, example is for an integer field.

ActiveRecord::Schema.define do
  change_table 'MYTABLE' do |table|
    add_column(:mytable, :my_field_name, :integer)
  end
end

Foulmouthed

Answer 22

It's change, not change_table for Rails 4.2:

class AddTimestampsToUsers < ActiveRecord::Migration
  def change
    add_timestamps(:users)
  end
end

Chimb

Answer 23

I personally used the following, and it updated all previous records with the current time/date:

add_column :<table>, :created_at, :datetime, default: Time.zone.now, null: false
add_column :<table>, :updated_at, :datetime, default: Time.zone.now, null: false

Sorus

Answer 24

I ran into the same issue on Rails 5 trying to use

change_table :my_table do |t|
    t.timestamps
end

I was able to add the timestamp columns manually with the following:

change_table :my_table do |t|
    t.datetime :created_at, null: false, default: DateTime.now
    t.datetime :updated_at, null: false, default: DateTime.now
end

Savitt

won't this always set the default value with the time at the moment the migration was run ? (so not really a dynamic timestamp handled by the DB) – Ferrell

for the records that already exist in your db, yes, it will set the created_at and updated_at to the datetime the migration was run. Without having those values beforehand though, idk how else you'd initialize those values. EDIT: It would just be considered the beginning of that row's history – Savitt