I'm not sure how sqlline prints date fields by default. Can you do a TO_CHAR(date) with a format string instead for both dates?
The TRUNC function just truncates the date to a day boundary when you specify the 'DAY' argument. On Tue, Feb 25, 2014 at 12:59 PM, Sean Huo <[email protected]> wrote: > 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 >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >
