CODEWITHSUNDEEP
sqlrstatistics
Pratik Patil
Pratik Patil

Reputation: 3753

Apply SQL Query On a Data Frame in R

I have some data in my data frame with column names as ID, START_TIME,END_TIME,VALUE. All data is in character format. I want to fetch the selective data from a data frame for further processing by applying SQL query on it. My sql query is something like this:

QUERY <- "SELECT ID,START_TIME,END_TIME,VALUE FROM HISTORY WHERE START_TIME >= '2015-04-17 01:00:00' AND END_TIME <= '2015-04-17 02:00:00'"

Is it possible in R?

Upvotes: 1

Views: 5658

Answers (1)

Spacedman
Spacedman

Reputation: 94162

If your dates are in character format like that, then this example seems to work (although you'll get different dates because I've used Sys.time():

> require(sqldf)
> HISTORY=data.frame(ID=1:10,START_TIME=as.character(Sys.time()+(1:10)*100000))
> sqldf("select * from HISTORY where START_TIME > '2015-04-26 10:26:01'")
  ID          START_TIME
1  5 2015-04-26 10:28:03
2  6 2015-04-27 14:14:43
3  7 2015-04-28 18:01:23
4  8 2015-04-29 21:48:03
5  9 2015-05-01 01:34:43
6 10 2015-05-02 05:21:23

Just extend with your END_TIME as needed. Note that this can't do everything SQL can do, and selecting using native R date types is probably a better thing to do...

Upvotes: 4

Related Questions