You'll get much better performance using the TRUNC function. See org.apache.phoenix.end2end.ProductMetricTest for some examples.
Thanks, James On Tue, Feb 25, 2014 at 12:11 PM, Sean Huo <[email protected]> wrote: > My usecase is simple. I have a event table that has timestamp as part of > the key. I want to do a event count per day > > I could do this in phoenix > > select to_char(ts,'yyyy-MM-dd') day, count(1) from events group by day; > > it returns event count per day per GMT time. > > Now if I am interested in doing a event count per PST timezone, > > That query doesn't work any more. > > The round about way to do this is to execute a query per day like this > > select count(1) from events where ts between to_date('2014-02-20 > PST','yyyy-MM-dd Z') and to_date('2014-02-21 13 PST','yyyy-MM-dd Z') > > I will look into trunc function. There is not much documentation and usage > on the function. > > Thanks > Sean > > > On Tue, Feb 25, 2014 at 11:53 AM, James Taylor <[email protected]>wrote: > >> Would it be possible to get a bit more info on your use case? Usually >> showing a date/time using a different timezone is a end-user display issue. >> How does this impact your group by? Grouping by a date/time will be the >> same regardless of the timezone you use to format your date. >> >> Do you know about our TRUNC and ROUND functions? >> http://phoenix.incubator.apache.org/language/functions.html#/truncate >> >> This is typically a good way to "bucketize" a date when you do a group >> by, like this: >> SELECT count(*) FROM t GROUP BY TRUNC(my_date,'DAY') >> >> You can use date arithmetic if you wanted to "shift" all the dates based >> on a timezone offset, like this (shifting 8 hours forward): >> SELECT count(*) FROM t GROUP BY TRUNC(my_date + 8.0/24.0,'DAY') >> >> HTH, >> James >> >> On Tue, Feb 25, 2014 at 11:41 AM, Sean Huo <[email protected]> wrote: >> >>> Well, I can not use to_date function since it expects a string input >>> while I have a timestamp. >>> Also doing is in java is not a solution since I want to do a group by on >>> the timestamp in a customized timezone. >>> >>> >>> >>> >>> On Tue, Feb 25, 2014 at 11:25 AM, James Taylor >>> <[email protected]>wrote: >>> >>>> Have you tried using the TO_DATE in conjunction with the TO_CHAR, where >>>> you specify a different timezone in the TO_DATE format_arg? >>>> >>>> Another option is to do this in Java. When you do a >>>> resultSet.getDate("MY_DATE_COL"), you can do whatever you want with the >>>> Date you get back. >>>> >>>> We're definitely open to taking contributions for new built-in >>>> functions. They're pretty easy to add. Just follow this guide: >>>> http://phoenix-hbase.blogspot.com/2013/04/how-to-add-your-own-built-in-function.html >>>> >>>> Adding more date manipulation functions would be much appreciated. >>>> >>>> Thanks, >>>> James >>>> >>>> >>>> On Tue, Feb 25, 2014 at 11:16 AM, Sean Huo <[email protected]>wrote: >>>> >>>>> Well, to be frankly, the example on the to_char udf is wrong >>>>> >>>>> TO_CHAR(myDate, '2001-02-03 04:05:06') >>>>> does not produce the right result and is misleading. >>>>> >>>>> This function does not give one the ability to format the date in a >>>>> customized timezone. >>>>> ALl it does is to allow timezone to be included in the output, but it >>>>> is is always GMT. >>>>> >>>>> >>>>> >>>>> >>>>> On Tue, Feb 25, 2014 at 11:04 AM, James Taylor <[email protected] >>>>> > wrote: >>>>> >>>>>> http://phoenix.incubator.apache.org/language/functions.html#/to_charwith >>>>>> a formatString argument. >>>>>> >>>>>> >>>>>> On Tue, Feb 25, 2014 at 10:58 AM, Sean Huo <[email protected]>wrote: >>>>>> >>>>>>> It seems that to_char udf always produces timestamp/date string in >>>>>>> GMT. >>>>>>> Is there a function that allows users to pass in a timezone string >>>>>>> so >>>>>>> that timestamp can be displayed accordingly? >>>>>>> >>>>>>> Thanks >>>>>>> >>>>>>> Sean >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>> >> >
