I am working in R 4.1.2 with duckdb v0.7.1.
I have had good success using duckdb to store my data but I have run into one stumbling block.
One of my columns holds POSIXct values. I can write and read this from rds and it keeps the subsecond portion of the time.
> format(timeFromRds, '%Y-%m-%d %H:%M:%OS6')
[1] "2017-08-11 00:01:17.802407"
When I write and read this from a duckdb file, the subsecond portion of the time is lost.
> format(timeFromDuckDb, '%Y-%m-%d %H:%M:%OS6')
[1] "2017-08-11 00:01:17.000000"
When I open the duckdb file in the windows CLI, I can see that it is stored as type timestamp, which according to the duckdb 0.7.1 documentation should be able to store the subsecond data:
From https://web.archive.org/web/20230314185132/https://duckdb.org/docs/sql/data_types/timestamp
A timestamp specifies a combination of DATE (year, month, day) and a TIME (hour, minute, second, millisecond). Timestamps can be created using the TIMESTAMP keyword, where the data must be formatted according to the ISO 8601 format (YYYY-MM-DD hh:mm:ss[.zzzzzz][+-TT[:tt]]).
I am reading and writing to the duckdb file using the DBI interface:
duckCon <- dbConnect(duckdb::duckdb(dbPath))
on.exit(dbDisconnect(duckCon, shutdown = TRUE))
...
dbWriteTable(duckCon, "duck", dataTable, append = TRUE)
Does anyone else have any experience with this?
Is this something that is handled properly by later versions of the duckdb package?
I have tried installing the 0.10 version of duckdb, but it is not available on CRAN for R 4.1.2, and I get build errors when I try to build from source. I am locked in to R 4.1.2 on windows, unfortunately, and haven't been able to locate any newer pre-built duckdb packages for this version of R.