Filter MonetDB by date?

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?

The issue seems to be MonetDB does not support the 'Z' timezone format you are trying to supply it. (https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/DateTimeFunctionality)

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)

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.