How to set timestamps to UTC DateTimes in Ecto

In Ecto versions 2.1 through 3.x the Ecto.Schema.timestamps/1 feature (updated_at and inserted_at) has been naive_datetime by default. I highly recommend using utc_datetime or utc_datetime_usec instead. This article will show how to do that.

Setting the type

One way to set the timestamps type is to put the line @timestamps_opts [type: :utc_datetime] in any module where use Ecto.Schema is present. Example:

defmodule User do
  use Ecto.Schema
  @timestamps_opts [type: :utc_datetime]

  schema "users" do
    field :name, :string
    timestamps()
  end
...

That’s it. Add that @timestamps_opts module attribute everywhere use Ecto.Schema is present. You could stop reading now and go and make that change to your Ecto project. Continue reading if you want more details:

More details

An alternative to the @timestamps_opts way is to pass the type as an argument when calling the timestamps/1 function in the schema:

  schema "users" do
    field :name, :string
    timestamps([type: :utc_datetime_usec])
  end

Either way works. I personally tend to prefer using the @timestamps_opts module attribute.

Ecto 3 types for timestamps and microseconds

Ecto 3 has a choice of four types to use for the timestamps function: :utc_datetime, :utc_datetime_usec, :naive_datetime,:naive_datetime_usec

They are equivalent to the following Elixir types:

Migrations and microseconds

If your Ecto project is currrently using naive_datetime for timestamps and you switch to utc_datetime in your schemas, you don’t have to do any changes to migrations for it to work. That being said here is some information about microsecond precision:

I like to use :utc_datetime_usec for the timestamps because it has microsecond precision instead of just whole seconds. In certain cases this can be useful. In order to have microsecond precision, make sure that the type created in the database table stores microseconds. This can be done by using utc_datetime_usec in the migration.

If you do not want to use microsecond precision, use :utc_datetime instead of :utc_datetime_usec in your schemas and make sure that the migration for the timestamps match in terms of having “_usec” at the end or not.

A peculiar detail is that in migrations, unlike schemas, “utc_datetime” and “naive_datetime” both do the same thing. It is “_usec” that matters in migrations. In Postgres the type is either timestamp without time zone or timestamp(0) without time zone. The “(0)” part means that fractional seconds are not stored.

As an aside - you might wonder why Ecto uses a Postgres type called “timestamp without time zone” even though we know that the time zone is UTC, but that is a subject for another blog post. For the type in the postgres database, the thing that counts is whether there is a (0) at the end or not: timestamp without time zone(0) for whole seconds and timestamp without time zone for the Ecto types that end in _usec.

To make sure the migrations have precision you want (usec or whole seconds), you can specify the type in the migrations when creating a table like so:

defmodule YourAppNameHere.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :name, :string

      timestamps([type: :utc_datetime_usec])
    end

  end
end

I like this approach because it is explicit and consistently works the same way regardsless of configuration. However an alternative to setting the type in the migration files is to use config. Putting the following in config.exs will use microsecond precision when running the migrations even if the migration files do not specify this and just say timestamps(): config :your_app_name_here, YourAppNameHere.Repo, migration_timestamps: [type: :utc_datetime_usec]

If you have existing tables and you want to change the microsecond precision with an Ecto migration here is an example of a migration that does that. In this case using microsecond precision:

defmodule YourAppNameHere.Repo.Migrations.MakeTimestampsUsec do
  use Ecto.Migration

  def change do
    # For each of the listed tables, change the type of :inserted_at and :updated_at to microsecond precision
    ~w/users products another_table/
    |> Enum.map(&String.to_atom/1)
    |> Enum.each(fn table_name ->
      alter table(table_name) do
        modify :inserted_at, :utc_datetime_usec
        modify :updated_at, :utc_datetime_usec
      end
    end)
  end
end

Ecto 2

In Ecto 2 (starting from version 2.1) there are two datetime types instead of four. Microseconds have their own separate setting for timestamps: usec which is a boolean. [type: :utc_datetime, usec: true] in Ecto 2 is the equivalent of [type: :utc_datetime_usec] in Ecto 3.

As with Ecto 3 you can put a @timestamps_opts everywhere use Ecto.Schema is present:

@timestamps_opts [type: :utc_datetime, usec: true]

Make sure to set usec to true or false in depending on the type in the database.

Why

This text has covered how to use UTC DateTimes for Ecto timestamps instead of NaiveDateTime. A later article will go more into why this is a good idea. It is related to “keeping the units around” in your data.

P.S. At the end of August I will be speaking about Date, Time and Timezones in Elixir 1.9 at ElixirConf US 2019 in Colorado.