Postgrex expected %DateTime{}, got ~N[2022-12-01 05:35:36]

Questions / Help
1

Having a bit of trouble with this one.

My migration and schema:

# migration
add :event_timestamp, :naive_datetime

# schema
field :event_timestamp, :naive_datetime

Here’s some logs

IO.inspect(params["ReceivedAt"])
# "2022-12-01T05:35:35Z"

{:ok, event_timestamp} = NaiveDateTime.from_iso8601(params["ReceivedAt"])

IO.inspect(event_timestamp)
# ~N[2022-12-01 05:35:35]

When I try to save this value using my context function I get this error:

** (DBConnection.EncodeError) Postgrex expected %DateTime{}, got ~N[2022-12-01 05:35:36]. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
(postgrex 0.16.5) lib/postgrex/type_module.ex:947: Postgrex.DefaultTypes.encode_params/3

2

What is the query?

3

After a good nights sleep I figured out the problem.

It turns out this field was correct, it was the timestamps that was failing on insert.

My migration was:

create table(:email_performances, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :event_timestamp, :naive_datetime
      add :event_type, :text
      add :sent_email_id, references(:sent_emails, on_delete: :nothing, type: :binary_id)

      timestamps(type: :timestamptz)
    end

But I forgot to set my schema correctly.

schema "email_performances" do
    field :event_timestamp, :naive_datetime
    field :event_type, Ecto.Enum, values: [:clicked, :opened]

    belongs_to :sent_email, SentEmail

    # BAD
    # timestamps()

    # GOOD
    timestamps(type: :utc_datetime_usec)
  end

Thanks @josevalim

3 Likes
4

So glad you posted your solution, as this was what I needed for a similar problem.

1 Like