Support of nanotime by RSQLite

I am looking into what is required to support nanotime objects in RSQLite queries. They are just integer64 wrappers.

Here is an example:

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:", bigint = "integer64")
ts <- nanotime::as.nanotime(Sys.time())

str(ts) # integer64 2023-06-04 17:30:21.669581000 

DBI::dbExecute(con, 'CREATE TABLE test (ts INTEGER);')
DBI::dbExecute(con, 'INSERT INTO test (ts) VALUES (:ts);', list('ts' = ts))

DBI::dbGetQuery(con, 'SELECT ts FROM test;') # returns 5.757609e-196
# This is not correct: the returned value should have been 1685899821669581000
# of type integer64.

What does it take to make RSQLite understand that nanotime is an integer64 and should be saved as such? Hence I expect the last query to return an integer64 object instead of a double.

I looked into DBI::dbDataType(), but I am not sure how to use it. My current approach is sending the parameters through a function that converts nanotime to integer64 before sending to RSQLite, but it would be nicer if this conversion was seamless (i.e. if it was done within RSQLite package or some RSQLite configuration where one could state that nanotime objects should be mapped to integer64).

Note: I am using RSQLite_2.3.1 and nanotime_0.3.7.

One option is to use bigint = "integer64" and to cast manually:

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:", bigint = "integer64")
ts <- nanotime::as.nanotime(Sys.time())

str(ts)
#> integer64 2023-06-05T04:40:53.451239936+00:00

tsi <- bit64::as.integer64(ts)
out <- DBI::dbGetQuery(con, "SELECT :ts AS x", list("ts" = tsi))
out$x
#> integer64
#> [1] 1685940053451239936
nanotime::as.nanotime(out$x)
#> [1] 2023-06-05T04:40:53.451239936+00:00

^{Created on 2023-06-05 with reprex v2.0.2}