Definitely use :utc_datetime_*. In hindsight, we wish we made that the default in Ecto but now we can’t due to backwards compatibility (and there are no plans for Ecto 4)

Here are some resources:

• https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29
• https://www.amberbit.com/blog/2017/8/3/time-zones-in-postgresql-elixir-and-phoenix/
• http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html

Thank you all so much! this sums it up, once and for all !
So basically it’s just great that Ecto is enforcing saving DateTime values in utc with the utc_datetime, which I feel really positive about since it’s a standard development practice. I’ve seen no other library/framework enforcing it.

I made the following global changes to my Phoenix 1.5 app to use utc_datetime instead of naive_datetime by Ecto, both in schemas and migrations for anyone who may find it useful:

In config.exs, I added this:

config :myapp, MyApp.Repo, migration_timestamps: [type: :utc_datetime]

created a new schema.ex with the following code:

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

And in my models everywhere, I replaced use Ecto.Schema with use MyApp.Schema

And, done!

This only works for automatically generated fields like inserted_at and updated_at and if you manually set other datetime fields to DateTime.utc_now(). But if you have any other Elixir code that is not timezone-aware and it fetches time in the local timezone (or have a legacy DB with non-UTC datetimes in it) then you’re in for trouble.

Better to do all of these together:

• Use utc_datetime type for datetime fields in your Ecto schema modules.
• When setting times manually, always start with DateTime.utc_now() and/or do calculations with Elixir’s stdlib or timex, always making sure you use UTC.
• If you have to work with an inherited legacy database, just make a one-off script to convert any non-UTC datetimes to UTC. It will save you a world of trouble in the future.

I started with :utc_datetime but switched to :utc_datetime_usec just because it got annoying having to do DateTime.utc_now() |> DateTime.truncate(:second) every time. I don’t really need the precision but it seemed to be less friction for the stuff I’ve done so far.

It is unfortunate that this cannot be done in Ecto because of backwards compatibility concerns, but it would be nice to do this by default for all new Phoenix applications.

I started a discussion about that on the mailing list if anyone is interested in following along:

https://groups.google.com/g/phoenix-core/c/dQA8zyCv93g