Ecto - accessing datetime fields by month

Question

defp query_by(query, %{"min_end_date_month" => min_end_date_month} = params) do
  query = from(q in query, where: q.end_date.month <= ^min_end_date_month)

  query_by(query, Map.delete(params, "min_end_date_month"))
end

The query function above is returning this error: (Ecto.Query.CompileError) q.start_date().year() is not a valid query expression. However, in the iex console, I can normally do some_record.end_date.month since month is a valid key. What am I doing wrong?

Dogbert Dogbert · Accepted Answer · 2018-09-13T10:34:51

When a query returns a Date value to Elixir, Elixir decodes it into a Date struct, which lets you access the parts of the date using .year, .month, etc. A query is executed by the database though, where you can't access the year/month/day with a .. There are database specific functions to extract these values from a Date value. Assuming you're on PostgreSQL, you can use date_part:

query = from(q in query, where: fragment("date_part('month', ?)", q.end_date) <= ^min_end_date_month)

Ecto - accessing datetime fields by month

1 Answers