Ecto/Elixir, How can I query by date?

Question

I am working on statistics page of my app and trying to query data by date.

To get the date range, I use Calendar.Date

date_range = Date.days_after_until(start_date, end_date, true)
|> Enum.to_list

And it returns date list of dates and each date looks like "2017-04-07". So with the date I got from date_range, I tried to query but it triggers an error like below.

where cannot be cast to type Ecto.DateTime in query: from o in Myapp.Order, where: o.created_date >= ^~D[2017-04-07]

For created_date field of Order, I made field like this, field :created_date, Ecto.DateTime.

If I want to query by date, how can I query it?

Thank in advance.

Ethical

What is the exact query you ran? – Impiety

Answer 1

It looks like you're trying to compare a date and datetime. You need to cast one of them to the other type so the comparison works, e.g. convert the datetime in the database to a date:

date = ~D[2017-01-01]
from p in Post, where: fragment("?::date", p.inserted_at) >= ^date

or convert the Elixir Date to NaiveDateTime:

{:ok, datetime} = NaiveDateTime.new(date, ~T[00:00:00])
from p in Post, where: p.inserted_at >= ^datetime

If you have a start and end date, you just need to add an and to either. You don't need to generate the whole list of dates using any library.

from p in Post,
  where: fragment("?::date", p.inserted_at) >= ^start_date and
         fragment("?::date", p.inserted_at) <= ^end_date

or

from p in Post,
  where: p.inserted_at >= ^start_datetime and
         p.inserted_at <= ^end_datetime

Impiety

thank you! after I change date to datetime, it works! – Ethical

Note that in more recent Ecto versions, it's possible to do the conversion without fragments by using the hexdocs.pm/ecto/Ecto.Query.API.html#type/2 function – Declivitous