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):
https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/DateTimeFunctionality
But so far, nothing has worked.
Has anyone had success with this?