使用Ecto的时间戳将时间戳添加到现有表中 [英] Adding timestamps to an existing table with Ecto's timestamps

问题描述

在此处已被问到如何将时间戳添加到具有Ecto时间戳的现有表中?，但是公认的解决方案意味着每个新条目都将具有相同的默认时间.我希望新条目具有正确的插入/更新时间.

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.

例如

# 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

如果这是迁移中的全部内容，:my_table的每个 inserted_at和updated_at的值将为2018-01-01 00:00:01，无论插入/更新的日期.

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.

我想做的是:

1. 将日期时间添加到insert_at&预先存在的行的updated_at列.
将时间戳添加到新创建的表时，
2. inserted_at和updated_at应该为null: false.
3. 将来的条目应具有正确的insert_at和updated_at值，即，insert_at是创建行的时间，而updated_at是更改时间的时间，而不是迁移中的默认设置.

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.

工作迁移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

工作迁移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

推荐答案

我遇到了同样的问题.对我来说，这是由于未在Ecto模式中指定timestamps():

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

迁移仅告诉您的数据库您有时间戳记列.您仍然需要告诉Ecto它们存在！

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

这篇关于使用Ecto的时间戳将时间戳添加到现有表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！