I am trying run an sqlQuery in Rstudio which seems to crash the program. I want to use the RODBC package to import a name called package name and elapsed time from a Oracle database. When I try to do an sqlQuery such as the following
dataframe <- sqlQuery(channel,
"select package_name, elapsed_time from fooSchema.barTable")
When I run this with just the package_name or other fields in the table, it works fine. If I try to run this with the elapsed_time, RStudio crashes. The datatype of 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 in RStudio or R in general?
DECIMAL is not a valid SQL type because it is nothing but numeric only in SQL. NUMERIC has fixed precision, and scale numbers range from -10^38+1 to 10^38-1.
You can get the MySQL table columns data type with the help of “information_schema. columns”. SELECT DATA_TYPE from INFORMATION_SCHEMA. COLUMNS where table_schema = 'yourDatabaseName' and table_name = 'yourTableName'.
In MySQL there are three main data types: string, numeric, and date and time.
The problem isn't R, Rstudio, or even RODBC. The problem is that Oracle doesn't support interval data types for ODBC connections.
It is under 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
objects.
Date
s and POSIXct
objects are stored as the number of days/seconds respectively since 1/1/1970 whereas POSIXlt
is a list of elements.
Whatever SQL connector you're using will need to coerce the SQL version of a date and time into one of the above. Sometimes it'll just convert to a character string. For instance with RPostgreSQL it'll take columns stored as Postgre's Date
type as a character but Postgres timestamp
columns will be coerced into POSIXct directly.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With