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

Panayiotis

I think what you have done so far is the best solution to update the already added fields. – Abacist

or you can use the defaults and everytime you have to add the columns to the table, you will have explicitly add the inserted and updated at fields. – Abacist

You should be aware that this functionality is deliberately not supported because MySQL does not support read after writes so if your ecto model and database can get out of sync during runtime. See José's comment here – Shack

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

Vinegarroon

It should be noted that if you are using MySQL these timestamps can be different from what is in the database and in your Model at runtime. See José's comment about read_after_writes – Shack

Answer 2

I ran into the same issue. For me, this was dued to not specifying timestamps() in the Ecto schema:

schema "my_table" do
    field(:name, :string)
    ...
    timestamps() // <- Add this here
end

The migration just tells your DB that you have timestamps columns. You still need to tell Ecto that they exist!

Remonstrance