0

I have two columns in a data frame where I need to calculate the difference in time. The data is the local PD data from open data of cities. One column is below

TimeDispatch             
01/01/2011 12:00:37 AM   

TimeArrive
01/01/2011 12:21:31 AM

Also the TimeArrival as a few missing values as every dispatch does not need the cops to arrive.

I am trying to find the difference using sqldf in R but it says this error

Error in sqliteSendQuery(conn, statement, bind.data) : 
  RAW() can only be applied to a 'raw', not a 'double'
In addition: Warning message:
In sqliteSendQuery(con, statement, bind.data) :
  Closing result set with pending rows

Any help guys?

Improve this question
5
  • Is it necessary to do it with the help of sqldf? Commented Apr 18, 2016 at 3:36
  • Can you please post the query you are using? Commented Apr 18, 2016 at 4:54
  • total$TimeDispatched <- as.POSIXlt(strptime(total$TimeDispatch, format= '%m/%d/%y %H:%M:%S')) total$TimeArrived <- as.POSIXlt(strptime(total$TimeArrive, format= '%m/%d/%y %H:%M:S')) total$diff <- difftime(total$TimeDispatched, total$TimeArrived, units = c(auto)) q2 <- sqldf('select avg(diff) as Avg_Arrival_time from total where diff is not null group by PoliceDistrict') The issue is that I need to later on group them with SQL and sqldx is not good with POSIXct date Commented Apr 18, 2016 at 4:56
  • You can also group them without using sqldf as well. Commented Apr 18, 2016 at 5:11
  • Thank you Kunal, I actually solved it. It was actually simple. After the conversion, I just used an as.integer function and then used the group by. Commented Apr 18, 2016 at 5:29

2 Answers 2

Reset to default
3

From the comments to the question, the problem is not how to calculate the difference bewteen two times using sqlite. The times have already been differenced before sending them to sqlite and the problem is that the resulting "difftime" class column is converted to numeric when it is sent to sqlite and when it is retrieved back to R, sqldf does not know how to convert that number back to a "difftime" class object because it does not know which units to use.

Here is a self contained example to illustrate:

library(sqldf)

now <- Sys.time()
now2 <- now + 1
dif <- difftime(now2, now)
DF <- data.frame(dif)

sqldf("select * from DF")
## Error in asfn(rs[[i]]) : need explicit units for numeric conversion

There are several approaches to this:

1) do not use a "difftime" object in the first place. Use the number of seconds or minutes or whatever as a numeric variable:

DF1 <- data.frame(dif = as.numeric(dif))
sqldf("select * from DF1")

##   dif
## 1   1

2) perform the differencing in SQL rather than in R so that a "difftime" column is not created in the first place:

DF2 <- data.frame(now, now2)
sqldf("select now2 - now as dif from DF2")

##   dif
## 1   1

3) use sqldf(..., method = "raw") to prevent it from trying to convert back to "difftime" class:

sqldf("select * from DF")

##   dif
## 1   1

4) make sure that the original "difftime" column is renamed in the output so that it cannot associate it with the original "difftime" column and so the heuristic that assigns classes will not try to convert it.

sqldf("select dif as dif2 from DF")

##   dif2
## 1    1

5) Use the name__class method of sqldf (note the double underscore to specify the class to convert to:

sqldf("select dif as dif__numeric from DF", method = "name__class")

##   dif
## 1   1
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

I actually found the answer. Just convert the difftime variable to an integer using as.integer(). I read numerous blogs and I could not find the answer anywhere so I am posting the answer for future reference

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.