Ecto Date Comparisons and Casting

Hi all,

I’m a little confused about the how dates (including datetimes) are compared in Ecto, as well as the actual usage of type/2.

For example, take the following query:

Repo.all from u in User,
where: u.inserted_at > ^Ecto.Date.from_erl({2016, 05, 15})

That gives the following error:

** (Ecto.CastError) iex:27: value `#Ecto.Date<2016-05-15>` in `where` cannot be
cast to type Ecto.DateTime in query:

from u in User,
  where: u.inserted_at > ^%{__struct__: Ecto.Date, day: 15, month: 5, year: 2016}

Error when casting value to `User.inserted_at`
    (elixir) lib/enum.ex:1473: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir) lib/enum.ex:1151: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3

But if I perform an explicit cast using type/2, where I say the type is Ecto.Date (which is already the type of the struct), then it works fine:

Repo.all from u in User,                  
where: u.inserted_at > type(^Ecto.Date.from_erl({2016, 05, 15}), Ecto.Date)

So what’s the actual purpose of type/2 here? Why is it necessary for the above to work?

I should probably note that the above is the only time I’ve ever found a need for type/2. Every other case (even including the examples in the documentation for type/2) have always worked fine without using it (so I’m not exactly sure what its purpose is).

1 Like

With [Queryable] fragments Ecto doesn’t know the type of the data elements and we can use the type/2 function to describe the type.

See the section titled “Make the test pass”:

Fragments in Ecto - Joseph Kain

1 Like


I have actually already read that blog post :slight_smile:. Specifically quoting the pertinent part:

The last piece to explain is the the type/2 calls. With fragments Ecto doesn’t know the type of the data elements and we can use the type/2 function to describe the type. In overlapping/1 we describe target.start and target.end as Ecto.Date.

In my code above, however, I wasn’t using SQL code fragments. That’s partly why I’m not sure why the casting is necessary (that, and the fact that I need to cast the Ecto.Date struct to the same type as itself for the query to work).

Edit: Ah, I see you mean Queryable fragments. So why can’t Ecto simply infer the type from within its querying DSL? It just seems a little redundant (and verbose) to have to specify the types of data elements again within the query itself.

1 Like

Hi there!

Ok, so per Ecto.Query.ex you’ll see that all types outside the Ecto.Query.API must be interpolated:
> All other types and dynamic values must be passed as a parameter using
interpolation as explained below.

We also see that avoid repetition Ecto automatically casts the interpolated values according to your defined schema:
To avoid the repetition of always specifying the types, you may define an Ecto.Schema. In such cases, Ecto will analyze your queries and automatically cast the interpolated “inserted_at” when compared to the u.inserted_at

Presumably in your User schema you have timestamps which defines the type of your inserted_at field to be an Ecto.DateTime type.

So for:

  where: u.inserted_at > ^%{__struct__: Ecto.Date, day: 15, month: 5, year: 2016}```

Ecto attempts to cast an Ecto.Date into an Ecto.DateTime which is not allowed per the Ecto.DateTime documentation

In the second query you provide a type tag:

where: u.inserted_at > type(^Ecto.Date.from_erl({2016, 05, 15}), Ecto.Date)```

The type tag tells Ecto to take the interpolated value and as you mentioned cast it to the Ecto.Date type. Now, the SQL query is passed off to the database, here is where the :date you passed is cast by the database to a :datetime.

Now, I can’t answer the Why? question with absolute certainty, given that I’m not a part of the core team. That said, I surmise that a decision was made to let the DB’s themselves decide how to handle casts from :date to :datetime to ensure consistency amongst a particular platform. For example, should your provided :date be cast to a :datetime at noon? 12:00:00 or the first minute of the day 00:00:00? What would happen if Ecto settled on 12:00:00 but your database interacts with another application who handles casting of the same :date to 00:00:00?

Hope that helps!


Fantastic answer @david! That nicely explains why I was receiving an Ecto.CastError when type/2 wasn’t used in the query :slight_smile:. Thanks for the explanation!