phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ankit Singhal <ankitsingha...@gmail.com>
Subject Re: CONVERT_TZ for TIMESTAMP column
Date Fri, 02 Sep 2016 13:17:35 GMT
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