In Ecto versions 2.1 through 3.x the
Ecto.Schema.timestamps/1 feature (
inserted_at) has been
naive_datetime by default. I highly recommend using
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:
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
They are equivalent to the following Elixir types:
|Ecto 3 type||Elixir type||Supports microseconds?||Supports DateTime functions?||Supports NaiveDateTime functions?|
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.
|Ecto 3 migration type||Postgres type||Stores microseconds?|
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
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
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
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
use Ecto.Schema is present:
@timestamps_opts [type: :utc_datetime, usec: true]
Make sure to set
false in depending on the type in the database.
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.