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? -- View this message in context: http://r.789695.n4.nabble.com/Compare-date-Oracle-with-Sys-time-tp4635624.html Sent from the R help mailing list archive at Nabble.com.
Compare date Oracle with Sys.time
6 messages · cindy.dol, arun, Marc Schwartz +2 more
Hello, Not quite understand about ur problem.? Could you dput your data? The below should be general format to convert to class date ?now_7<-format(Sys.time()-(7*60*60*24), "%Y-%m-%d 00:00:00")
now_7
[1] "2012-06-29 00:00:00"
class(now_7)
[1] "character" now_7_new<-as.Date(Sys.time()-(7*60*60*24), format="%Y-%m-%d %H:%M:%S")
now_7_new
[1] "2012-06-29"
class(now_7_new)
[1] "Date" A.K. ----- Original Message ----- From: cindy.dol <cindy.dolomieu at insa-lyon.fr> To: r-help at r-project.org Cc: Sent: Friday, July 6, 2012 11:50 AM Subject: [R] Compare date Oracle with Sys.time 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? -- View this message in context: http://r.789695.n4.nabble.com/Compare-date-Oracle-with-Sys-time-tp4635624.html Sent from the R help mailing list archive at Nabble.com. ______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
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
Hello,
On further thinking about the issue, I guess this may be close to what you are looking for:
date1<-c("2012-06-12","2012-06-13","2012-06-14","2012-06-27","2012-07-01","2012-07-02","2012-07-03","2012-07-04")
?class(date1)
[1] "character"
#converting to date class
date2<-as.Date(date1,format="%Y-%m-%d")
class(date2)
[1] "Date"
date3<-as.data.frame(date2)
colnames(date3)<-"date"
difft<- difftime(Sys.time(),date3[,1])>=7
date3[difft,]
[1] "2012-06-12" "2012-06-13" "2012-06-14" "2012-06-27"
class(date3[difft,] )
[1] "Date" A.K. ----- Original Message ----- From: cindy.dol <cindy.dolomieu at insa-lyon.fr> To: r-help at r-project.org Cc: Sent: Friday, July 6, 2012 11:50 AM Subject: [R] Compare date Oracle with Sys.time 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? -- View this message in context: http://r.789695.n4.nabble.com/Compare-date-Oracle-with-Sys-time-tp4635624.html Sent from the R help mailing list archive at Nabble.com. ______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Yes, this request is woefully incomplete, but it looked to me like the question was how to select the data so that a limited amount of data could be imported, rather than loading the whole table and subsetting in R. If so, the discussion should be about constructing appropriate SQL (with or without parameters), which seems out of scope for this list (perhaps R-sig-DB?).
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---------------------------------------------------------------------------
Sent from my phone. Please excuse my brevity.
Marc Schwartz <marc_schwartz at me.com> wrote:
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
______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Yes, it's more like that, to limit the amout of data imported in R from Oracle.
----- Mail original -----
De: "Jeff Newmiller" <jdnewmil at dcn.davis.CA.us>
?: "Marc Schwartz" <marc_schwartz at me.com>, "cindy.dol" <cindy.dolomieu at insa-lyon.fr>
Cc: r-help at r-project.org
Envoy?: Vendredi 6 Juillet 2012 20:00:37
Objet: Re: [R] Compare date Oracle with Sys.time
Yes, this request is woefully incomplete, but it looked to me like the question was how to select the data so that a limited amount of data could be imported, rather than loading the whole table and subsetting in R. If so, the discussion should be about constructing appropriate SQL (with or without parameters), which seems out of scope for this list (perhaps R-sig-DB?).
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---------------------------------------------------------------------------
Sent from my phone. Please excuse my brevity.
Marc Schwartz <marc_schwartz at me.com> wrote:
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
______________________________________________ R-help at r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.