I am trying to query data from Hive service and create a variable. dbGetQuery(hivecon,"select date1, date2 from dateTable limit 10") date1, date2, Diif 4/5/1999, 6/14/2000 7/2/1999, 6/26/2000 8/14/1999, 8/19/2000 11/10/1999, 9/18/2000 8/25/2000, 6/5/2001 3/14/2012, 3/15/2004 Here is what I wanted to do. While I am querying I want create a variable diff= dat1e1-date2. I may use this variable "diff" to do some statistics (mean, mode, etc) and also in the where clause l like as the following. test_date=dbGetQuery(hivecon,"select date1, date2 from dateTable where diff gt 1000 limit 10") I would appreciate if you suggest me how to do this. Here is the sample of the data and result date1, date2, Diif 4/5/1999, 6/14/2000, -436 7/2/1999, 6/26/2000, -360 8/14/1999, 8/19/2000, -371 11/10/1999, 9/18/2000, -313 8/25/2000, 6/5/2001, -284 3/14/2012, 3/15/2004, 2921 Thank you in advance
I think you need to do some homework on your own first. Have you gone through any R tutorials? -- there are many good ones on the web. Including ones on date-time processing in R. To answer your question directly, read up on date/time classes and functions with ?"date-time" . You may also find the "lubridate" package useful, as it is intended to standardize and simplify typical date-time processing and analysis. Cheers, Bert Bert Gunter "The trouble with having an open mind is that people keep coming along and sticking things into it." -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) On Sun, Oct 9, 2016 at 7:56 AM, Ashta <sewashm at gmail.com> wrote:> I am trying to query data from Hive service and create a variable. > > > dbGetQuery(hivecon,"select date1, date2 from dateTable limit 10") > date1, date2, Diif > 4/5/1999, 6/14/2000 > 7/2/1999, 6/26/2000 > 8/14/1999, 8/19/2000 > 11/10/1999, 9/18/2000 > 8/25/2000, 6/5/2001 > 3/14/2012, 3/15/2004 > > > Here is what I wanted to do. While I am querying I want create a > variable diff= dat1e1-date2. > I may use this variable "diff" to do some statistics (mean, mode, > etc) and also in the where clause l like as the following. > > test_date=dbGetQuery(hivecon,"select date1, date2 from dateTable > where diff gt 1000 limit 10") > > I would appreciate if you suggest me how to do this. > > > > Here is the sample of the data and result > > date1, date2, Diif > 4/5/1999, 6/14/2000, -436 > 7/2/1999, 6/26/2000, -360 > 8/14/1999, 8/19/2000, -371 > 11/10/1999, 9/18/2000, -313 > 8/25/2000, 6/5/2001, -284 > 3/14/2012, 3/15/2004, 2921 > > Thank you in advance > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > 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 Oct 9, 2016, at 7:56 AM, Ashta <sewashm at gmail.com> wrote: > > I am trying to query data from Hive service and create a variable. > > > dbGetQuery(hivecon,"select date1, date2 from dateTable limit 10") > date1, date2, Diif > 4/5/1999, 6/14/2000 > 7/2/1999, 6/26/2000 > 8/14/1999, 8/19/2000 > 11/10/1999, 9/18/2000 > 8/25/2000, 6/5/2001 > 3/14/2012, 3/15/2004 > > > Here is what I wanted to do. While I am querying I want create a > variable diff= dat1e1-date2. > I may use this variable "diff" to do some statistics (mean, mode, > etc) and also in the where clause l like as the following. > > test_date=dbGetQuery(hivecon,"select date1, date2 from dateTable > where diff gt 1000 limit 10") > > I would appreciate if you suggest me how to do this. > > > > Here is the sample of the data and result > > date1, date2, Diif > 4/5/1999, 6/14/2000, -436 > 7/2/1999, 6/26/2000, -360 > 8/14/1999, 8/19/2000, -371 > 11/10/1999, 9/18/2000, -313 > 8/25/2000, 6/5/2001, -284 > 3/14/2012, 3/15/2004, 2921 > > Thank you in advance > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > 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.David Winsemius Alameda, CA, USA
> On Oct 9, 2016, at 7:56 AM, Ashta <sewashm at gmail.com> wrote: > > I am trying to query data from Hive service and create a variable. > > > dbGetQuery(hivecon,"select date1, date2 from dateTable limit 10") > date1, date2, Diif > 4/5/1999, 6/14/2000 > 7/2/1999, 6/26/2000 > 8/14/1999, 8/19/2000 > 11/10/1999, 9/18/2000 > 8/25/2000, 6/5/2001 > 3/14/2012, 3/15/2004 > > > Here is what I wanted to do. While I am querying I want create a > variable diff= dat1e1-date2. > I may use this variable "diff" to do some statistics (mean, mode, > etc) and also in the where clause l like as the following. > > test_date=dbGetQuery(hivecon,"select date1, date2 from dateTable > where diff gt 1000 limit 10") > > I would appreciate if you suggest me how to do this.Sorry for the blank message earlier. My reading of the use of Hive queries is that you would need to use the `datediff` function. I further suspect you need to define a variable name to which then apply your limits. I also read that hive dates are actually strings types represented as POSIX style character values and might need a to_date funciton. This is all guesswork since I don't have a hive cluster to run this against: So perhaps something like one of these: try1 <- dbGetQuery(hivecon,"select date1, date2, datediff(TO_DATE(date1),TO_DATE(date2)) as d12diff from dateTable where d12diff GT 1000 limit 10") try2 <- dbGetQuery(hivecon,"select date1, date2, datediff(dat1,date2) as d12diff from dateTable where d12diff GT 1000 limit 10") Obviously these are just guesses. -- David.> > > > Here is the sample of the data and result > > date1, date2, Diif > 4/5/1999, 6/14/2000, -436 > 7/2/1999, 6/26/2000, -360 > 8/14/1999, 8/19/2000, -371 > 11/10/1999, 9/18/2000, -313 > 8/25/2000, 6/5/2001, -284 > 3/14/2012, 3/15/2004, 2921 > > Thank you in advance > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > 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.David Winsemius Alameda, CA, USA
This being the R-help mailing list, not being fluent in random SQL variants is normal. There is a place for discussing the intersection of R and databases where the intersection of R with such knowledge might be more typical. I suggest that Ashta read the Posting Guide to learn about appropriate mailing lists and other useful background. If in fact this question is about R, then confusing the issue with SQL should not be necessary since a sample of the output of a database query can be provided as dput() output. If the question is purely about Hive then it doesn't belong on any R list anyway. -- Sent from my phone. Please excuse my brevity. On October 9, 2016 9:16:33 AM PDT, David Winsemius <dwinsemius at comcast.net> wrote:> >> On Oct 9, 2016, at 7:56 AM, Ashta <sewashm at gmail.com> wrote: >> >> I am trying to query data from Hive service and create a variable. >> >> >> dbGetQuery(hivecon,"select date1, date2 from dateTable limit 10") >> date1, date2, Diif >> 4/5/1999, 6/14/2000 >> 7/2/1999, 6/26/2000 >> 8/14/1999, 8/19/2000 >> 11/10/1999, 9/18/2000 >> 8/25/2000, 6/5/2001 >> 3/14/2012, 3/15/2004 >> >> >> Here is what I wanted to do. While I am querying I want create a >> variable diff= dat1e1-date2. >> I may use this variable "diff" to do some statistics (mean, mode, >> etc) and also in the where clause l like as the following. >> >> test_date=dbGetQuery(hivecon,"select date1, date2 from dateTable >> where diff gt 1000 limit 10") >> >> I would appreciate if you suggest me how to do this. > >Sorry for the blank message earlier. My reading of the use of Hive >queries is that you would need to use the `datediff` function. I >further suspect you need to define a variable name to which then apply >your limits. I also read that hive dates are actually strings types >represented as POSIX style character values and might need a to_date >funciton. This is all guesswork since I don't have a hive cluster to >run this against: > > So perhaps something like one of these: > >try1 <- dbGetQuery(hivecon,"select date1, date2, >datediff(TO_DATE(date1),TO_DATE(date2)) as d12diff from dateTable >where d12diff GT 1000 limit 10") > >try2 <- dbGetQuery(hivecon,"select date1, date2, datediff(dat1,date2) >as d12diff from dateTable where d12diff GT 1000 limit 10") > >Obviously these are just guesses.
Thank you so much David! Your suggestions worked for me. On Sun, Oct 9, 2016 at 11:16 AM, David Winsemius <dwinsemius at comcast.net> wrote:> >> On Oct 9, 2016, at 7:56 AM, Ashta <sewashm at gmail.com> wrote: >> >> I am trying to query data from Hive service and create a variable. >> >> >> dbGetQuery(hivecon,"select date1, date2 from dateTable limit 10") >> date1, date2, Diif >> 4/5/1999, 6/14/2000 >> 7/2/1999, 6/26/2000 >> 8/14/1999, 8/19/2000 >> 11/10/1999, 9/18/2000 >> 8/25/2000, 6/5/2001 >> 3/14/2012, 3/15/2004 >> >> >> Here is what I wanted to do. While I am querying I want create a >> variable diff= dat1e1-date2. >> I may use this variable "diff" to do some statistics (mean, mode, >> etc) and also in the where clause l like as the following. >> >> test_date=dbGetQuery(hivecon,"select date1, date2 from dateTable >> where diff gt 1000 limit 10") >> >> I would appreciate if you suggest me how to do this. > > Sorry for the blank message earlier. My reading of the use of Hive queries is that you would need to use the `datediff` function. I further suspect you need to define a variable name to which then apply your limits. I also read that hive dates are actually strings types represented as POSIX style character values and might need a to_date funciton. This is all guesswork since I don't have a hive cluster to run this against: > > So perhaps something like one of these: > > try1 <- dbGetQuery(hivecon,"select date1, date2, datediff(TO_DATE(date1),TO_DATE(date2)) as d12diff from dateTable where d12diff GT 1000 limit 10") > > try2 <- dbGetQuery(hivecon,"select date1, date2, datediff(dat1,date2) as d12diff from dateTable where d12diff GT 1000 limit 10") > > Obviously these are just guesses. > > -- > David. >> >> >> >> Here is the sample of the data and result >> >> date1, date2, Diif >> 4/5/1999, 6/14/2000, -436 >> 7/2/1999, 6/26/2000, -360 >> 8/14/1999, 8/19/2000, -371 >> 11/10/1999, 9/18/2000, -313 >> 8/25/2000, 6/5/2001, -284 >> 3/14/2012, 3/15/2004, 2921 >> >> Thank you in advance >> >> ______________________________________________ >> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >> 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. > > David Winsemius > Alameda, CA, USA >
Hi David and all,
I want run the following script in a loop but faced difficulty.
trt=c(1,2,2,4,5,6,7,8)
for(i in 1:length (trt))
{
try[i] <- (select trt, date1, date2, datediff(date1,date2) as
d12diff [i] from
dateTable where trt=[i]")
}
I would appreciate if you point me the problem.
Thank you in advance
On Sun, Oct 9, 2016 at 11:16 AM, David Winsemius <dwinsemius at
comcast.net> wrote:>
>> On Oct 9, 2016, at 7:56 AM, Ashta <sewashm at gmail.com> wrote:
>>
>> I am trying to query data from Hive service and create a variable.
>>
>>
>> dbGetQuery(hivecon,"select date1, date2 from dateTable limit
10")
>> date1, date2, Diif
>> 4/5/1999, 6/14/2000
>> 7/2/1999, 6/26/2000
>> 8/14/1999, 8/19/2000
>> 11/10/1999, 9/18/2000
>> 8/25/2000, 6/5/2001
>> 3/14/2012, 3/15/2004
>>
>>
>> Here is what I wanted to do. While I am querying I want create a
>> variable diff= dat1e1-date2.
>> I may use this variable "diff" to do some statistics (mean,
mode,
>> etc) and also in the where clause l like as the following.
>>
>> test_date=dbGetQuery(hivecon,"select date1, date2 from dateTable
>> where diff gt 1000 limit 10")
>>
>> I would appreciate if you suggest me how to do this.
>
> Sorry for the blank message earlier. My reading of the use of Hive queries
is that you would need to use the `datediff` function. I further suspect you
need to define a variable name to which then apply your limits. I also read that
hive dates are actually strings types represented as POSIX style character
values and might need a to_date funciton. This is all guesswork since I
don't have a hive cluster to run this against:
>
> So perhaps something like one of these:
>
> try1 <- dbGetQuery(hivecon,"select date1, date2,
datediff(TO_DATE(date1),TO_DATE(date2)) as d12diff from dateTable where d12diff
GT 1000 limit 10")
>
> try2 <- dbGetQuery(hivecon,"select date1, date2,
datediff(dat1,date2) as d12diff from dateTable where d12diff GT 1000 limit
10")
>
> Obviously these are just guesses.
>
> --
> David.
>>
>>
>>
>> Here is the sample of the data and result
>>
>> date1, date2, Diif
>> 4/5/1999, 6/14/2000, -436
>> 7/2/1999, 6/26/2000, -360
>> 8/14/1999, 8/19/2000, -371
>> 11/10/1999, 9/18/2000, -313
>> 8/25/2000, 6/5/2001, -284
>> 3/14/2012, 3/15/2004, 2921
>>
>> Thank you in advance
>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> 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.
>
> David Winsemius
> Alameda, CA, USA
>