Hey folks, I am working on a query to get the count of users who joined an app with grouping by the interval.
I have a function:
def get_users_count(interval_str, start_date, end_date) do
parsed_start_date = Timex.parse!(start_date, "%Y-%m-%d", :strftime)
parsed_end_date = Timex.parse!(end_date, "%Y-%m-%d", :strftime)
query =
from(u in User,
select:
{fragment("time_bucket(?, ?)", ^interval_str, u.inserted_at), count(u.id)},
where: u.inserted_at >= ^parsed_start_date and u.inserted_at <= ^parsed_end_date,
group_by: [fragment("time_bucket(?, ?)", ^interval_str, u.inserted_at), u.inserted_at])
Repo.all(query)
end
I call it this way:
Users.get_users_count("1 week", "2020-01-01", "2023-05-03")
And this returns an error:
** (DBConnection.EncodeError) Postgrex expected %Postgrex.Interval{}, got "1 week". Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
(postgrex 0.17.0) lib/postgrex/type_module.ex:947: Postgrex.DefaultTypes.encode_params/3
(postgrex 0.17.0) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
(db_connection 2.5.0) lib/db_connection.ex:1323: DBConnection.encode/5
(db_connection 2.5.0) lib/db_connection.ex:1423: DBConnection.run_prepare_execute/5
(db_connection 2.5.0) lib/db_connection.ex:1527: DBConnection.run/6
(db_connection 2.5.0) lib/db_connection.ex:656: DBConnection.parsed_prepare_execute/5
(db_connection 2.5.0) lib/db_connection.ex:648: DBConnection.prepare_execute/4
(ecto_sql 3.10.1) lib/ecto/adapters/postgres/connection.ex:70: Ecto.Adapters.Postgres.Connection.prepare_execute/5
(ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:836: Ecto.Adapters.SQL.execute!/5
(ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:828: Ecto.Adapters.SQL.execute/6
(ecto 3.10.0) lib/ecto/repo/queryable.ex:229: Ecto.Repo.Queryable.execute/4
(ecto 3.10.0) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3```
I also tried something like that:
sql = """
SELECT
time_bucket($1, inserted_at) AS my_time_bucket,
count(id) AS count,
$1 AS interval
FROM
users
WHERE
inserted_at >= $2 AND inserted_at <= $3
GROUP BY
my_time_bucket
"""
Ecto.Adapters.SQL.query(Repo, sql, [interval_str, parsed_start_date, parsed_end_date])
But it returns the same error.
I run this query in psql console and it works:
SELECT time_bucket('1 second', u.inserted_at) AS one_sec, count(id)
FROM users AS u
WHERE u.inserted_at > '2016-01-02' and u.inserted_at < '2023-05-04'
GROUP BY one_sec
ORDER BY one_sec;
I don’t even know which direction I should go now to fix this. I would appreciate any hint how to solve this.
