How to get month in ecto query from "inserted_at" compared to part of date?

Hello,

I’ve tried “fragments” but I can’t get this work as “inserted_at” is a naive datetime, and my field consist only year and month so I can’t compare them in query.

What I can do is to select distinct year/month from query using fragments, but can’t get this work in “where” clause.

I want to get only entries from a given month, list all posts made in that month and sum comments for them.

Any idea?

Will something like this work?

input = “2022-11”
query = from(p in Post where: String_starts_with?(Date.to_string(~N[2022-11-28 14:14]), ^input) == true, select: p.title)

No. You cannot use arbitrary elixir code in queries. Ecto queries are just a DSL to build up sql, so you can only do what your db supports using sql.

Something like

from p in Post, 
  where: fragment("date_part(?, ?)", "year", p.inserted_at) == ^year,
  where: fragment("date_part(?, ?)", "month", p.inserted_at) == ^month
5 Likes

def posts_by_date(date) do

date_with_time = date <> "-01 00:00:00"
my_date =  NaiveDateTime.from_iso8601!(date_with_time)
year = my_date.year
month = my_date.month
query = from(p in Reward, where: fragment("date_part(?, ?)", "year", p.inserted_at) == ^year, where: fragment("date_part(?, ?)", "month", p.inserted_at) == ^month, select: [p.user_id, p.comment, p.inserted_at])

query
|> Repo.all()

end

My query results

[1, 5, ~N[2022-11-27 15:58:16]]

why it’s not a map?

Because you selected a list.

1 Like