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.

I’ll chime in, because this is a subject that always annoyed me about how it was handled by default and I need to manually change things to my taste (I’m using PostgreSQL).

First, I want my timestamps to be timestamptz in the DB. Forget that it’s badly named (confusion about timezone), it is the correct type to represent a “point in time”. If your DB uses timestamp (without tz), it’s ambiguous and someone needing to interface with the DB outside your app won’t have the slightest clue what they represent (is it local time (ouch…) or UTC?). Using the correct type makes confusion impossible.

Anyway, most of the things I change have been mentioned in this thread.

Make sure migration timestamps are timestamptz by default and have a default, which makes it easier to insert rows in DB manually for testing. It’s important that the default timezone is set to UTC (it could be set in the DB, but I prefer to have my system work either way. It’s important because Ecto was doing some casting where where: token.inserted_at > ago(^days, "day") was converted to SQL inserted_at > $2::timestamp + (interval ...) and the only this conversion works correctly is if the default timezone is UTC.

config :my_app, MyApp.Repo,
  migration_primary_key: [type: :identity],
  migration_timestamps: [type: :timestamptz, default: {:fragment, "now()"}],
  after_connect: {Postgrex, :query!, ["SET TimeZone TO 'UTC';", []]}

As already mentioned, I have a custom schema:

defmodule MyApp.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @timestamps_opts [type: :utc_datetime_usec]
    end
  end
end

I don’t use the generators much, but still I have a private copy of the phx.gen.schema templates.
schema.ex, make it use my schema:

defmodule <%= inspect schema.module %> do
  use <%= hd(Module.split(schema.repo)) %>.Schema

migration.exs, make sure fields with utc_datetime and utc_datetime_usec are converted correctly to timestamptz, and at the same time make sure text is always used because I can’t stand the sight of varchar(255):

<%
internal_type_mapper = fn
  :string -> :text
  :utc_datetime_usec -> :timestamptz
  other -> other
end

type_mapper = fn
  :utc_datetime -> ":timestamptz, size: 0"
  {:array, :utc_datetime} -> "{:array, :timestamptz}, size: 0"
  {:array, subtype} -> inspect({:array, internal_type_mapper.(subtype)})
  other -> inspect(internal_type_mapper.(other))
end
%>defmodule <%= inspect schema.repo %>.Migrations.Create<%= Macro.camelize(schema.table) %> do
...
... <%= type_mapper.(Mix.Phoenix.Schema.type_for_migration(v)) %> ...

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

config :my_app, MyApp.Repo,
  migration_timestamps: [type: :utc_datetime_usec]

defmodule MyApp.Ecto.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @timestamps_opts [type: :utc_datetime_usec]
    end
  end
end

That’s what I usually do.

It can only be reiterated that timestamptz despite its name does not store timezones though. The timestamp is stored in UTC. Though it does make timezone conversions within queries a bit less verbose with the db knowing it’s utc.

It also enables automatic conversion to/from the session timezone for postgres clients - which I find more confusing than helpful given different clients can see different values. Ecto does use the binary protocol of postgres, which doesn’t do sessions anyways.

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.