Passing value of datatype interval in parametrized query

[yogesch]

I am trying to pass an interval as a query parameter. In the example query below, the table structure is simple - name is of type text, and creation_date is of type timestamp. So when I do something like

>pgo:query("select name from new_table where 
current_timestamp - creation_date < '6 days'"). 

it works nicely. But what I really want to do is get that value of 6 days from the server. So I try something like

>pgo:query("select name from new_table where
current_timestamp - timestamp < $1", ["6 days"]).

it throws an error. I tried '6 days', "'6 days'" and a few other concoctions, all throw errors.

The error message I get (when I pass "2 days" or "'2 days'"as the query parameter) looks like:

{error,{pgsql_error,#{code => <<"08P01">>,file => <<"pqformat.c">>,
                          line => <<"575">>,
                          message => <<"insufficient data left in message">>,
                          routine => <<"pq_copymsgbytes">>,severity => <<"ERROR">>,
                          {unknown,86} => <<"ERROR">>}}}

And when I pass '2 days' as the parameter, it throws a badarg error: {error,{badarg,'2 days'}}

Most likely, passing '2 days' is confusing to the parser since an Erlang atom is enclosed in single quotes, and a string gets passed as is but doesn't get typecast into type interval by postgres automatically. I am not sure if/how this should be tackled...

[yogesch]

One can achieve the desired objective by using a different type of query (as suggested in this SE answer https://dba.stackexchange.com/questions/208580/passing-value-of-datatype-interval-in-parametrized-query)

>pgo:query("select name from new_table
where creation_date > localtimestamp - interval '1 day' * $1", [2])

But that still leaves open the issue of (not) being able to pass an interval as a query parameter.

[tsloughter]

I'll investigate this today. Likely a format like {interval, Days, Months} or similar will be needed for the argument.

I want to replace the protocol handling with that of https://github.com/epgsql/epgsql so that types like this are supported without duplicating work.

[tsloughter]

Forgot to reply after looking into it further. It shouldn't be too bad to implement, I'l probably do that before having the time to switch to epgsql decode/encode functionality.

[yogesch]

If I may, I would like to suggest that switching out the back end to epgsql should be more meaningful than this formatting issue, because it could, potentially, subsume this problem. The query above that doesn't work is anyways suboptimal (since it leaves no scope to use indexes) and the working alternative is far better - from a database/efficiency perspective. Just my 2¢.

One could of course revisit this issue if it persists even after switching to epgsql. So if you don't disagree, I'm just going to close this issue; and we'll reopen it, if necessary, at a later date. Cheers