Thank you so much David! Your suggestions worked for me.
On Sun, Oct 9, 2016 at 11:16 AM, David Winsemius <dwinsem...@comcast.net> wrote: > >> On Oct 9, 2016, at 7:56 AM, Ashta <sewa...@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@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 > ______________________________________________ R-help@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.
