I am using sqldf to join several tables, but I am having trouble preserving the times class set with the chron package on a column. I used the method="name__class" argument to the sqldf function and appropriately named my column with the class, but my times variables are not preserved after selection with sqldf.
Is it possible to select a times class column and preserve the class or will I have to reset the class after the SQL selection (which is not ideal). I’ve included a toy example that shows how sqldf preserves Date classes, but not the times class:
library(chron)
mytime = data.frame(x=times(c("11:45:00", "12:15:00")))
mytime$y = as.Date(c("2019-09-01", "2019-09-11"))
mytime
x y
1 11:45:00 2019-09-01
2 12:15:00 2019-09-11
class(mytime$x)
[1] "times"
class(mytime$y)
[1] "Date"
sqldf('select x as x__times, y as y__Date from mytime', method = "name__class")
x__times y
1 0.4895833 2019-09-01
2 0.5104167 2019-09-11
Thanks in advance for your help.
Advertisement
Answer
sqldf looks for as.X to convert to class X but there is no as.times so it assumes that times is not a class and x__times is the actual name you wanted to use.
To work around this define as.times:
as.times <- times
method=”auto”
Also, if you add an as.times definition then, as @A. Suliman points out in the comments, you don’t actually need to use name__class in this instance as the default method="auto" already automatically converts any output column having the same name as an input column to that input column’s class.
RH2
The H2 database, unlike SQLite, does support date and time classes so if you use the H2 database backend, RH2, then you don’t need to define as.times.
library(RH2)
library(sqldf)
sqldf("select x, y from mytime")
If you want to go back to SQLite be sure that RH2 is detached first since it will assume you want it if it is loaded unless you specifically specify SQLite as per ?sqldf .
New issue
This has been added as a new issue https://github.com/ggrothendieck/sqldf/issues/36