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 >>>>> >>>> >>>> >>> >> >
