[R] create variable

[David Winsemius]

> 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