postgresqlelixirecto

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

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).

Solution

  • 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

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