phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig Roberts <craig.robe...@frogasia.com>
Subject CONVERT_TZ for TIMESTAMP column
Date Fri, 02 Sep 2016 08:08:07 GMT
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