Loss of precision with microsecond timestamps

Hey all,

Just a question here about using microsecond precision for timestamps with Postgres.

My user migration is:

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

  def change do
    create table(:users, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :username, :string, null: false
      add :email, :string, null: false
      add :password_hash, :string
      add :is_active, :boolean, default: false, null: false

      timestamps(type: :utc_datetime_usec)
    end

    create unique_index(:users, [:username])
    create unique_index(:users, [:email])
  end
end

and my User Schema is:

defmodule MyApp.Account.User do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "users" do
    field :username, :string
    field :email, :string
    field :is_active, :boolean, default: false
    field :password, :string, virtual: true
    field :password_hash, :string

    timestamps(type: :utc_datetime_usec)
  end

  # ...
end

But in my tests I am finding that when the user is retrieved, there is a loss of precision with the database user record’s timestamp being rounded to the nearest second:

  1) test users update_user/2 with invalid data returns error changeset (MyApp.AccountTest)
     test/my_app/account_test.exs:55
     Assertion with == failed
     code:  assert user == Account.get_user!(user.id)
     left:  %MyApp.Account.User{
              __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
              email: "some email",
              id: "2da66128-72bf-4b33-b46a-485fe885bcef",
              inserted_at: ~U[2020-07-28 16:27:21.850620Z],
              is_active: true,
              password: nil,
              password_hash: "$2b$04$VwiXS6DTkMY.Ikvy7nVAFeqx6uqMzavmyrzI6uapL9Sij1rHzh3EC",
              updated_at: ~U[2020-07-28 16:27:21.850620Z],
              username: "some username"
            }
     right: %MyApp.Account.User{
              __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
              email: "some email",
              id: "2da66128-72bf-4b33-b46a-485fe885bcef",
              inserted_at: ~U[2020-07-28 16:27:22.000000Z],
              is_active: true,
              password: nil,
              password_hash: "$2b$04$VwiXS6DTkMY.Ikvy7nVAFeqx6uqMzavmyrzI6uapL9Sij1rHzh3EC",
              updated_at: ~U[2020-07-28 16:27:22.000000Z],
              username: "some username"
            }
     stacktrace:
       test/my_app/account_test.exs:58: (test

Am I missing an ecto configuration step?

nevermind. Dropping the test database fixed my problem.

> MIX_ENV=test mix ecto.drop
The database for MyApp.Repo has been dropped

> mix test
..........

Finished in 0.2 seconds
10 tests, 0 failures

Randomized with seed 516127
1 Like

The test DB is usually too aggressively not dropped and re-created in Elixir’s Phoenix+Ecto projects. If you have any doubts that previous bogus migrations could have made it not a good copy of your dev DB then indeed the best course of action is to just drop and recreate it as you did.

Glad you solved your problem. :023:

1 Like

Good to know!