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.
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)`