1

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.

Davor Josipovic
  • 5,296
  • 1
  • 39
  • 57

1 Answers1

1

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

krlmlr
  • 25,056
  • 14
  • 120
  • 217
  • From your answer, I see now that my question wasn't clear. My question is more about **storing** `nanotime` seamlessly as `INTEGER` in SQLite. The example with the SELECT statement is just to show how RSQLite maps the `nanotime` internally, which is currently as `REAL`, and that is not good. – Davor Josipovic Jun 05 '23 at 12:56
  • I don't follow, can you please rephrase? AFAICT, the data is stored as 64-biit integer in the SQLite database. – krlmlr Jun 24 '23 at 04:36
  • it is not stored as 64bit integer without explicit conversion. I have updated the question. It should be clear now. – Davor Josipovic Jun 26 '23 at 20:55
  • I think you are not appreciating the difference in range between `int` and `bigint`. While Kirill was preparing his answer, I did the same based on casual googling which lead to `(ts BIGINT)` (and his use of `integer64` is nicer) as the type and the same explicit cast (because `nanotime` by default goes a different route that is the one useful in its context for R). It have me `1.68781e+18` as desired. Using `integer64` has the added benefit of coming out as integer (rather than cast to double, which is lossy) as you desired. So this is your answer. I'll upvote it. – Dirk Eddelbuettel Jun 26 '23 at 21:01
  • @DirkEddelbuettel, krlmlr, no guys, you are completely missing my point :D. If `bit64` is available, then it is used [by default](https://rsqlite.r-dbi.org/reference/sqlite). I already assume `bit64`. My question is about how to configure `RSQLite` to _not have to_ "cast manually", as krlmlr suggests. I have updated my question with krlmlr suggestion, and it clearly shows that `nanotime` by default is saved as `REAL` and not as `INTEGER` (== 64bit INT in sqlite). What I want is `RSQLite` to use `INTEGER` every time it sees `nanotime`, just as it uses `INTEGER` every time it sees `integer64`. – Davor Josipovic Jun 27 '23 at 18:02
  • I believe you are missing our point. (And I happen to be maintainer / co-author of `nanotime` so I have some ideas of how it casts and why.) What Kirill suggested is good. But you don't have to trust us, but if you don't, then you have some programming to do. – Dirk Eddelbuettel Jun 27 '23 at 18:21
  • @DirkEddelbuettel, I know you created many great R tools, and I use them daily, including `nanotime`. I appreciate you being here too. But it is not about trust, it is about logic-disconnect. What Kirill suggests I already do (as noted in my original question). I merely wonder whether it can be improved, and how. – Davor Josipovic Jun 27 '23 at 18:41
  • When the value is stored in the database, it is no longer a nanotime. This information is lost, and can't be retrieved on the way back (from SQLite to R). – krlmlr Jul 01 '23 at 20:39