Read more

Your database tables should always have timestamps

Henning Koch
October 23, 2013Software engineer at makandra GmbH

Whenever you create a table from a database migration, remember to add updated_at and created_at timestamps to that table. Without those timestamps, investigating future bug reports will be hell. Always have timestamps.

Adding timestamps to new tables

Illustration online protection

Rails professionals since 2007

Our laser focus on a single technology has made us a leader in this space. Need help?

  • We build a solid first version of your product
  • We train your development team
  • We rescue your project in trouble
Read more Show archive.org snapshot

When you create a table using create_table, you can add timestamps by using the timestamps shortcut:

class CreateEpisode < ActiveRecord::Migration
  def change
    create_table :episodes do |t|
      t.string :name
      t.timestamps
    end
  end
end

ActiveRecord will automagically set those timestamps when you create or update a record.

Adding timestamps to existing tables

If you forgot to add timestamps in the past, take a look at your db/schema.rb and add timetamps for any table that is missing them. You can use the add_timestamps macros for that.

Rails >= 4.2

Since Rails 4.2 add_timestamps also adds a NOT NULL constraint by default. So you will see an error like "ERROR: column "created_at" contains null values" when you don't take care of it. Here are two approaches on how to handle this:

(a) Don't have a NOT NULL constraint:

class AddMissingTimestamps < ActiveRecord::Migration
  def change
    add_timestamps :shows, null: true
    add_timestamps :tags, null: true
    add_timestamps :users, null: true
  end
end

(b) Fill the datetimes with an old time (to mark it as being backfilled):

class AddMissingTimestamps < ActiveRecord::Migration
  def change
    # Add timestamps column, but without a NOT NULL constraint
    add_timestamps :shows, null: true

    # Backfill missing data with a really old date
    time = Time.zone.parse('2000-01-01 00:00:00')
    update "UPDATE shows SET created_at = '#{time}'"
    update "UPDATE shows SET updated_at = '#{time}'"

    # Restore NOT NULL constraints to be in line with the Rails default
    change_column_null :shows, :created_at, false
    change_column_null :shows, :updated_at, false
    
    # ...
  end
end

Rails < 4.2

class AddMissingTimestamps < ActiveRecord::Migration
  def change
    add_timestamps :shows
    add_timestamps :tags
    add_timestamps :users
  end
end

Also

Always have timestamps.

Henning Koch
October 23, 2013Software engineer at makandra GmbH
Posted by Henning Koch to makandra dev (2013-10-23 15:13)