Ecto 3.0 app with :utc_datetime timestamps gives "DBConnection.EncodeError: Postgrex expected %DateTime{}, got ~N[...] error

Questions & Help Questions
1

Hi. I need help getting utc_timestamps to work with Ecto 3. I initially tried upgrading my 1.3.0 Phoenix app to 1.4.0, but got this error and then tried a simple barebones Ecto 3.0 app without Phoenix and I am still getting the same error.

I am using utc_datetime timestamps and “timestamp with time zone” columns in Postgresql, but when I want to insert a record it looks like Postgrex gets it a NaiveDateTime but expects a %DateTime{} value.

In iex i do the following:

iex(1)> person = %Friends.Person{age: 28}
iex(2)> Friends.Repo.insert(person)

And here is the error:

21:03:33.319 [debug] QUERY ERROR db=5.6ms queue=1.1ms
INSERT INTO "people" ("age","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING
"id" [28, ~N[2018-11-12 19:03:33], ~N[2018-11-12 19:03:33]]
** (DBConnection.EncodeError) Postgrex expected %DateTime{}, got ~N[2018-11-12  19:03:33]. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
(postgrex) lib/postgrex/type_module.ex:713: Postgrex.DefaultTypes.encode_params/3
(postgrex) lib/postgrex/query.ex:62: DBConnection.Query.Postgrex.Query.encode/3
(db_connection) lib/db_connection.ex:1074: DBConnection.encode/5
(db_connection) lib/db_connection.ex:1172: DBConnection.run_prepare_execute/5
(db_connection) lib/db_connection.ex:1268: DBConnection.run/6
(db_connection) lib/db_connection.ex:480: DBConnection.parsed_prepare_execute/5
(db_connection) lib/db_connection.ex:473: DBConnection.prepare_execute/4
(postgrex) lib/postgrex.ex:167: Postgrex.query/4

The deps in my mix.exs:

[
  {:ecto_sql, "~> 3.0"},
  {:postgrex, ">= 0.0.0"}
]

My migration is as follows:

defmodule Friends.Repo.Migrations.CreatePeople do
  use Ecto.Migration

  def change do
    create table(:people) do
      add :first_name, :string
      add :last_name, :string
      add :age, :integer

      timestamps([type: :timestamptz])
    end
  end
end

My schema:

defmodule Friends.Person do
  use Ecto.Schema

  @timestamps_opts [type: :utc_datetime]

  schema "people" do
    field :first_name, :string
    field :last_name, :string
    field :age, :integer

    timestamps()
 end
end

My config.exs:

config :friends,
  ecto_repos: [Friends.Repo],
  migration_timestamps: [type: :utc_datetime]

Any help would be greatly appreciated.

2

Looks like you’re running into this issue: Dump UTC datetime as a DateTime by josevalim · Pull Request #2798 · elixir-ecto/ecto · GitHub

I’d keep an eye out for the next ecto release. Until then you probably will have to stick with Phoenix 1.3

3

Thank you @davydog187! I can confirm that pull request #2798 does indeed fix my issue:

iex(2)> Friends.Repo.insert(person)

20:55:13.247 [debug] QUERY OK db=5.2ms decode=2.9ms queue=1.1ms
INSERT INTO "people" ("age","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING           "id" [28, #DateTime<2018-11-13 18:55:13Z>, #DateTime<2018-11-13 18:55:13Z>]
{:ok,
  %Friends.Person{
 __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
 age: 28,
 first_name: nil,
 id: 1,
 inserted_at: #DateTime<2018-11-13 18:55:13Z>,
 last_name: nil,
 updated_at: #DateTime<2018-11-13 18:55:13Z>
}}
4

I have the same error, even though my dependencies and Elixir are of the latest versions.