postgresql - 如何在 Ecto 模式中设置 `DateTime` 和在迁移中设置 `timestamp with time zone` (`timestamptz` ) PostgreSQL 类型？

Question

想在 Ecto 模式和迁移中使用 DateTime，而不是默认的 NaiveDateTime，也在 PostgreSQL 中使用 timestamptz，而不是默认 timestamp(又名。timestamp without time zone)。

Answer 1

ECTO 迁移:切换到 timestamptz 和 :utc_datetime

注意:Ecto.Migration.timestamps/1 ( source ) 全局配置总是可以在本地覆盖。

1。全局配置

使用 Ecto.Migration docs 中的 :migration_timestamps 配置选项:

# in ./config/dev.exs (for example)

config :app, App.Repo, migration_timestamps: [type: :timestamptz]

并且可以像往常一样在迁移中使用 Ecto.Migration.timestamps/1:

# ./priv/repo/migrations/20190718195828_create_users.exs

create table(:users) do
  add :username, :string, null: false

  timestamps()
end

Postgres 适配器会自动切换从 Elixir 到 DateTime 的表示NaiveDateTime。

2。本地配置

使用Ecto.Migration.timestamps/1的 :type 选项:

defmodule App.Repo.Migrations.CreateUsers do

  use Ecto.Migration

  def change do
    create table(:users) do
      add :username, :string, null: false

      timestamps(type: :timestamptz)
    end
  end
end

ECTO SCHEMAS:切换到:utc_datetime

1。全局配置

Ecto 模式也需要修改为使用 :utc_datetime，否则他们会期望NaiveDateTime 默认情况下。稍微修改一下中的示例 Ecto.Schema docs :

# Define a module to be used as base
defmodule MyApp.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema

      # In case one uses UUIDs
      @primary_key {:id, :binary_id, autogenerate: true}
      @foreign_key_type :binary_id

      # ------------------------------------
      @timestamps_opts [type: :utc_datetime]

    end
  end
end

# Now use MyApp.Schema to define new schemas
defmodule MyApp.Comment do
  use MyApp.Schema

  schema "comments" do
    belongs_to :post, MyApp.Post

    timestamps()
  end
end

2。本地配置

defmodule ANV.Accounts.User do

  use Ecto.Schema

  # -- EITHER --------------------------
  @timestamps_opts [type: :utc_datetime]

  schema "users" do

    field :username, :string

    # -- OR -----------------------
    timestamps(type: :utc_datetime)
  end

资源

• Difference in between :utc_datetime and :naive_datetime in Ecto

---

• lau/tzdata

  DateTime在 Elixir 中“仅处理“Etc/UTC”datetimes”，但可以使用自定义配置时区数据库，也就是tzdata图书馆是

---

• Time zones in PostgreSQL, Elixir and Phoenix和 How to set timestamps to UTC DateTimes in Ecto

  第一篇文章中的一个非常方便的表格:

+----------------------+------------------+------------------------+------------------------------+-----------------------------------+
|    Ecto 3 type       |    Elixir type   | Supports microseconds? | Supports DateTime functions? | Supports NaiveDateTime functions? |
+----------------------+------------------+------------------------+------------------------------+-----------------------------------+
| :utc_datetime_usec   | DateTime         |    YES                 |   YES                        |   YES                             |
| :utc_datetime        | DateTime         |    NO                  |   YES                        |   YES                             |
| :naive_datetime_usec | NaiveDateTime    |    YES                 |   NO                         |   YES                             |
| :naive_datetime      | NaiveDateTime    |    NO                  |   NO                         |   YES                             |
+----------------------+------------------+------------------------+------------------------------+-----------------------------------+

---

• 针对 PostgreSQL 的讨论和建议

  • Don't use timestamp (without time zone) (PostgreSQL Wiki)

  • Difference between timestamps with/without time zone in PostgreSQL

  • Ignoring time zones altogether in Rails and PostgreSQL (accepted answer)

  • 8.5. Date/Time Types (PostgreSQL manual)

  • 9.9.3. AT TIME ZONE (PostgreSQL manual)