phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig Roberts <craig.robe...@frogasia.com>
Subject Re: CONVERT_TZ for TIMESTAMP column
Date Tue, 06 Sep 2016 02:05:03 GMT
Thanks for the quick response Ankit (and the patch, although I won't be
able to use it until Hortonworks update HDP). I've changed it to a DATE
field, and everything is working as expected.

*Craig Roberts*
*Senior Developer*

*FrogAsia Sdn Bhd (A YTL Company) *| Unit 9, Level 2, D6 at Sentul East |
801, Jalan Sentul, 51000 Kuala Lumpur | 01125618093 | Twitter
<http://www.twitter.com/FrogAsia> | Facebook
<http://www.facebook.com/FrogAsia> | Website <http://www.frogasia.com/>

*This message (including any attachments) is for the use of the addressee
only. It may contain private proprietary or legally privileged statements
and information. No confidentiality or privilege is waived or lost by any
mistransmission. If you are not the intended recipient, please immediately
delete it and all copies of it from your system, destroy any hard copies of
it and notify the sender. You must not, directly or indirectly, use,
disclose, distribute, print, copy or rely on any part of the message if you
are not the intended recipient. Any views expressed in this message
(including any attachments) are those of the individual sender and not
those of any member of the YTL Group, except where the message states
otherwise and the sender is authorized to state them to be the views of any
such entity.*

On Fri, Sep 2, 2016 at 9:17 PM, Ankit Singhal <ankitsinghal59@gmail.com>
wrote:

> Yes, it seems to be a bug as function signature in docs doesn't seems to
> match with the allowedTypes(only date) in the code.
> Filed Jira for the same(PHOENIX-3241)
>
> For now , you can use DATE datatype(time part is also maintained with
> date in Phoenix).
>
> For eg:-
> CREATE TABLE IF NOT EXISTS test (*"iso_8601" TIMESTAMP NOT NULL* PRIMARY
> KEY);
> upsert into test values(TO_DATE('2016-04-01 22:45:00'));
> select * from test;
> +--------------------------+
> |         iso_8601         |
> +--------------------------+
> | 2016-04-01 22:45:00.000  |
> +--------------------------+
>
>
> Regards,
> Ankit Singhal
>
>
>
>
> On Fri, Sep 2, 2016 at 1:38 PM, Craig Roberts <craig.roberts@frogasia.com>
> wrote:
>
>> Hi folks,
>>
>> I've been tripped up recently by Phoenix auto-converting to GMT (from
>> phoenix.query.dateFormatTimeZone), which I've now set to UTC. I'm on
>> Phoenix 4.4 in the Ambari/HDP stack.
>>
>> I'd like to do some retrieval and aggregation based on the client's local
>> time zone, so I'm using:
>>
>> *SELECT CONVERT_TZ("iso_8601", 'UTC', 'Asia/Kuala_Lumpur') FROM TEST
>> LIMIT 10;*
>>
>> But I'm getting:
>>
>>
>> *Error: ERROR 203 (22005): Type mismatch. expected: [DATE] but was:
>> TIMESTAMP at CONVERT_TZ argument 1*
>>
>> in Squirrel.
>>
>> My schema (cut down) is:
>>
>> *CREATE TABLE IF NOT EXISTS TEST(*
>> *  "iso_8601" TIMESTAMP NOT NULL*
>> *)*
>>
>> Just for reference: the time part is important. I don't just want to
>> convert 2016-01-01, I need to convert 2016-01-01 01:00:00 to the local
>> equivalent at 2016-01-01 09:00:00.
>>
>> The docs state timestampTerm is a valid argument, so am I missing
>> something obvious?
>>
>> The alternative is I set Phoenix to store all dates in my local timezone,
>> but I'd really rather store in UTC.
>>
>> Thanks for any help you can offer,
>> *Craig Roberts*
>> *Senior Developer*
>>
>> *FrogAsia Sdn Bhd (A YTL Company) *| Unit 9, Level 2, D6 at Sentul East
>> | 801, Jalan Sentul, 51000 Kuala Lumpur | 01125618093 | Twitter
>> <http://www.twitter.com/FrogAsia> | Facebook
>> <http://www.facebook.com/FrogAsia> | Website <http://www.frogasia.com/>
>>
>> *This message (including any attachments) is for the use of the addressee
>> only. It may contain private proprietary or legally privileged statements
>> and information. No confidentiality or privilege is waived or lost by any
>> mistransmission. If you are not the intended recipient, please immediately
>> delete it and all copies of it from your system, destroy any hard copies of
>> it and notify the sender. You must not, directly or indirectly, use,
>> disclose, distribute, print, copy or rely on any part of the message if you
>> are not the intended recipient. Any views expressed in this message
>> (including any attachments) are those of the individual sender and not
>> those of any member of the YTL Group, except where the message states
>> otherwise and the sender is authorized to state them to be the views of any
>> such entity.*
>>
>
>

Mime
View raw message