Cannot be cast to type :naive_datetime in query

Question

I have query:

 PhoenixApp.one(from r in PhoenixApp.Reply,
              where: r.inserted_at > ^datatime,
              select: count(r.id))

This query fails with error: value '#Ecto.DateTime<2016-12-04 20:11:21>' in 'where' cannot be cast to type :naive_datetime in query:

But when convert datatime using type(^datatime, Ecto.DateTime) it's works.

Question: Whay it's happend, looks like datatime initially have DateTime type?

Using new Ecto 2.1.2

Answer 1

It looks like Ecto does not support casting from the deprecated Ecto.DateTime to the new Elixir NaiveDateTime struct, which you're using when you use :naive_datetime in Ecto 2.1+:

iex(1)> dt = Ecto.DateTime.utc
#Ecto.DateTime<2017-01-08 12:11:59>
iex(2)> Ecto.Type.cast :naive_datetime, dt
:error

The recommended way in Ecto 2.1 is to stop using deprecated Ecto.DateTime struct and use Elixir's new NaiveDateTime (or DateTime) everywhere. If you still want to explicitly convert between them, you can do |> Ecto.DateTime.to_erl |> NaiveDateTime.from_erl! and use that value in your query:

iex(1)> dt = Ecto.DateTime.utc
#Ecto.DateTime<2017-01-08 12:13:50>
iex(2)> dt |> Ecto.DateTime.to_erl |> NaiveDateTime.from_erl!
~N[2017-01-08 12:13:50]

If you're getting the old structs from Ecto queries, you probably have the old types defined for them in your schemas, which you should change from field :foo, Ecto.DateTime to field :foo, NaiveDateTime (or field :foo, DateTime).

See also: https://github.com/elixir-ecto/ecto/blob/v2.1/CHANGELOG.md#integration-with-elixir-13-calendar-types.