Data type datetime and timezone_ntz convert to character in dplyr-snowflakedb. Is this normal or a bug?

[harmverschuren]

I am integrating Snowflake and R via dplyr-snowflakedb package. Columns of data type datetime or timezone are being converted to character in R. I would expect the column to be converted to POSIXct.
Has anyone have run into this problem before? Is it a bug or feature/normal behaviour?

TIA.

Test case:

Create a table in snowflake, like this

`create table test
(date_time_1 timestamp_ntz
,date_time_2 datetime
);

insert into test values ('2019-08-05 10:05:00', '2019-08-05 10:05:10');
insert into test values ('2019-08-06 18:45:00', '2019-08-06 18:45:10');

commit;`

In RStudio, query the table. Following R-script provides a working example test script.

`##############################

local function, fill in your credentials

##############################
query_snowflake <- function(query) {
tryCatch({
message(paste("INFO - run Snowflake query:", query, sep = " "))
conn <- src_snowflakedb(
user = user,
password = password,
account = account,
region = region,
opts = list(
warehouse = warehouse,
db = database,
schema = schema
)
)
resultset <- dbSendQuery(conn$con, query)
ds <- dbFetch(resultset)
dbClearResult(resultset)
},
finally = dbDisconnect(conn$con)
)
return(ds)
}

##############################

environment

##############################
if (!require(RJDBC)) install.packages("RJDBC")
library(RJDBC)

if (!require(DBI)) install.packages("DBI")
library(DBI)

if (!require(rJava)) install.packages("rJava")
library(rJava)

if (!require(devtools)) install.packages("devtools")
library(devtools)

if (!require(dplyr.snowflakedb)) devtools::install_github("snowflakedb/dplyr-snowflakedb")
library(dplyr.snowflakedb)
options(dplyr.jdbc.classpath = "../snowflake-jdbc-3.6.28.jar")

library(dplyr)

##############################

Main.

##############################
ds <- query_snowflake("select * from test")

both attributes are character (chr)

str(ds)

expected:

ds$DATE_TIME_1 <- as.POSIXct(ds$DATE_TIME_1)
ds$DATE_TIME_2 <- as.POSIXct(ds$DATE_TIME_2)
str(ds)`

[rdatasculptor]

I encountered the same problem.