Query with time_bucket

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,
          {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])


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 = """
      time_bucket($1, inserted_at) AS my_time_bucket,
      count(id) AS count,
      $1 AS interval
      inserted_at >= $2 AND inserted_at <= $3
    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.

The query is expecting the interval not to be a string interval_str, but to be a struct of type Postgex.Interval.

Users.get_users_count(%Postgrex.Interval{days: 7}, "2020-01-01", "2023-05-03")

should work.


You’re right! It works as it should. Thank you!