Are there SQL data types that do not work with R?

Question

I am trying to run sqlQuery in Rstudio, which seems to crash the program. I want to use the RODBC package to import a name called the package name and elapsed time from an Oracle database. When I try to execute sqlQuery, for example, the following

dataframe <- sqlQuery(channel, "select package_name, elapsed_time from fooSchema.barTable") 

When I run this only with the name package_name or other fields in the table, it works fine. If I try to run this using elapsed_time, RStudio will fail. The data type elapsed_time is INTERVAL DAY (3) TO SECOND (6), so one record, for example, looks like this: "+000 00: 00: 00.22723"

Are there certain data types, such as Interval Day to Second, from Oracle that don't work at all in RStudio or R?

Answer 1

The problem is not R, Rstudio, or even RODBC. The problem is that Oracle does not support interval data types for ODBC connections.

Section E.1

https://docs.oracle.com/cd/B28359_01/server.111/b32009/app_odbc.htm#CIHBFHCG

To get back to your question in a more general sense. Base R supports Date , POSIXct and POSIXlt .

The objects

Date and POSIXct are stored as the number of days / seconds, respectively, from 1/1/1970, while POSIXlt is a list of items.

No matter which SQL connector you use, you must force the SQL version of the date and time to one of the above. Sometimes it just converts to a string of characters. For example, using RPostgreSQL, columns that will be stored as the Postgre Date type as a character, but Postgres timestamp columns will be forced into POSIXct.