How to add timestamps to an existing table with Ecto's timestamps?

Question

Since inserted_at and updated_at can not be null this won't work:

def change do
  alter table(:channels) do
    timestamps
  end
end

** (Postgrex.Error) ERROR (not_null_violation): column "inserted_at" contains null values

Is there an easy way to accomplish this without copying timestamps' functionality?

Rhododendron

Answer 1

The timestamps/1 function accepts an options keyword list, you can set the default value with it.

def change do
  alter table(:channels) do
    timestamps default: "2016-01-01 00:00:01", null: false
  end
end

UPDATE Ecto >= 2.1
You'll need to use the new type NaiveDateTime

def change do
  alter table(:channels) do
    timestamps default: ~N[2017-01-01 00:00:01], null: false
  end
end

If you have more doubts take a look at the documentation

Th

Ecto 2.0 has removed this option :( Probably the best solution is to add both fields manually. – Jaworski

The original solution worked in Ecto 3.2 just now :) ... timestamps default: "2016-01-01 00:00:01", null: false -- that's the original un-updated answer. – Evangelistic

The problem I found with this is that all future inserted_at and updated_at values are that date exactly. Ideally it would set a value for pre-existing rows & for new rows/updates, it would use the time of update. I asked a separate question here for this case. – Braziel

This seems foolish? If you don't set a timestamp field it will default to that static date? That is surely going to wreak havoc somewhere along the line. Much better to take the long road and manually set dates as necessary during the migration and absolutely do not set a default static date on timestamp fields which are supposed to track activity. – Hohenzollern

Answer 2

I used following migration to add timestamps to existing table and fill them with current time:

defmodule MyApp.AddTimestampsToChannels do
  use Ecto.Migration

  def up do
    alter table(:channels) do
      timestamps null: true
    end

    execute """
    UPDATE channels
    SET updated_at=NOW(), inserted_at=NOW()
    """

    alter table(:channels) do
      modify :inserted_at, :utc_datetime, null: false
      modify :updated_at, :utc_datetime, null: false
    end
  end

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

And there are other ways to do it. For example, if you have some related table, you can borrow initial timestamps from it:

execute """
UPDATE channels
SET inserted_at=u.inserted_at,
    updated_at=u.updated_at
FROM
  (SELECT id,
          inserted_at,
          updated_at
   FROM accounts) AS u
WHERE u.id=channels.user_id;
"""

Heymann

Answer 3

I guess you get this when you are trying to update a record, I can think 2 possible solutions you could either touch the inserted_at column in your table by running a UPDATE query or adding a function to your ecto model like so

  def create_changeset(model, attrs) do
    model
    |> cast(attrs, @required_fields, @optional_fields)
    |> update_inserted_at
  end

  defp update_inserted_at(changeset) do
    # check if the updated field is null set a new date
  end

Intrados