You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by "Bulvik, Noam" <No...@teoco.com> on 2015/11/18 15:50:36 UTC
Trunc () on date questions
Hi,
In other implementations of SQL (like Oracle and impala) trunc() on date support also date parts higher than day level (for example WEEK, MONTH, YEAR) - any chance it can be supported also in phoenix ?
should I open JIRA for it?
Regards,
Noam
________________________________
PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and confidential, and is intended only for the use of the individual to whom it is addressed and others who have been specifically authorized to receive it. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, or if any problems occur with transmission, please contact sender. Thank you.
Re: Trunc () on date questions
Posted by James Taylor <ja...@apache.org>.
Yes, please file a JIRA.
On Wed, Nov 18, 2015 at 10:06 AM, Stephen Wilcoxon <wi...@gmail.com>
wrote:
> I think he's asking for trunc() to support the higher levels (not just
> ways to retrieve the higher level parts). Although, it's a little unclear
> to me exactly what the expected behavior is for a date(time) truncated to
> say month is (yyyy-mm-01, yyyy-mm-<last day>, or something else). I
> suspect it likely varies by implementation since (date_)trunc is not a
> standard function.
>
> On Wed, Nov 18, 2015 at 12:00 PM, Alok Singh <al...@cloudability.com>
> wrote:
>
>> Have you looked at supported date/time functions here:
>> https://phoenix.apache.org/language/functions.html
>> e.g. WEEK(timestamp_column) or WEEK(to_date('YYYY-mm-dd')) will give you
>> the week.
>>
>> Alok
>>
>> Alok
>>
>> alok@cloudability.com
>>
>> On Wed, Nov 18, 2015 at 6:50 AM, Bulvik, Noam <No...@teoco.com>
>> wrote:
>>
>>> Hi,
>>>
>>> In other implementations of SQL (like Oracle and impala) trunc() on date
>>> support also date parts higher than day level (for example WEEK, MONTH,
>>> YEAR) – any chance it can be supported also in phoenix ?
>>>
>>> should I open JIRA for it?
>>>
>>>
>>>
>>>
>>>
>>> Regards,
>>>
>>>
>>>
>>> *Noam *
>>>
>>>
>>>
>>> ------------------------------
>>>
>>> PRIVILEGED AND CONFIDENTIAL
>>> PLEASE NOTE: The information contained in this message is privileged and
>>> confidential, and is intended only for the use of the individual to whom it
>>> is addressed and others who have been specifically authorized to receive
>>> it. If you are not the intended recipient, you are hereby notified that any
>>> dissemination, distribution or copying of this communication is strictly
>>> prohibited. If you have received this communication in error, or if any
>>> problems occur with transmission, please contact sender. Thank you.
>>>
>>
>>
>
RE: Trunc () on date questions
Posted by "Bulvik, Noam" <No...@teoco.com>.
Thanks,
We are writing some logic in SQL as workaround it will be nicer if it was built in
From: James Taylor [mailto:jamestaylor@apache.org]
Sent: Thursday, November 19, 2015 10:03 AM
To: user <us...@phoenix.apache.org>
Subject: Re: Trunc () on date questions
Thanks for filing the JIRA, Noam. I can see the value in having these. FWIW, the reason we didn't implement these with the others is because they require using a calendar object, but since we're using Joda time now, perhaps this isn't such a big deal.
Another option would be for you to write a UDF[1].
James
[1] https://phoenix.apache.org/udf.html
On Wed, Nov 18, 2015 at 11:55 PM, Bulvik, Noam <No...@teoco.com>> wrote:
This is correct, I would like the function to trunc() or round() a date according to the requested unit and return the full date not only the date part.
This is very useful for example in aggregation on dates and for UI display.
You can see how it is documented for Oracle in http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm and different supported level in http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084 (I do not need all options but at least week/month/year will be nice)
I created https://issues.apache.org/jira/browse/PHOENIX-2433
From: Stephen Wilcoxon [mailto:wilcoxon@gmail.com<ma...@gmail.com>]
Sent: Wednesday, November 18, 2015 8:07 PM
To: user@phoenix.apache.org<ma...@phoenix.apache.org>
Subject: Re: Trunc () on date questions
I think he's asking for trunc() to support the higher levels (not just ways to retrieve the higher level parts). Although, it's a little unclear to me exactly what the expected behavior is for a date(time) truncated to say month is (yyyy-mm-01, yyyy-mm-<last day>, or something else). I suspect it likely varies by implementation since (date_)trunc is not a standard function.
On Wed, Nov 18, 2015 at 12:00 PM, Alok Singh <al...@cloudability.com>> wrote:
Have you looked at supported date/time functions here: https://phoenix.apache.org/language/functions.html
e.g. WEEK(timestamp_column) or WEEK(to_date('YYYY-mm-dd')) will give you the week.
Alok
Alok
alok@cloudability.com<ma...@cloudability.com>
On Wed, Nov 18, 2015 at 6:50 AM, Bulvik, Noam <No...@teoco.com>> wrote:
Hi,
In other implementations of SQL (like Oracle and impala) trunc() on date support also date parts higher than day level (for example WEEK, MONTH, YEAR) – any chance it can be supported also in phoenix ?
should I open JIRA for it?
Regards,
Noam
________________________________
PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and confidential, and is intended only for the use of the individual to whom it is addressed and others who have been specifically authorized to receive it. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, or if any problems occur with transmission, please contact sender. Thank you.
________________________________
PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and confidential, and is intended only for the use of the individual to whom it is addressed and others who have been specifically authorized to receive it. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, or if any problems occur with transmission, please contact sender. Thank you.
________________________________
PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and confidential, and is intended only for the use of the individual to whom it is addressed and others who have been specifically authorized to receive it. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, or if any problems occur with transmission, please contact sender. Thank you.
Re: Trunc () on date questions
Posted by James Taylor <ja...@apache.org>.
Thanks for filing the JIRA, Noam. I can see the value in having these.
FWIW, the reason we didn't implement these with the others is because they
require using a calendar object, but since we're using Joda time now,
perhaps this isn't such a big deal.
Another option would be for you to write a UDF[1].
James
[1] https://phoenix.apache.org/udf.html
On Wed, Nov 18, 2015 at 11:55 PM, Bulvik, Noam <No...@teoco.com>
wrote:
> This is correct, I would like the function to trunc() or round() a date
> according to the requested unit and return the full date not only the date
> part.
>
> This is very useful for example in aggregation on dates and for UI display.
>
>
>
> You can see how it is documented for Oracle in
> http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm
> and different supported level in
> http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084
> (I do not need all options but at least week/month/year will be nice)
>
>
>
> I created https://issues.apache.org/jira/browse/PHOENIX-2433
>
>
>
>
>
>
>
> *From:* Stephen Wilcoxon [mailto:wilcoxon@gmail.com]
> *Sent:* Wednesday, November 18, 2015 8:07 PM
> *To:* user@phoenix.apache.org
> *Subject:* Re: Trunc () on date questions
>
>
>
> I think he's asking for trunc() to support the higher levels (not just
> ways to retrieve the higher level parts). Although, it's a little unclear
> to me exactly what the expected behavior is for a date(time) truncated to
> say month is (yyyy-mm-01, yyyy-mm-<last day>, or something else). I
> suspect it likely varies by implementation since (date_)trunc is not a
> standard function.
>
>
>
> On Wed, Nov 18, 2015 at 12:00 PM, Alok Singh <al...@cloudability.com>
> wrote:
>
> Have you looked at supported date/time functions here:
> https://phoenix.apache.org/language/functions.html
>
> e.g. WEEK(timestamp_column) or WEEK(to_date('YYYY-mm-dd')) will give you
> the week.
>
>
>
> Alok
>
>
> Alok
>
>
>
> alok@cloudability.com
>
>
>
> On Wed, Nov 18, 2015 at 6:50 AM, Bulvik, Noam <No...@teoco.com>
> wrote:
>
> Hi,
>
> In other implementations of SQL (like Oracle and impala) trunc() on date
> support also date parts higher than day level (for example WEEK, MONTH,
> YEAR) – any chance it can be supported also in phoenix ?
>
> should I open JIRA for it?
>
>
>
>
>
> Regards,
>
>
>
> *Noam *
>
>
>
>
> ------------------------------
>
>
> PRIVILEGED AND CONFIDENTIAL
> PLEASE NOTE: The information contained in this message is privileged and
> confidential, and is intended only for the use of the individual to whom it
> is addressed and others who have been specifically authorized to receive
> it. If you are not the intended recipient, you are hereby notified that any
> dissemination, distribution or copying of this communication is strictly
> prohibited. If you have received this communication in error, or if any
> problems occur with transmission, please contact sender. Thank you.
>
>
>
>
>
> ------------------------------
>
> PRIVILEGED AND CONFIDENTIAL
> PLEASE NOTE: The information contained in this message is privileged and
> confidential, and is intended only for the use of the individual to whom it
> is addressed and others who have been specifically authorized to receive
> it. If you are not the intended recipient, you are hereby notified that any
> dissemination, distribution or copying of this communication is strictly
> prohibited. If you have received this communication in error, or if any
> problems occur with transmission, please contact sender. Thank you.
>
RE: Trunc () on date questions
Posted by "Bulvik, Noam" <No...@teoco.com>.
This is correct, I would like the function to trunc() or round() a date according to the requested unit and return the full date not only the date part.
This is very useful for example in aggregation on dates and for UI display.
You can see how it is documented for Oracle in http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm and different supported level in http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084 (I do not need all options but at least week/month/year will be nice)
I created https://issues.apache.org/jira/browse/PHOENIX-2433
From: Stephen Wilcoxon [mailto:wilcoxon@gmail.com]
Sent: Wednesday, November 18, 2015 8:07 PM
To: user@phoenix.apache.org
Subject: Re: Trunc () on date questions
I think he's asking for trunc() to support the higher levels (not just ways to retrieve the higher level parts). Although, it's a little unclear to me exactly what the expected behavior is for a date(time) truncated to say month is (yyyy-mm-01, yyyy-mm-<last day>, or something else). I suspect it likely varies by implementation since (date_)trunc is not a standard function.
On Wed, Nov 18, 2015 at 12:00 PM, Alok Singh <al...@cloudability.com>> wrote:
Have you looked at supported date/time functions here: https://phoenix.apache.org/language/functions.html
e.g. WEEK(timestamp_column) or WEEK(to_date('YYYY-mm-dd')) will give you the week.
Alok
Alok
alok@cloudability.com<ma...@cloudability.com>
On Wed, Nov 18, 2015 at 6:50 AM, Bulvik, Noam <No...@teoco.com>> wrote:
Hi,
In other implementations of SQL (like Oracle and impala) trunc() on date support also date parts higher than day level (for example WEEK, MONTH, YEAR) – any chance it can be supported also in phoenix ?
should I open JIRA for it?
Regards,
Noam
________________________________
PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and confidential, and is intended only for the use of the individual to whom it is addressed and others who have been specifically authorized to receive it. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, or if any problems occur with transmission, please contact sender. Thank you.
________________________________
PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and confidential, and is intended only for the use of the individual to whom it is addressed and others who have been specifically authorized to receive it. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, or if any problems occur with transmission, please contact sender. Thank you.
Re: Trunc () on date questions
Posted by Stephen Wilcoxon <wi...@gmail.com>.
I think he's asking for trunc() to support the higher levels (not just ways
to retrieve the higher level parts). Although, it's a little unclear to me
exactly what the expected behavior is for a date(time) truncated to say
month is (yyyy-mm-01, yyyy-mm-<last day>, or something else). I suspect it
likely varies by implementation since (date_)trunc is not a standard
function.
On Wed, Nov 18, 2015 at 12:00 PM, Alok Singh <al...@cloudability.com> wrote:
> Have you looked at supported date/time functions here:
> https://phoenix.apache.org/language/functions.html
> e.g. WEEK(timestamp_column) or WEEK(to_date('YYYY-mm-dd')) will give you
> the week.
>
> Alok
>
> Alok
>
> alok@cloudability.com
>
> On Wed, Nov 18, 2015 at 6:50 AM, Bulvik, Noam <No...@teoco.com>
> wrote:
>
>> Hi,
>>
>> In other implementations of SQL (like Oracle and impala) trunc() on date
>> support also date parts higher than day level (for example WEEK, MONTH,
>> YEAR) – any chance it can be supported also in phoenix ?
>>
>> should I open JIRA for it?
>>
>>
>>
>>
>>
>> Regards,
>>
>>
>>
>> *Noam *
>>
>>
>>
>> ------------------------------
>>
>> PRIVILEGED AND CONFIDENTIAL
>> PLEASE NOTE: The information contained in this message is privileged and
>> confidential, and is intended only for the use of the individual to whom it
>> is addressed and others who have been specifically authorized to receive
>> it. If you are not the intended recipient, you are hereby notified that any
>> dissemination, distribution or copying of this communication is strictly
>> prohibited. If you have received this communication in error, or if any
>> problems occur with transmission, please contact sender. Thank you.
>>
>
>
Re: Trunc () on date questions
Posted by Alok Singh <al...@cloudability.com>.
Have you looked at supported date/time functions here:
https://phoenix.apache.org/language/functions.html
e.g. WEEK(timestamp_column) or WEEK(to_date('YYYY-mm-dd')) will give you
the week.
Alok
Alok
alok@cloudability.com
On Wed, Nov 18, 2015 at 6:50 AM, Bulvik, Noam <No...@teoco.com> wrote:
> Hi,
>
> In other implementations of SQL (like Oracle and impala) trunc() on date
> support also date parts higher than day level (for example WEEK, MONTH,
> YEAR) – any chance it can be supported also in phoenix ?
>
> should I open JIRA for it?
>
>
>
>
>
> Regards,
>
>
>
> *Noam *
>
>
>
> ------------------------------
>
> PRIVILEGED AND CONFIDENTIAL
> PLEASE NOTE: The information contained in this message is privileged and
> confidential, and is intended only for the use of the individual to whom it
> is addressed and others who have been specifically authorized to receive
> it. If you are not the intended recipient, you are hereby notified that any
> dissemination, distribution or copying of this communication is strictly
> prohibited. If you have received this communication in error, or if any
> problems occur with transmission, please contact sender. Thank you.
>