Filter MonetDB by date?

[ldecicco]

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?

[jimhester]

@ldecicco

df_filtered <- tbl(con,"test") %>% filter(x > !! filter_time) %>% collect() dbDisconnect(con, shutdown=TRUE)

The issue seems to be MonetDB does not support the 'Z' timezone format you are trying to supply it. (Documentation | MonetDB Docs)

One easy way to fix this is to call format() on the datetime object you are using to filter.

library(MonetDBLite)
library(DBI)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

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 <- format(as.POSIXct("2018-10-15 00:00:00Z", tz = "UTC"))

tbl(con,"test") %>%
  filter(x > filter_time) %>%
  collect()
#> # A tibble: 17 x 1
#>    x                  
#>    <dttm>             
#>  1 2018-10-15 04:00:00
#>  2 2018-10-16 04:00:00
#>  3 2018-10-17 04:00:00
#>  4 2018-10-18 04:00:00
#>  5 2018-10-19 04:00:00
#>  6 2018-10-20 04:00:00
#>  7 2018-10-21 04:00:00
#>  8 2018-10-22 04:00:00
#>  9 2018-10-23 04:00:00
#> 10 2018-10-24 04:00:00
#> 11 2018-10-25 04:00:00
#> 12 2018-10-26 04:00:00
#> 13 2018-10-27 04:00:00
#> 14 2018-10-28 04:00:00
#> 15 2018-10-29 04:00:00
#> 16 2018-10-30 04:00:00
#> 17 2018-10-31 04:00:00

dbDisconnect(con, shutdown=TRUE)

^{Created on 2018-11-08 by the reprex package (v0.2.1)}