R › R help

Compare date Oracle with Sys.time

Classic

List

Threaded

6 messages Options

cindy.dol

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?

arun kirshna

Re: Compare date Oracle with Sys.time

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 <[hidden email]>
To: [hidden email]
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.

______________________________________________
[hidden email] 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.

______________________________________________
[hidden email] 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.

Marc Schwartz-3

Re: Compare date Oracle with Sys.time

In reply to this post by cindy.dol

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

______________________________________________
[hidden email] 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.

arun kirshna

Re: Compare date Oracle with Sys.time

In reply to this post by cindy.dol

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 <[hidden email]>
To: [hidden email]
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.

______________________________________________
[hidden email] 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.

______________________________________________
[hidden email] 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.

Jeff Newmiller

Re: Compare date Oracle with Sys.time

In reply to this post by Marc Schwartz-3

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:<[hidden email]> 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 <[hidden email]> 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
>
>______________________________________________
>[hidden email] 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.

______________________________________________
[hidden email] 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.

cindy.dol

Re: Compare date Oracle with Sys.time

Yes, it's more like that, to limit the amout of data imported in R from Oracle.

----- Mail original -----
De: "Jeff Newmiller" <[hidden email]>
À: "Marc Schwartz" <[hidden email]>, "cindy.dol" <[hidden email]>
Cc: [hidden email]
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:<[hidden email]> 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 <[hidden email]> wrote: