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

user_1498178 0
Viewed 4185

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

ecto elixir postgresql
1 Answers

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

  • 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

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