How to set a default value for a datetime column to record creation time in a migration?

Question

Consider the table creation script below:

create_table :foo do |t|
  t.datetime :starts_at, :null => false
end

Is it's possible to set the default value as the current time?

I am trying to find a DB independent equivalent in rails for the SQL column definitions given below:

Oracle Syntax

start_at DATE DEFAULT SYSDATE() 

MySQL Syntax

start_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

OR

start_at DATETIME DEFAULT NOW()

Answer 1

This is supported now in Rails 5.

Here is a sample migration:

class CreatePosts < ActiveRecord::Migration[5.0]
  def change
    create_table :posts do |t|
      t.datetime :modified_at, default: -> { 'CURRENT_TIMESTAMP' }
      t.timestamps
    end
  end 
end

See discussion at https://github.com/rails/rails/issues/27077 and answer there by prathamesh-sonpatki

Answer 2

You can add a function in a model like this:

  before_create :set_foo_to_now
  def set_foo_to_now
    self.foo = Time.now
  end

So that the model will set the current time in the model.

You can also place some sql code in the migration for setting the default value at the database level, something like:

execute 'alter table foo alter column starts_at set default now()'

Setting something like this:

create_table :foo do |t|
  t.datetime :starts_at, :null => false, :default => Time.now
end

causes executing the Time.now function during migrating so then the table in database is created like this:

create table foo ( starts_at timestamp not null default '2009-01-01 00:00:00');

but I think that it is not what you want.

Answer 3

If you need to change an existing DateTime column in Rails 5 (rather than creating a new table as specified in other answers) so that it can take advantage of the default date capability, you can create a migration like this:

class MakeStartsAtDefaultDateForFoo < ActiveRecord::Migration[5.0]
  def change
    change_column :foos, :starts_at, :datetime, default: -> { 'CURRENT_TIMESTAMP' }
  end
end

Answer 4

Active Record automatically timestamps create and update operations if the table has fields named created_at/created_on or updated_at/updated_on. Source - api.rubyonrails.org

You don't need to do anything else except to have that column.

Answer 5

I was searching for a similar solutions but I ended using https://github.com/FooBarWidget/default_value_for.

The default_value_for plugin allows one to define default values for ActiveRecord models in a declarative manner. For example:

class User < ActiveRecord::Base
  default_value_for :name, "(no name)"
  default_value_for :last_seen do
    Time.now
  end
end

u = User.new
u.name       # => "(no name)"
u.last_seen  # => Mon Sep 22 17:28:38 +0200 2008

Answer 6

I usually do:

def change
  execute("
    ALTER TABLE your_table
    ALTER COLUMN your_column
    SET DEFAULT CURRENT_TIMESTAMP
  ")
end

So, your schema.rb is going to have something like:

create_table "your_table", force: :cascade do |t|
  t.datetime "your_column", default: "now()"
end

Answer 7

Did you know that upserts fail unless you have a default updated_at/created_at????

there is no migration flag which automatically does this, you have to manually include an options object with a default key

create_table :table_foos do |t|
  #...
  # date with timestamp
  t.datetime :last_something_at, null: false, default: -> { "CURRENT_TIMESTAMP" }
  
  # standard timestamps
  t.timestamps({default: -> { "CURRENT_TIMESTAMP" }})
end

Answer 8

In the answer given by @szymon-lipiński (Szymon Lipiński), the execute method didn't work for me. It was throwing a MySQL syntax error.

The MySQL syntax which worked for me is this.

execute "ALTER TABLE mytable CHANGE `column_name` `column_name` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"

So to set the default value for a datetime column in migration script can be done as follows:

def up
  create_table :foo do |t|
    t.datetime :starts_at, :null => false
  end

  execute "ALTER TABLE `foo` CHANGE `starts_at` `starts_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"
end