Hi James, I still don't understand the semantics of trunc function.
Here is what is displayed on sqlline select trunc(date,'DAY'), date from events limit 1; *+----------------------+--------------+* *| **FLOOR(TO_DATE(DATE))** | ** DATE ** |* *+----------------------+--------------+* *| *2013-07-14 * | *2013-07-15 00:01:02.346* |* *+----------------------+--------------+* Can you tell me why it returns '2013-07-14' rather than '2013-07-15' Thanks Sean On Tue, Feb 25, 2014 at 12:18 PM, James Taylor <[email protected]>wrote: > 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 >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >
