R help - May 2012 - Query regarding date as argument in functions - and about sqldf

Hi,

I have a query about sqldf, and dates in general. I couldnt find much on
the net or on the forums, hence I am here. Here is the issue:

I want to write a function that accepts 3 arguments: date1, date2 and a
dataframe, say 'df'. Within the function, I want to populate a temp
dataframe which essentially contains the output of the query "select * from
df where DATE between date1 and date2". DATE is a column (of class Date)
which will be present in the input dataframe. This is how the function will
look like:

testfun = function("date1", "date2", df){
temp = sqldf("select * from df where DATE between 'date1' and
'date2'")
return(temp) #not sure if I'm doing this correctly
}

There are multiple issues here:

One is, how do I accept date as a parameter? Do I need to change the class
of date1 and date2 at input level or within the sql query? In general how
to format the input and how to return the temp dataframe once the function
ends, kindly help. It may be basic but I've spent hours on it with no
success.

The above was the bigger picture. Another issue that I faced is when
running a simple select (using sqldf) on a sample dataframe, I could not
get a result. But when I use 'subset', I get an output.

Below is the sample dataset named 'dlfhead':

structure(list(NAME = c("DLF", "DLF", "DLF",
"DLF", "DLF", "DLF"
), EXPDATE = structure(c(15001, 15001, 15001, 15001, 15001, 15001
), class = "Date"), STRIKE = c(280, 280, 280, 280, 280, 280),
    TMSTMP = c("14:54:17", "14:42:38", "14:09:04",
"14:20:25",
    "12:27:26", "11:59:36"), PRICE = c(22, 23, 23, 23, 23,
23.2
    ), DATE = structure(c(14977, 14977, 14977, 14977, 14977,
    14977), class = "Date"), TTE = c(24, 24, 24, 24, 24, 24),
    DTTM = structure(c(1294046657, 1294045958, 1294043944, 1294044625,
    1294037846, 1294036176), class = c("POSIXct",
"POSIXt")),
    SPOT = c(295, 294.5, 294.8, 293.85, 294.7, 294.55), MONEYNESS
c(1.05357142857143,
    1.05178571428571, 1.05285714285714, 1.04946428571429, 1.0525,
    1.05196428571429), TTEBY7 = c(4, 4, 4, 4, 4, 4), COMB
c("2011-01-034",
    "2011-01-034", "2011-01-034", "2011-01-034",
"2011-01-034",
    "2011-01-034"), RATE = c(7.55219988366433, 7.55260732560891,
    7.55302228192484, 7.55344470721609, 7.55387455629645, 7.82914555610361
    )), .Names = c("NAME", "EXPDATE", "STRIKE",
"TMSTMP", "PRICE",
"DATE", "TTE", "DTTM", "SPOT",
"MONEYNESS", "TTEBY7", "COMB",
"RATE"), row.names = c(NA, 6L), class = "data.frame")

Below are the commands and their outputs:
> dlftest = sqldf("select * from dlfhead where DATE =
'2011-01-03'")
> nrow(dlftest)
[1] 0

Kindly note that in the above, the query does complete successfully with
'dlftest' created with 0 rows. When I run the select on a column with
class
other than date I get the output.
> dlftest = sqldf("select * from dlfhead where TTE=24")
> nrow(dlftest)
[1] 6

But when I use the 'subset' command, I do get an answer:
> dlftest = subset(dlfhead, DATE == '2011-01-03')
> nrow(dlftest)
[1] 6

So the issue has to be the date-class columns in the dataframe, but what is
the issue I have no idea. Can anyone help please?

Thanks a lot for reading though this long email. Kindly reply if you have
an idea about either issue.

Regards,
Shivam

	[[alternative HTML version deleted]]

On Sat, May 12, 2012 at 6:18 PM, Shivam <shivamsingh at gmail.com>
wrote:
> Hi,
>
> I have a query about sqldf, and dates in general. I couldnt find much on
> the net or on the forums, hence I am here. Here is the issue:
>
> I want to write a function that accepts 3 arguments: date1, date2 and a
> dataframe, say 'df'. Within the function, I want to populate a temp
> dataframe which essentially contains the output of the query "select *
from
> df where DATE between date1 and date2". DATE is a column (of class
Date)
> which will be present in the input dataframe. This is how the function will
> look like:
>
> testfun = function("date1", "date2", df){
> temp = sqldf("select * from df where DATE between 'date1' and
'date2'")
> return(temp) #not sure if I'm doing this correctly
> }
See FAQ 4 on the sqldf home page. http://sqldf.googlecode.com


-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com