How to set `DateTime` in Ecto schemas and `timestamp with time zone` (`timestamptz`) PostgreSQL type in migrations?

Question

Would like to use DateTime in Ecto schemas and migrations, instead of the default NaiveDateTime, and also timestamptz in PostgreSQL, instead of the default timestamp (aka. timestamp without time zone).

Answer 1

ECTO MIGRATIONS: Switch to timestamptz and :utc_datetime

Note: Ecto.Migration.timestamps/1 (source) global configuration can always be overridden locally.

1. Global configuration

Using the :migration_timestamps configuration option from the Ecto.Migration docs:

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

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

and one can use Ecto.Migration.timestamps/1 in migrations as usual:

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

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

  timestamps()
end

The Postgres adapter will automatically switch the Elixir representation to DateTime from NaiveDateTime.

2. Local configuration

Use Ecto.Migration.timestamps/1's :type option:

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: Switch to :utc_datetime

1. Global configuration

The Ecto schemas also need to be modified to use :utc_datetime, otherwise they will expect NaiveDateTime by default. Slightly modifying the example in the 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. Local configuration

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

Resources

• Difference in between :utc_datetime and :naive_datetime in Ecto

---

• lau/tzdata

  DateTime in Elixir "only handles "Etc/UTC" datetimes" but it can be configured with a custom time zone database, which is what the tzdata library is

---

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

  A very handy table from the first article:

+----------------------+------------------+------------------------+------------------------------+-----------------------------------+
|    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                             |
+----------------------+------------------+------------------------+------------------------------+-----------------------------------+

---

• Discussions and advice specific to 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)