How to add parameter in raw sql relating to interval?

Questions / Help
,
1

I have code like this:


    query = """
    select from_user_id, to_user_id from account_relationships ar
    where from_user_id =ANY($1)
    and to_user_id =ANY($2)
    and state in('avoid','block')
    and inserted_at <= (now() - interval '$3 hours')
    """

    results = Ecto.Adapters.SQL.query!(Repo, query, [player_ids, player_ids, minimum_time_hours])

The third parameter is a number. I get a postgres error.

2

Try to pass the whole interval as a parameter

and inserted_at <= (now() - interval $3)

results = Ecto.Adapters.SQL.query!(Repo, query, [player_ids, player_ids, "#{minimum_time_hours} hours"])
3

'$3 hours' this is a string literal for $3 hours. It won’t do any interpolation/parameterization. You can only use parameter placeholders in place of “whole values”.