Adding timestamps to an existing table with Ecto's timestamps

Question

This has been asked here How to add timestamps to an existing table with Ecto's timestamps?, however the accepted solution means that every new entry will have the same default time. I would want the new entries to have the correct time of insert/update.

eg.

# set default to given date to fill in all existing rows with timestamps
def change do
  alter table(:my_table) do
   timestamps(default: "2018-01-01 00:00:01")
  end
end

If this is all that is in the migration, every inserted_at and updated_at for :my_table will have 2018-01-01 00:00:01 as the value, regardless of the date it was inserted/updated.

What I want to do is:

1. Add datetime to inserted_at & updated_at columns for pre-existing rows.
2. inserted_at and updated_at should be null: false as they are when adding timestamps to a newly created table.
3. Future entries should have the correct inserted_at and updated_at values i.e. inserted_at is the time the row was made, and updated_at is the time it was changed, instead of the default set in the migration.

I had a couple of solutions that do achieve this, but they seem quite messy. I am looking if there is a cleaner way to do this, or if there are options to handle this case which I am missing.

Working migration 1:

def up do
  alter table(:my_table) do
    timestamps(default: "now()")
  end
  execute("ALTER TABLE my_table ALTER COLUMN inserted_at SET DEFAULT now()")
  execute("ALTER TABLE my_table ALTER COLUMN updated_at SET DEFAULT now()")
end

def down do
  alter table(:my_table) do
    remove :inserted_at
    remove :updated_at
  end
end

Working migration 2:

def up do
  alter table(:my_table) do
    timestamps(null: true)
  end
  execute("UPDATE my_table SET inserted_at = now()")
  execute("UPDATE my_table SET updated_at = now()")
  alter table(:my_table) do
    modify :inserted_at, :naive_datetime, null: false
    modify :updated_at, :naive_datetime, null: false
  end
end

def down do
  alter table(:my_table) do
    remove :inserted_at
    remove :updated_at
  end
end

Answer 1

You can provide an SQL function as the default value using fragment. The documentation provides the following example:

create table("posts") do
  add :inserted_at, :naive_datetime, default: fragment("now()")
end

timestamps seems to forward the default: option to add, so in your specific case you should be able to do the following:

def change do
  alter table(:my_table) do
    timestamps(default: fragment("now()"))
  end
end