Regardless if you are new to Phoenix, Ecto or Elixir, at some point you might wonder what the two timestamp type options in your ecto schema are about.
It also does not help much that the documentation on the differences between them is very minimal. It comes down to:
:naive_datetimerepresent the NaiveDateTime Elixir type. You can find more information on it here.:utc_datetimerepresent the DateTime Elixir type. You can find mor information on it here.
Now, when to use what?
But lets come back to the original question, the difference between them and when to use a timestamp with and without timezone information. Although the practical effect of this setting might not be that drastic on a new greenfield project it might get quite important down the road.
At the time of writing this, the default value in ecto is still :naive_timestamp, mainly for backwards compatibility. In most cases and if in doubt:
Use utc_datetime in the Ecto schemas and timestamptz in migrations.
On the one hand side it makes the used timestamp types more explicit and pins the timezone handling within the database to utc.
defmodule Hello.User do use Ecto.Schema import Ecto.Changeset schema "users" do field :bio, :string field :email, :string field :name, :string field :number_of_pets, :integer timestamps(type: :utc_datetime) end @doc false def changeset(user, attrs) do user |> cast(attrs, [:name, :email, :bio, :number_of_pets]) |> validate_required([:name, :email, :bio, :number_of_pets]) end end
defmodule Hello.Repo.Migrations.CreateUsers do use Ecto.Migration def change do create table(:users) do add :name, :string add :email, :string add :bio, :string add :number_of_pets, :integer timestamps(type: :timestamptz) end end end
How to handle timezones then?
If you don’t use the timestamptz type on the database level the timezone representation might depend on the timezone of your database connection. Although that is not the case for the postgrex driver used by ecto as it uses a binary protocol that always represents timestamps in utc but if you connect to the same database using other drivers or languages it might lead to strange and hard to debug situations.
In general it’s also a good idea to keep the user-facing timezone decoration/information out of the controller and persistence boundaries and handle it in the view.
There might be cases when you really want to store the specific timezone for a timestamp but even in these cases it might be a better idea to keep the timestamp itself on utc and store the timezone in a specific field next to it. Be sure to know what you are doing in those cases.
Summary
To sum it up, when in doubt, use :utc_datetime in your schema and :timestamptz in your migration and you will most likely be on the right track.
How to persist a point in time is quite a deep rabbit hole. There is quite some historic background to this topic regarding Elixir and Ecto but also regarding databases like PostgreSQL or MySQL. I might write up a longer article reflecting on that topic from various angles so, stay tuned.
What are your opinions and experiences on this? Any comments, critiques and suggestions are highly welcome.