I'm struggling to filter a MonetDB table by a date. Here is an example:
library(MonetDBLite) library(DBI) library(dplyr) file_name <- "test.monet" con <- dbConnect(MonetDBLite(), dbname = file_name) df <- data.frame(x = seq.POSIXt(as.POSIXct("2018-10-01 00:00:00Z"), as.POSIXct("2018-10-31 00:00:00Z"), by = "day")) dbWriteTable(con, "test", df) filter_time <- as.POSIXct("2018-10-15 00:00:00Z", tz = "UTC") df_filtered <- tbl(con,"test") %>% filter(x > !! filter_time) %>% collect() dbDisconnect(con, shutdown=TRUE)
The error I get is:
Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT * FROM "test" WHERE ("x" > '2018-10-15T00:00:00Z')'. Server says 'SQLException:timestamp:22007!Timestamp (2018-10-15T00:00:00Z) has incorrect format'.
I've been trying various versions of my filter based on this link (mostly focusing on trying to re-format the R date into something Monet will like):
But so far, nothing has worked.
Has anyone had success with this?