How to do query using fragment for particular time?

I have a table for role. which has the following field

user_id
inserted_at
updated_at
name

I want to be able to query for a particular user_id and be able to check how many users have been created for weekdays?

So for this, I was trying something like this

from r in Role, where: r.user_id == user_id, select: %{users: "result for month"}

I don’t exactly know how to use fragments here which I think can do a job

I tried this so far

users: fragment("date_part('month', ?)", count(t.inserted_at))

but I’m getting this error

Postgrex.Error) ERROR 42883 (undefined_function) function date_part(unknown, bigint) does not exist

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).

I wanted to count how many users were added for this month. That’s why I wanted to use count. Let me check postgres functions

Maybe something like

from r in Role, 
where:
    r.user_id == user_id and 
        fragment("EXTRACT(MONTH FROM ?)", r.inserted_at) == ^month, 
select: %{users: count(r.user_id)}

I didn’t understand with “month” variable. Its not defined anywhere

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.

oh okay got it. Thanks

1 Like

I have one more question.

Can we not put this in select itself. Because If I need to add more fragments then I can directly use it in select?

I am not sure, I understand what you are trying to achieve.
Do you have a SQL query which gives you the desired output?

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))
}