2

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.

StatsStudent
  • 1,384
  • 2
  • 10
  • 28
  • Why not `sqldf('select x , y from mytime')`. Because you have problem with `times` as from `?sqldf` _(4) "name__class" which means that columns names that end in __class with two underscores where class is an R class (such as Date)..._ and `times` isn't an R class. – A. Suliman Aug 11 '19 at 00:31
  • I needed to use the variable renaming. I was hoping to do this in a single step (using `select varname as newvarname from table` instead of `newtable<-select varname from table` names(newtable)<-c("newvarname")`. G. Grothendieck's suggestion works though! Thank you! – StatsStudent Aug 11 '19 at 05:22

1 Answers1

4

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

G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • Excellent! Thank you for this. This works perfectly! But I think I still need to use name__class though right, since I need to preserve the date class? Thanks for adding this as a bug. – StatsStudent Aug 11 '19 at 05:22
  • With the default `method="auto"` it automatically converts the class of any column whose name is the same as an input column name so since `y` has `Date` class in the input it will automatically convert an output column named `y` to `Date` class. – G. Grothendieck Aug 11 '19 at 11:35
  • Ah. Right. I see what you mean now. Sorry for the confusion and thanks for all your help! – StatsStudent Aug 11 '19 at 19:29