This seems like a really silly question, but I couldn't find anything about it
Suppose I have the following object calling a SQL connection
sql1 = na.omit(sqlQuery(con,paste("SELECT UNIX_TIMESTAMP(timestamp), rawPressure1, rawPressure2 FROM ",sensorname[[1]][1],"_Data WHERE timestamp > '2013-05-25 17:29:00' AND timestamp < '2013-06-10 15:33:00'", sep="")));
This statement works perfect grabs all the data I want ect. But now I want to automate this a little bit.
Suppose I have an object that compiles start and end times
> flowobs <- na.omit(read.csv('FlowObs.csv', header=TRUE))
Start Finish FlowObs Head
1 2013-05-25 17:29:00 2013-05-26 18:38:00 0.3307309 70.8750
2 2013-05-27 16:22:00 2013-05-28 20:15:00 0.3286909 72.1250
3 2013-05-29 13:05:00 2013-05-30 14:42:00 0.3211857 74.0000
4 2013-05-30 15:08:00 2013-06-03 11:54:00 0.3277443 75.0625
5 2013-06-05 12:13:48 2013-06-06 14:30:00 0.3490507 77.3125
Now lets make an object that grabs the range of the data set flowobs[[1]][1] (the start of the testing procedure) and then the end flowobs[[2]][length(flowobs[[2]])]
range <- c(paste(flowobs[[1]][1],sep = "'"),paste(flowobs[[2]][length(flowobs[[2]])],sep="'")
So here's where my problem comes in. Lets circle back to the SQL syntax.
...WHERE timestamp > '2013-05-25 17:29:00' AND timestamp < '2013-06-10 15:33:00'", sep="")));
The SQL syntax requires that the dates have back ticks surrounding the character string.
If we look at the structure of my object range you would expect that
...WHERE timestamp > ",range[[1]]," AND timestamp < ",range[[2]], sep="")));"
This should work just fine .... it does not. When we look at the elements of the range object we get this.
> range[[1]]
"2013-05-25 17:29:00"
NOT this
'2013-05-25 17:29:00'
Does anyone have an idea of how I can solve this issue?
Use this:
"...WHERE timestamp > '",range[[1]],"' AND timestamp < '",range[[2]],"'", sep="")));"