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!

2 Likes
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.

15 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
5 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.

3 Likes
9

Ah, I see. That makes sense. Thanks.

10

Looking at this thread from 2019, I can see the OP’s frustration with the timezone defaults in Ecto/Phoenix. Here’s a friendly follow-up question you could post:

Hey everyone!

I just stumbled across this thread from a few years back, and I’m wondering if there’s been any movement on this topic since then?

I completely agree with the original sentiment here - it feels like we’re swimming upstream when timezone-aware timestamps should really be the default in 2025. I’ve also been bitten having both NaiveDateTime and DateTime in my code, and honestly, having to remember timestamps(type: :utc_datetime) in schemas and timestamps(type: :timestamptz) in migrations feels like unnecessary cognitive overhead.

I’m wondering: Would there be appetite for this change now? I imagine the backwards compatibility concerns are still valid, but maybe there’s a path forward for new Phoenix apps?

I’m considering writing a Credo custom check to catch this, but I’d love to hear if others have found more elegant solutions.

It just seems like in 2025, timezone-awareness should be table stakes rather than opt-in. Most modern applications need to handle timezones properly anyway.

Thoughts? Has anyone else been advocating for this change, or found good patterns to make this less painful?

2 Likes
11 
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.

4 Likes
12

Yeah, this is one of the issues with Ecto’s approach of trying to please everyone, when what we need is one source of truth (the database) and then do the right thing. This doesn’t have to be this hard.

13

As an application developer, I’d like to avoid extra boilerplate to remember, and avoid having two different datetime types in my app (DateTime and NaiveDateTime).

Let’s put aside the Postgres implementation. I think that’s a distraction.

Can we build a consensus around schema timestamps() defaulting internally to timestamps(type: :utc_datetime)?

The semantic idea when it includes a tz is that the timestamp represents an instant in time. This matches exactly the intent of created_at and updated_at.

1 Like
14

:utc_datetime_usec but yes, I do think that this should be the default.

3 Likes
15

But, once again, that’s not how it behaves with PostgreSQL. No time zone is actually stored or included. This name has historically confused a lot of people and the feature you are arguing for is not there.

It is also not how it behaves with MySQL nor SQLite. The only database supported by Ecto that properly handles timezones is SQL Server. So it isn’t a matter of “trying to please everyone” either.

And unfortunately we cannot default to utc_datetime_usec in Ecto without it being a massive breaking change.

2 Likes
16

I would also add that storing the time zone in the database for past dates is most likely suboptimal because if that data is seen by different users, you most likely want to show it in the user respective time zone, and not in the time zone it was created in. That’s why most applications have you select the time zone in the user profile and then convert the date time to the user time zone at the time of display, which is the same level of complexity for both naive and UTC datetimes.

And depending on the application, there is a much simpler approach, which is just to output the datetime (either explicitly or assumed to be in UTC) and use JavaScript (or phx-hook) to convert it to the user time zone. This way you never store the time zone on the server and always handle it on the client. This also has the advantage that you would show it in the user locale (MM-DD-YYYY, DD-MM-YYYY, etc). The level of complexity here is also the same regardless if you’re using naïve or UTC date times.

1 Like
17

Since new users often first encounter Ecto through Phoenix, would it make sense to update the Phoenix generators to provide better defaults for newly created projects that use Postgres? For example:

config :my_app, MyApp.Repo,
  migration_timestamps: [
    type: :timestamptz,
    null: false,
    # Same as now(), but more explicit https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
    default: {:fragment, "transaction_timestamp()"}
  ],
  # Modern SQL identity columns instead of serial https://www.postgresql.org/docs/current/ddl-identity-columns.html#DDL-IDENTITY-COLUMNS
  migration_primary_key: [name: :id, type: :bigint, generated: "ALWAYS AS IDENTITY"],
  # May need to disable if using CockroachDB, PgBouncer, or other proxies
  # https://hexdocs.pm/ecto_sql/Ecto.Adapters.Postgres.html#module-migration-options
  migration_lock: :pg_advisory_lock
2 Likes
18

I am skeptical you are gaining much by using timestamptz and it will just increase the odds of confusion we have seen repeated in this thread a few times already. Advisory locks also comes with their pitfalls that can lead to a broken first deploy. I think identity columns would be a good default though.

19

I do all of that: JS to automatically localize; query the user for their perfered tz for emails.

I just don’t want both DateTime and NaiveDateTime floating around by default in my app. It’s a 1-month old Phoenix 1.7.21 app.

It was actually the JS phx-hook local time conversion that broke on the NaiveDateTimes. — that caused me to spend time figuring out why I had two different types, how to manually add configs to make them match, etc.

I believe this is orthogonal to how timestamps are stored in the RDBMS.

My thought about updated_at and inserted_at isn’t about the DB layer — it’s about Ecto. IMO it’s never appropriate to represent those without a timezone because they represent a past event, a globally distinct moment in time. So the tz is part of the data.

1 Like
20

Oh, I see. This was a thread about timestamptz though and your mention of timestamptz additionaly pointed everyone to the wrong direction. :sweat_smile:

If you only want DateTime instead of NaiveDateTime, you indeed don’t need to touch migrations. In both cases, the data is stored in exactly the same way, you just want to load it into another data type. You could change all calls to timestamps but you can also define schema defaults like this:

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

And use that as the Ecto schema. I believe Phoenix generators even have a configuration that instructs it to only use a schema of your choice as default instead of use Ecto.Schema. Alternatively follow @LostKobrakai’s suggestion and add _usec too (in this case, both to the schema and the migration, as the migration defaults to no usec).

1 Like