CODEWITHSUNDEEP
elixirphoenix-frameworkecto
gkkirsch
gkkirsch

Reputation: 2116

How to write a Ecto query that does a group_by MONTH on a datetime field

I am doing a ecto query and am trying to group by q.created_date. This query successfully does the GROUP BY but it does it by the second. I am trying to group by month instead.

MYQUERY |> group_by([q], [q.created_date, q.id])

Is there something like:

MYQUERY |> group_by([q], [month(q.created_date), q.id])

Upvotes: 4

Views: 4379

Answers (2)

ashraf
ashraf

Reputation: 557

Using Dogbert's answer and arvidkahl's excellent hint, it's possible to group by the month without ambiguity on the fragment dimension using the following:

from q in query,
  join: i in Item,
  on: i.id == q.item_id,
  group_by: [i.category_id, fragment("date_trunc('month', ?)", q.inserted_at)],
  select: %{category_id: i.category_id, date: fragment("date_trunc('day', ?)", q.inserted_at), quantity: sum(q.quantity)}

Upvotes: 2

Dogbert
Dogbert

Reputation: 222198

You can use fragment with date_part('month', field) to extract the month in PostgreSQL: fragment("date_part('month', ?)", p.inserted_at))

iex(1)> from(Post) |> select([p], p.inserted_at) |> Repo.all
[debug] QUERY OK db=1.1ms queue=0.1ms
SELECT p0."inserted_at" FROM "posts" AS p0 []
[#Ecto.DateTime<2000-01-01 00:00:00>, #Ecto.DateTime<2000-02-02 00:00:00>,
 #Ecto.DateTime<2000-03-03 00:00:00>, #Ecto.DateTime<2000-04-04 00:00:00>,
 #Ecto.DateTime<2000-04-02 00:00:00>, #Ecto.DateTime<2000-03-02 00:00:00>,
 #Ecto.DateTime<2000-02-02 00:00:00>, #Ecto.DateTime<2000-01-02 00:00:00>]
iex(2)> from(Post) |> group_by([p], fragment("date_part('month', ?)", p.inserted_at)) |> select([p], count("*")) |> Repo.all
[debug] QUERY OK db=1.7ms
SELECT count('*') FROM "posts" AS p0 GROUP BY date_part('month', p0."inserted_at") []
[2, 2, 2, 2]

Upvotes: 13

Related Questions