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 2014/11/19 19:15:40 UTC
RE: timezone offset
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
[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>.
Re: timezone offset
Posted by James Taylor <ja...@apache.org>.
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 <No...@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>.
>