phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: timezone offset
Date Tue, 25 Nov 2014 01:18:44 GMT
Thanks, Thomas & Noam - that's very useful info. If you wouldn't mind
filing a JIRA, that'd be much appreciated. Of course, patches are welcome
as well.

    James

On Wed, Nov 19, 2014 at 10:15 AM, Bulvik, Noam <Noam.Bulvik@teoco.com>
wrote:

>  Thanks for the detailed info,
> I think that Poenix should support disabling timezone conversion in the
> connection string, or disable it on their implementation.
> I use Oracle and impala and both of them does not do that.
>
> -----Original Message-----
> *From:* Thomas Grayson [tgrayson@bluemetal.com]
> *Received:* רביעי, 19 נוב 2014, 18:53
> *To:* user@phoenix.apache.org [user@phoenix.apache.org]
> *Subject:* RE: timezone offset
>
>   Noam,
>
>
>
> The Phoenix JDBC driver converts date and timestamp columns to the local
> time zone of the client’s JVM.  However, the TO_DATE and TO_CHAR functions
> explicitly force the time zone to be UTC.  In your example, the timestamp
> was inserted as UTC via TO_DATE and retrieved in UTC by TO_CHAR.  In
> contrast, the TS column was converted to the local time zone (UTC+05:00 in
> your case).  Hence, you have two choices for retrieving the date in UTC in
> SQuirreL:
>
>
>
> -  Always use TO_DATE when querying these columns from the table.  This is
> inconvenient for SELECT * queries, as you’ve discovered.
>
> -  Have SQuirreL run in the UTC time zone by setting the
> “-Duser.timezone=UTC” JVM parameter.  This is the route I have chosen.  I
> updated the “start” command in the squirrel.bat file to use this parameter,
> as shown below:
>
>
>
> start "SQuirreL SQL Client" /B "%LOCAL_JAVA%" -Xmx1024m
> -Duser.timezone=UTC -Dsun.java2d.noddraw=true -cp %SQUIRREL_CP%
> -splash:"%SQUIRREL_SQL_HOME%/icons/splash.jpg"
> net.sourceforge.squirrel_sql.client.Main %TMP_PARMS%
>
>
>
> SQuirrel conveniently displays the time with the time zone in the lower
> right corner of its window, so it is easy to confirm what time zone
> SQuirrel’s JVM is using.
>
>
>
> I’ll add that in Phoenix the java.sql.ResultSet.getString method returns
> UTC, not local time, at least for UNSIGNED_TIMESTAMP columns.
>
>
>
> Some JDBC drivers, such as MySQL’s, have configuration properties that
> allow one to work around these time zone issues by configuring the
> connection.  To my knowledge, Phoenix has no such options.  According to
> this StackOverflow post, the handling of time zones is up to the JDBC
> implementation:
>
>
>
> http://stackoverflow.com/a/9305468
>
>
>
> For what it’s worth I posted a similar question back in August to which no
> one replied:
>
>
>
>
> http://mail-archives.apache.org/mod_mbox/phoenix-user/201408.mbox/%3C04914161c47d4d9b9d6103c59de80b2f%40BL2PR05MB193.namprd05.prod.outlook.com%3E
>
>
>
> Tom Grayson
>
>
>
> *From:* Bulvik, Noam [mailto:Noam.Bulvik@teoco.com]
> *Sent:* Thursday, November 13, 2014 8:30 AM
> *To:* user@phoenix.apache.org
> *Subject:* timezone offset
>
>
>
> I created a table with TIMESTAMP column and inserted a value from string
> to it. When I query the table I get the result with timezone offset – any
> way to avid it.
>
>
>
> My steps
>
> ·         I created a table DATE_TEST with TS column
>
> ·         For insert I use: upsert into DATE_TEST  values
> (to_Date('2014-09-01 11:00:00','yyyy-MM-dd HH:mm:ss')
>
> ·         When calling select TO_CHAR (TS,'yyyy-MM-dd HH:mm:ss' ) from
> DATE_TEST  I am getting the value that I inserted
>
> ·         When claling select * from DATE_TEST I am getting the data with
> offset
>
>
>
> See screen shout from I SQuirrel
>
>
>
>
>
> [image: cid:image001.jpg@01D003E4.437285B0]
>
>
>
>
>
>
>
> Regards,
>
> Noam
>
> Information in this e-mail and its attachments is confidential and
> privileged under the TEOCO confidentiality terms that can be reviewed here
> <http://www.teoco.com/email-disclaimer>.
>

Mime
View raw message