[R] Compare date Oracle with Sys.time

[Marc Schwartz]

On Jul 6, 2012, at 10:50 AM, cindy.dol wrote:

> I would like to import only datas of my table where DATE>today-7days.
> But my datas in Oracle are 'dates' and in R are 'characters'.
> now_7<-format(Sys.time()-(7*60*60*24), "%Y-%m-%d 00:00:00")
> How to do?


Huh? How are you getting the data from Oracle into R?

Typically Oracle DATE/TIMESTAMP data types come over as ?POSIXct in R, if using facilities such as RODBC, which means using a regular date based comparison. 

Use:

  str(DF)

where 'DF' is your data frame, to review the structure of it.

If your data frame is called DF and your date/time column is called MyDATE, you can use as.Date() to coerce both to ?Date class and then do the subtraction with days as the unit of measurement:

  NewDF <- subset(DF, as.Date(Sys.time()) - as.Date(MyDATE) < 7)

If for some reason, you are importing your data via another means into R, then you can still use ?as.Date to coerce the column to a Date class and use the above incantation.

See ?subset for additional information on that function.

Regards,

Marc Schwartz