Prepared statement in R - dbSendQuery does not work with dates


#1

I am trying to write a prepared statement with dbSendQuery. My issue is that the data frame of inputs are converted to numeric values, but two of the three inputs are dates. This results in the following error message:

Warning: Error in postgresqlExecStatement: RS-DBI driver: (could not Retrieve the result : ERROR: invalid input syntax for type timestamp: "17624" )

My code is as follows:

query = dbSendQuery(con,"
                            SELECT

                            ***AL LOT OF TABLES AND JOINS***

                            WHERE

                            users_terminals.user_user_id = $1 and
                            planning_stops.planned_arrival >= $2 and
                            planning_stops.planned_arrival <= $3"
                            ,
                            data.frame(user$users_users_id,
                                          datefrom, 
                                          dateto))

        tmp = dbFetch(query)
        dbClearResult(query)

The numeric value of datefrom is 17624, so this make me think that $2 is replaced by as.numeric(datefrom) when I run the command. Also, user$users_users_id is a numeric value and I do not get an error for that one. Probably the whole data frame is converted to numeric.

I have created a workaround, but it is not an optimal situation and I would like to understand what happens here. The workaround I created is:

query = dbSendQuery(con,"
                            SELECT

                            ***AL LOT OF TABLES AND JOINS***

                            WHERE

                            users_terminals.user_user_id = $1 and
                            EXTRACT(day from planning_stops.planned_arrival - '1970-01-01') >= $2 and
                            EXTRACT(day from planning_stops.planned_arrival - '1970-01-01') <= $3"
                            ,
                            data.frame(user$users_users_id,
                                          datefrom, 
                                          dateto))

        tmp = dbFetch(query)
        dbClearResult(query) 

Could anyone help me out here? The workaround works for now, but it does not seem to be optimal.

Thanks.

UPDATE

I have read something about sqlInterpolate, so I thought let's give it a try. However, I still receive an error message:

Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: operator does not exist: timestamp without time zone >= integer LINE 57: ... planning_stops.planned_arrival >= 2018-04... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. )

My code is now:

 query = sqlInterpolate(con,"
                                SELECT

                                ***AL LOT OF TABLE AND JOINS***

                                WHERE

                                users_terminals.user_user_id = ?id1 and
                                planning_stops.planned_arrival >= ?date1 and
                                planning_stops.planned_arrival <= ?date2"
                                ,
                                id1 = user$users_users_id,
                                date1 = datefrom, 
                                date2 = dateto)

            tmp = dbGetQuery(con, 
                     query)

Still not working though.. It seems sqlInterpolate converts the inputs to integer.


#2

Here's the idea:
as per psql documentation we know that date is stored as 4B object while time as 8B. This indicates the underlying format is 4B integer for date format which would correspond to what we have in R.
psql has special type called epoch which is 1970-01-01 00:00:00+00
you can therefore calculate difference between your date column and epoch, then divide by number of seconds in a day and cast as integer:
SELECT (extract(epoch from now())/86400)::int;
now you can pass integer from R to query

I dont know however if this will be most optimal solution, maybe there is a way how to extract/cast 4B int directly from date bypassing the division and ::int. Still the alternative of using R to pass date as string would be less efficient i am afraid.
Note: if your column is date type still need to cast to int as the following returns double:

select extract(epoch from '2018-05-01'::date)

Rgds,
Peter