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