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

Questions / Help
,
1

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
2

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.

12 Likes
3

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…

4

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
4 Likes
5

Was wondering: Why doesn’t my app scream at me in any way when I set the @timestamps_opts [type: :utc_datetime] inside my schema, without setting the App.Repo config (config :app, App.Repo, migration_timestamps: [type: :utc_datetime])?

I reckon a detail, but in my case :type must be set to :utc_datetime and not :timestamptz.

6

Because in ecto schemas and migrations are completely separate things. There is no coupling or inference between them at all. Ecto schemas can be used with partial table data, whole tables or multiple tables (even similar, but not the same ones). So these things need to be decoupled.

That’s not the case. In ecto migrations you can use ecto native types (e.g. :utc_datetime) for columns, which map to what ecto considers the best default for the type (timestamp(0) for :utc_datetime). If you need a different column type from that ecto selected one then you can also use the names of the db specific types like :timestamptz.

7

But if there is a mismatch between the date types, shouldn’t the database reject the struct created by the changeset? That is the scenario I expected to get yelled at by the database.

I simply meant that I want to set the type to utc_datetime, not to :timestamptz – in contrast to OP. But this is definitely good to know. Ty.

8

It depends. The database never sees any information about the struct. All it sees is the value (as mapped to by Ecto.Type.dump/2) you try to write. If the value is still valid for the db column there’s nothing to complain. If that’s not the case you’ll get a database error at runtime. So mismatches between different timestamp values are unlikely to cause issues, as it the end the value written to the db is always a timestamp.

2 Likes
9

Ah, I see. That makes sense. Thanks.