You are passing in an integer to date_part because you are calling count. Instead of count, use max so that at least a date is being passed to date_part.
Instead of date_part(‘month’, date) you might also look at the Postgres function EXTRACT(DOW FROM some_date).
You wrote you want to have the count of users which were added for a month.
month here is just the variable for the month you want to query.
If you want the current month, as you wrote above, you can initialize month with month = NaiveDateTime.utc_now().month for example.
No. But I wanted to use one more fragment for weekday. So in that case it can be something like this
from r in Role,
where:
r.user_id == user_id and
fragment("EXTRACT(MONTH FROM ?)", r.inserted_at) == ^month,
select: %{users_for_month: count(r.user_id), users_for_weekday: count(r.user_id)}
Output will be something like this. It will be a list of this
[%{users_for_month: 5, users_for_weekday: 5}]
But now in this case it will still use the fragment which I’ve defined earlier.
I think you want to use date_trunc instead of date_part or extract, assuming you want 2021 October to not be treated the same as 2022 October. As far as querying for multiple counts in the same query, I think you’ll want to use filter:
from r in Role,
where: r.user_id == ^user_id,
select: %{
month_count: filter(count(r), fragment("date_trunc('month', ?) = date_trunc('month', current_timestamp)", r.inserted_at)),
week_count: filter(count(r), fragment("date_trunc('week', ?) = date_trunc('week', current_timestamp)", r.inserted_at))
}