WHERE creation_date::timestamp::date > (CURRENT_DATE - interval '30 days')::date

[BeeRich33]

Hi folks.  Back again, asking for more guidance on typemaps.  Lovely typemaps.

So I'm getting a type error on this:

    WHERE creation_date::timestamp::date > (CURRENT_DATE - interval '30 days')::date 

Here's the error:

    invalid input syntax for type interval: "$1::Integer days" (PG::InvalidDatetimeFormat)

    LINE 3: ..._date::timestamp::date > (CURRENT_DATE - interval '$1::Integ...

                                                                                              ^

Essentially it's a date > date comparator.  Here's my variable and type map declaration:

n = 3

tm = PG::TypeMapByColumn.new([

  PG::TextEncoder::Integer.new

  ])

Simple date range I'm trying to grab.  

Any help appreciated as to what I'm doing wrong.  Not too many examples online about this.  

Cheers

[Lars Kanis]

Hi,

you could do something like this:

c.exec_params("SELECT now() + ($1||'days')::interval", [7]).values
 => [["2019-04-25 10:11:44.341146+02"]]

But this is what make_interval() is made for. See https://www.postgresql.org/docs/11/functions-datetime.html for more information:

c.exec_params("SELECT now() + make_interval(days => $1)", [7]).values
 => [["2019-04-25 10:10:49.289482+02"]]

You don't need a typemap in case of integers, since Integer#to_s converts them into a valid PostgreSQL string of INT. But using a typemap makes the conversion slightly faster when importing millions of integers.

--

Kind Regards,

Lars

Am 17.04.19 um 23:54 schrieb BeeRich33:

--
You received this message because you are subscribed to the Google Groups "ruby-pg" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ruby-pg+u...@googlegroups.com.
To post to this group, send email to rub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ruby-pg/ed8f0af6-ee73-4ccc-b33a-335f35bcfc89%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[BeeRich33]

So params aren't directly translated like string interpolation in Ruby.  I see.  I'm guessing the single quotes screw that up and treat it as a literal.  

Thank you