Why can't `:timestamptz` be set up as default timestamp for migrations in config?

Hi,

Trying to make :utc_datetime the global default for Ecto schemas and migrations in our app, and use timestamptz as default datetime type in PostgreSQL. Was able to figure these out, but there seems to be inconsistency around migration configurations - and because I don’t understand what’s happening, having a hard time letting it go.

Schemas

Making :utc_datetime the default at most can only be done on a module-per-module basis, but at least it’s straightforward.

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
end

Migrations

In migration: timestamps(type: :timestamptz)

Found the simplest solution in Time zones in PostgreSQL, Elixir and Phoenix by @hubertlepicki :

create table(:events) do
  add :title, :string

  timestamps(type: :timestamptz)
end

Which makes total sense, because Ecto.Migration.timestamps/1 will forward the type info to Ecto.Migration.add/3.

This solution also seems to automatically choose the right Elixir struct, which is DateTime, instead of the default NaiveDateTime, because if the corresponding schema timestamps are not set to :utc_datetime, any DB operations will fail.

Application-wide: migration_timestamps

From the Ecto.Migration docs:

  • :migration_timestamps - By default, Ecto uses the :naive_datetime type, but you can configure it via:

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

Naively assumed that exchanging :utc_datetime to :timestamptz and simply using timestamps() in the migration would do the trick (based on how Ecto.Migration.timestamps/1 is implemented), but got an error message instead (see below).

It also seems superfluous to use a global config with :utc_datetime, because one would have to provide :timestamptz in the migration anyway, simply overwriting the global setting.

I assume that providing the type directly to timestamps/1 goes through the Postgrex adapter, setting the correct Elixir type, but using config has a side effect somewhere along the line.

** (DBConnection.EncodeError) Postgrex expected %DateTime{}, got ~N[2019-10-01 17:45:34]. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
    (postgrex) lib/postgrex/type_module.ex:897: Postgrex.DefaultTypes.encode_params/3
    (postgrex) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
    (db_connection) lib/db_connection.ex:1148: DBConnection.encode/5
    (db_connection) lib/db_connection.ex:1246: DBConnection.run_prepare_execute/5
    (db_connection) lib/db_connection.ex:1342: DBConnection.run/6
    (db_connection) lib/db_connection.ex:540: DBConnection.parsed_prepare_execute/5
    (db_connection) lib/db_connection.ex:533: DBConnection.prepare_execute/4
    (postgrex) lib/postgrex.ex:198: Postgrex.query/4
    (ecto_sql) lib/ecto/adapters/sql.ex:658: Ecto.Adapters.SQL.struct/10
    (ecto) lib/ecto/repo/schema.ex:649: Ecto.Repo.Schema.apply/4
    (ecto) lib/ecto/repo/schema.ex:262: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    priv/repo/seeds.exs:21: anonymous fn/2 in :elixir_compiler_1.__FILE__/1
    (elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
    priv/repo/seeds.exs:16: (file)
    (elixir) lib/code.ex:813: Code.require_file/2

In the end, I don’t mind the extra typing, and timestamps(type: :timestamptz) explicitly conveys type in every migration, but still curious what I am missing.

Thanks!

1 Like

Naively assumed that exchanging :utc_datetime to :timestamptz and simply using timestamps() in the migration would do the trick (based on how Ecto.Migration.timestamps/1 is implemented), but got an error message instead (see below).

This should work. However, if you update your migrations, you must update your schema. So a combination of:

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

And @timestamps_opts [type: :utc_datetime] is enough for everything.

Ecto does not automatically figure out which type to use from the migration/database, exactly because a single database type may be one or more Elixir types. Case in point, utc_datetime can be used for both timestamp and timestamptz. The way timestamptz works is that the DB converts to UTC. The way utc_datetime works is that Elixir’s adapter converts to UTC before writing to the database. So since utc_datetime already guarantees the data will be normalized, you don’t strictly need timestamptz.

7 Likes

Thanks, I was an idiot, and just needed someone to confirm it works I guess.

Missed the part, that all my migrations ran successfully, and only the queries in seeds.exs failed. Those only failed because I started experimenting on a schema with lots of relationships (has_many/3, many_to_many/3), and forgot to update the corresponding schemas…

I was also wrong about this one, it is in plain sight in the docs:
https://hexdocs.pm/ecto/Ecto.Schema.html#module-schema-attributes

# Define a module to be used as base
defmodule MyApp.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @primary_key {:id, :binary_id, autogenerate: true}
      @foreign_key_type :binary_id
    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
  end
end
1 Like